时间戳

首页 > 解决方案 > Postgres Jsonb 聚合

postgresql - Postgres Jsonb 聚合

问题描述

我正在尝试从 POSTGRES jsonb 列实现以下（需要结果）输出，但没有使用“jsonb_agg”函数获得所需的结果。我浏览了这个 postgres 文档https://www.postgresql.org/docs/12/functions-json.html，但这里没有运气。
还建议为 postgres 提供 json 格式相关内容的好资源。

城市 JColA JColB
纽约 [{"id":"ID1","name":"ID1_NAME","type":"ID1_TYPE","amount":20.12,"full_name":null},{"id":"ID2","name ":"ID2_NAME","type":"ID2_TYPE","amount":11.55,"full_name":null},{"id":"ID1","name":"ID1_NAME","type":"ID1_TYPE ","amount":5.45,"full_name":null}] [{"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":null},{"id":"ID3","name ":"ID3_NAME","type":"ID3_TYPE","amount":1.2,"full_name":null},{"id":"ID4","name":"ID4_NAME","type":"ID4_TYPE ","amount":1,"full_name":null}] [{"key":"key1","value":"1"},{"key":"key1","value":"2"},{"key":"key1","value": "3"}]
深度学习 [{"id":"ID4","name":"ID4_NAME","type":"ID4_TYPE","amount":1.5,"full_name":null},{"id":"ID2","name ":"ID2_NAME","type":"ID2_TYPE","amount":1.2,"full_name":null},{"id":"ID4","name":"ID4_NAME","type":"ID4_TYPE ","amount":1,"full_name":null}] [{"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":null},{"id":"ID2","name ":"ID2_NAME","type":"ID2_TYPE","amount":2.2,"full_name":null},{"id":"ID4","name":"ID4_NAME","type":"ID4_TYPE ","amount":6,"full_name":null}] [{"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":null},{"id":"ID3","name ":"ID3_NAME","type":"ID3_TYPE","amount":2.2,"full_name":null},{"id":"ID4","name":"ID4_NAME","type":"ID4_TYPE ","amount":2,"full_name":null}] [{"key":"key1","value":"2"},{"key":"key2","value":"2"},{"key":"key3","value": "4"}]

要求的结果

城市 AggJSonColA AggJsonColB
纽约 [{"id":"ID1","name":"ID1_NAME","type":"ID1_TYPE","amount":30.07,"full_name":null},{"id":"ID2","name ":"ID2_NAME","type":"ID2_TYPE","amount":13.75,"full_name":null},{"id":"ID4","name":"ID4_NAME","type":"ID4_TYPE ","amount":6,"full_name":null}] [{"key":"key1","value":"1"},{"key":"key2","value":"11"},{"key":"key3","value": "3"}, {"key":"key4","value":"2"}]
直流 [{"id":"ID1","name":"ID1_NAME","type":"ID1_TYPE","amount":1.5,"full_name":null},{"id":"ID3","name ":"ID3_NAME","type":"ID3_TYPE","amount":5.9,"full_name":null},{"id":"ID4","name":"ID4_NAME","type":"ID4_TYPE ","amount":3,"full_name":null}] [{"key":"key1","value":"8"},{"key":"key2","value":"2"},{"key":"key3","value": "4"}]
深度学习 [{"id":"ID4","name":"ID4_NAME","type":"ID4_TYPE","amount":1.5,"full_name":null},{"id":"ID2","name ":"ID2_NAME","type":"ID2_TYPE","amount":1.2,"full_name":null},{"id":"ID4","name":"ID4_NAME","type":"ID4_TYPE ","amount":1,"full_name":null}] [{"key":"key1","value":"2"},{"key":"key2","value":"2"},{"key":"key3","value": "4"}]

标签： postgresqlaggregationjsonb

解决方案

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

您必须取消嵌套jColA和jColB数组（使用json_array_elements），然后将结果扩展为字段（使用json_to_recordset或简单->>运算符）。

拥有普通数据集后，您可以对按其他属性分组和按 分组的amout列求和。jColAjColAvalue JColBkey

在你之后，你必须加入子查询的结果city。

推荐阅读