Postgresql，导出为 json

版本： PostgreSQL 11.4（Ubuntu 11.4-1.pgdg19.04+1）。

问题：可以导出到 json 文件吗？类似 csv 导出的东西。

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

谢谢。

更新：或者我可以为字段添加转义字符\吗？param_namevalue

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

结果是：

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

我需要这个（\之前"只用于字段param_name和value：

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

Update2：添加了整个 sql 命令。如何实现功能row_to_json？只需添加row_to_json到我拥有的选择中的每个项目？例如：

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

...etc...

整个 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
;