PostgreSQL aggregate JSON recordset keys by row

Question

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.

jperelli · Accepted Answer · 2017-08-22T21:43:09.080

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

score 1 · Answer 2 · answered Aug 22 '17 at 02:34

This is my first time using Postgres new JSON functions, they are pretty neat! I have this solution using a table called test.

I split the array elements and then expand them into key/value pairs, and then I sum them in an outer query.

I had to do a nasty double cast ::text::integer to get the JSON values as integers as you can't cast from JSON directly to integer as I found out in this answer.

I found out how to break up the key/value tuples in this tutorial.

SELECT id,
    SUM(CASE WHEN k = 'a' THEN v ELSE 0 END) AS a,
    SUM(CASE WHEN k = 'b' THEN v ELSE 0 END) AS b
FROM (
    SELECT id, 
        (json_each(json_array_elements(data))).key as k, 
        (json_each(json_array_elements(data))).value::text::integer AS v FROM test
) AS json_data
GROUP BY id

Giving the result:

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

PostgreSQL aggregate JSON recordset keys by row

2 Answers2