Postgres Jsonb aggregation

Question

我正试图从POSTGRES jsonb列中实现以下(结果所需)输出，但没有使用"jsonb_agg"函数。我浏览了这个postgres文档https://www.postgresql.org/docs/12/functions-json.html，但没有找到。
为postgres提供json格式相关的资源。

<表类> 城市 JColA JColB tbody><<tr>纽约[{"id"ID1","name":"ID1_NAME","type":"ID1_TYPE","amount": 20.12,"full_name":零},{"id"ID2","name":"ID2_NAME","type":"ID2_TYPE","amount": 11.55,"full_name":零},{"id"ID1","name":"ID1_NAME","type":"ID1_TYPE","amount": 5.45,"full_name"零}):[{"key"key1","value":"1";},{"key"key2","value":"2";},{"key"key3","value":"3"}]直流[{"id"ID1","name":"ID1_NAME","type":"ID1_TYPE","amount": 1.5,"full_name":零},{"id"ID3","name":"ID3_NAME","type":"ID3_TYPE","amount": 1.2,"full_name":零},{"id"ID4","name":"ID4_NAME","type":"ID4_TYPE","amount": 1,"full_name":零}][{"key"key1","value":"1";},{"key"key1","value":"2";},{"key"key1","value":"3"}]DL[{"id"ID4","name":"ID4_NAME","type":"ID4_TYPE","amount": 1.5,"full_name":零},{"id"ID2","name":"ID2_NAME","type":"ID2_TYPE","amount": 1.2,"full_name":零},{"id"ID4","name":"ID4_NAME","type":"ID4_TYPE","amount": 1,"full_name":零}][{"key"key1","value":"2";},{"key"key2","value":"2";},{"key"key3","value":"4"}]纽约[{"id"ID1","name":"ID1_NAME","type":"ID1_TYPE","amount": 4.5,"full_name":零},{"id"ID2","name":"ID2_NAME","type":"ID2_TYPE","amount": 2.2,"full_name":零},{"id"ID4","name":"ID4_NAME","type":"ID4_TYPE","amount": 6"full_name":零}][{"key"key4","value":"2";},{"key"key2","value":"5";},{"key"key2","value":"4"}]直流[{"id"ID3","name":"ID3_NAME","type":"ID3_TYPE","amount": 2.5,"full_name":零},{"id"ID3","name":"ID3_NAME","type":"ID3_TYPE","amount": 2.2,"full_name":零},{"id"ID4","name":"ID4_NAME","type":"ID4_TYPE","amount": 2,"full_name":零}][{"key"key1","value":"2";},{"key"key2","value":"2";},{"key"key3","value":"4"}]

您可以连续使用JSONB_POPULATE_RECORDSET()并为每个JSONB列创建类型，然后使用JSONB_BUILD_OBJECT()组合键值对，同时求和数值，例如

WITH t0 AS
(
SELECT city, 
(JSONB_POPULATE_RECORDSET(NULL::jstA,JColA)).*,
(JSONB_POPULATE_RECORDSET(NULL::jstB,JColB)).*
FROM t
), tA AS
(
SELECT city,
JSONB_BUILD_OBJECT('id',id,'name',name,'type',type,'amount',SUM(amount),'full_name',full_name) AS jsA
FROM t0
GROUP BY city, id, name, type, full_name
ORDER BY id
), tB AS
(
SELECT city,
JSONB_BUILD_OBJECT('key',key,'value',SUM(value)) AS jsB
FROM t0
GROUP BY city, key
ORDER BY key
)
SELECT *
FROM (SELECT city, JSONB_PRETTY(JSONB_AGG(jsA)) AS AggJsonColA FROM tA GROUP BY city) AS tA
JOIN (SELECT city, JSONB_PRETTY(JSONB_AGG(jsB)) AS AggJsonColB FROM tB GROUP BY city) AS tB
ON tB.city=tA.city

您必须在不同的子选择中聚合jColA和jColB值。

您必须打开jColA和jColB数组(使用json_array_elements)，然后将结果扩展到字段(使用json_to_recordset或简单的->>操作符)。

有了纯数据集后，可以将jColA按其他jColA属性分组的amout列和value按key分组的JColB列相加。

在此之后，您必须通过city连接子查询的结果。