PostgreSQL - Aggregate values in JSON object without specifying keys

Question

I am trying to get a sum of values in a JSON array grouped by a shop_id and key, and get the output in the same format.

So, with an array like this:

[
    {
        sales: {"bicycle": 2, "skateboard": 5},
        shop_id: 6321
    },
    {
        sales: {"bicycle": 1, "skateboard": 3},
        shop_id: 6243
    },
    {
        sales: {"bicycle": 3, "skateboard": 4},
        shop_id: 6243
    }
]

The output should look like:

[
    {
        sales: {"bicycle": 2, "skateboard": 5},
        shop_id: 6321
    },
    {
        sales: {"bicycle": 4, "skateboard": 7},
        shop_id: 6243
    }
]

The JSON input will always be uniform. I can get it to work if I use JSON_BUILD_OBJECT and JSON_AGG but I don't want to specify what keys to use while building JSON because the real data will probably hold around 20 different keys, so I am looking for a cleaner solution.

SELECT JSON_BUILD_OBJECT('bicycle', SUM((sales::json ->> 'bicycle')::int),
                         'skateboard', SUM((sales::json ->> 'skateboard')::int)
           ) AS sales,
       shop_id
FROM (SELECT val ->> 'sales' AS sales, val ->> 'shop_id' AS shop_id
      FROM (SELECT UNNEST($1)::jsonb AS val) core) outercore
GROUP BY shop_id;

How can I achieve something like this?

Which Postgres version are you using?

user330315
– user330315

2021-04-28 11:08:53 +00:00
Commented Apr 28, 2021 at 11:08 — user330315
– user330315, Commented Apr 28, 2021 at 11:08
11.9 but there are plans to upgrade to 13.2 later on

Özenç B.
– Özenç B.

2021-04-28 11:12:40 +00:00
Commented Apr 28, 2021 at 11:12 — Özenç B.
– Özenç B., Commented Apr 28, 2021 at 11:12

user330315 · Accepted Answer · 2021-04-28 11:19:46Z

You need to extract all items to key/value pairs, then you can aggregate them back into a single JSON:

select jsonb_agg(s)
from (
  select shop_id, 
         jsonb_object_agg(typ, value) as sales
  from (       
    select (t.item ->> 'shop_id')::int as shop_id,
           s.typ, 
           sum(value::int) as value
    from your_table yt --<< this is where the json column comes from
      cross join jsonb_array_elements(yt.val) as t(item)
      cross join jsonb_each(t.item -> 'sales') as s(typ, value)
    group by id, shop_id, typ 
  ) x
  group by id, shop_id  
) s  
;

Online example

Collectives™ on Stack Overflow

PostgreSQL - Aggregate values in JSON object without specifying keys

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related