Postgresql, export to json

Question

Version: PostgreSQL 11.4 (Ubuntu 11.4-1.pgdg19.04+1).

Question: Is possible export to json file? Something like csv export.

COPY ( SQL )
TO stdout DELIMITER '"' csv;

Thanks.

Update: Or can I add escape character \ just for fields param_name and value for this?

string_agg(distinct '{name:"' || param_name || '",value:"' || value || '"}',',') as "params",

Result is:

{name:"připojení",value:"1/2""},

I need this (use \ before " only for fields param_name and value:

{name:"připojení",value:"1/2\""},

Update2: Whole sql command added. How to implement function row_to_json? Just add row_to_json to every item in select I have? For example:

select row_to_json(string_agg(distinct '{name:"' || param_name || '",value:"' || value || '"}',',') as "params"),

...etc...

Whole sql.

with recursive
cte as(
select
category_id,
category_parent,
category_name::text,
category_id::text category_ids
from s_category as c
where category_parent = 0
union all
select
c.category_id,
c.category_parent,
concat(cte.category_name, ' > ', c.category_name),
concat(cte.category_ids, ':', c.category_id::text)
from s_category as c,cte
where cte.category_id = c.category_parent
)

select distinct
s_product.product_id as "itemID",
s_product.product_id as "itemGroupID",
product_shop_id as "productCode",
product_ean as "ean",
product_name as "title",
product_short_label as "description",
concat('https://eshop.unihobby.cz/',product_url,'/',s_product.product_id,'p/') as "link",
concat('https://eshop.unihobby.cz/bin/product/4/',filename) as "image",
price_tax as "price",
price_rec as "priceOriginal",
available_count as "available",
case
when available_count >= 5 then 'SKLADEM > 5 KS'
when available_count < 5 then 'SKLADEM < 5 KS'
when available_couNt = 0 then 'NENÍ SKLADEM'
end as "availability",
producer_name as "brand",
concat('Úvod > ',cte.category_name) as "category",
s_category.category_id as "categoryID",
cte.category_ids as "hierarchy",
string_agg(distinct '{name:"' || param_name || '",value:"' || value || '"}',',') as "params",
case when price_tax = price_rec then '' else 'Akce' end as "label"

from
s_product
left join s_cf_j_product_value on s_product.product_id = s_cf_j_product_value.product_id
left join s_product_image on s_product.product_id = s_product_image.product_id
left join s_pricelist_generated_lists on s_product.product_id = s_pricelist_generated_lists.product_id
left join s_producer on s_product.producer_id = s_producer.producer_id
left join s_category on s_product.category_id = s_category.category_id
left join cte on s_product.category_id = cte.category_id
left join s_cf_j_product_value as a on s_product.product_id = a.product_id
left join s_cf_value as v on a.value_id = v.value_id
left join s_cf_param as p on v.param_id = p.param_id

where
image_order = '0'
and s_category.category_name is not null
and product_active = 'y'
and s_pricelist_generated_lists.group_id = '10'
--and s_product.product_id = '133471'

group by
s_product.product_id,
s_product_image.filename,
s_category.category_id,
s_pricelist_generated_lists.price_tax,
s_pricelist_generated_lists.price_rec,
s_producer.producer_name,
cte.category_name,
cte.category_ids
;

Answer 1

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.