Postgresql，導出為 json

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

**問題：**可以導出到 json 文件嗎？類似 csv 導出的東西。

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

謝謝。

更新：或者我可以為欄位添加轉義字元\嗎？param_name``value

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
;

可以導出到 json 文件嗎？類似 csv 導出的東西。

是的。試試這個：

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

或者

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

如果你需要copy。

請參閱row_to_json文件中的其他 JSON 函式：JSON 函式和運算符。

我不建議在 JSON 之上使用 CSV，因為雙引號對於 CSV 和 JSON 都是特殊的，並且對於 CSV，它們會加倍，這會降低輸出的可讀性。

. 的文本格式或多或少也是如此COPY。沒有任何額外引用的普通 SELECT 可能更簡單。

---

更新：tablename可以是子查詢。對於更新問題中的大子查詢，您可以編寫

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

或者，臨時視圖也可能有助於提高可讀性或易用性。

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

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

臨時視圖將在會話結束時自動刪除。

引用自：https://dba.stackexchange.com/questions/244538