PostgreSQL按行汇总JSON记录集键 [英] PostgreSQL aggregate JSON recordset keys by row

问题描述

我将PostgreSQL表中每一行的数据存储在JSON数组中，如下所示:

id          data
-------------------------------------------------
1           [{"a": 1, "b": 2}, {"a": 3, "b":2}]
2           [{"a": 5, "b": 8}, {"a": 9, "b":0}]

如何获取按ID分组的记录集中特定键的总和? 这是所需结果集的示例:

id          a           b
-------------------------------------------------
1           4           4
2           14          8

更新:

我还应该提到data列是jsonb数据类型.

解决方案

select id, sum(a), sum(b)
  from jsontable j
    CROSS JOIN LATERAL
    json_to_recordset(j.data) as x(a integer, b integer)
group by id

在这里您可以测试查询或对其进行修饰 http://rextester.com/ZTCY82930

有关json_to_recordset的文档，请参见 https://www .postgresql.org/docs/9.6/static/functions-json.html

，对于交叉连接，请参见以下 https: //www.reddit.com/r/PostgreSQL/comments/2u6ah3/how_to_use_json_to_recordset_on_json_stored_in_a/co6hr65/

编辑:正如您在更新中说的那样，您使用的是jsonb字段，因此无需使用json_to_recordset，而只需使用jsonb_to_recordset

I have data stored in JSON arrays for each row in a PostgreSQL table like so:

id          data
-------------------------------------------------
1           [{"a": 1, "b": 2}, {"a": 3, "b":2}]
2           [{"a": 5, "b": 8}, {"a": 9, "b":0}]

How can I get the sum of specific keys in the recordsets grouped by id? Here is an example of the desired result set:

id          a           b
-------------------------------------------------
1           4           4
2           14          8

Update:

I should also mention that the data column is a jsonb data type.

解决方案

select id, sum(a), sum(b)
  from jsontable j
    CROSS JOIN LATERAL
    json_to_recordset(j.data) as x(a integer, b integer)
group by id

here you can test the query or fiddle with it http://rextester.com/ZTCY82930

For the documentation on json_to_recordset, see this https://www.postgresql.org/docs/9.6/static/functions-json.html

and for the cross-join thing, see this https://www.reddit.com/r/PostgreSQL/comments/2u6ah3/how_to_use_json_to_recordset_on_json_stored_in_a/co6hr65/

Edit: As you say in your update, you use a jsonb field, so instead of using json_to_recordset, just use jsonb_to_recordset

这篇关于PostgreSQL按行汇总JSON记录集键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！