Postgresql, export to json
Is possible export to json file? Something like csv export.


Yes. Try this:

select row_to_json(tablename.*,true) from tablename;


or

copy (select row_to_json(tablename.*,true) from tablename) to stdout;


if you need copy.

See row_to_json along with other JSON functions in the documentation: JSON Functions and Operators.

I wouldn't recommend CSV on top of JSON because double quotes are special for both CSV and JSON, and they will be doubled for CSV, which makes the output less readable.

The same goes more or less for the text format of COPY. A plain SELECT without any additional quoting may be simpler.



Update: tablename can be a subquery. For the large subquery in the updated question, you may write

select row_to_json(v.*) FROM ( ...subquery here... ) AS v;


Alternatively, a temporary view may also be helpful to improve readibility or ease of use.

CREATE TEMPORARY VIEW tmpview AS ...subquery here...;

SELECT row_to_json(v.*,true) from tmpview;


The temporary view will be automatically removed at the end of the session.