PostgreSQL aggregate over json arrays

Question

I have seen a lot of references to using json_array_elements on extracting the elements of a JSON array. However, this appears to only work on exactly 1 array. If I use this in a generic query, I get the error

ERROR: cannot call json_array_elements on a scalar

Given something like this:

orders
{ "order_id":"2", "items": [{"name": "apple","price": 1.10}]}
{ "order_id": "3","items": [{"name": "apple","price": 1.10},{"name": "banana","price": 0.99}]}

I would like to extract

item	count
apple	2
banana	1

Or

item	total_value_sold
apple	2.20
banana	0.99

Is it possible to aggregate over json arrays like this using json_array_elements?

Answer 1

Use the function for orders->'items' to flatten the data:

select elem->>'name' as name, (elem->>'price')::numeric as price
from my_table
cross join jsonb_array_elements(orders->'items') as elem;

It is easy to get the aggregates you want from the flattened data:

select name, count(*), sum(price) as total_value_sold
from (
    select elem->>'name' as name, (elem->>'price')::numeric as price
    from my_table
    cross join jsonb_array_elements(orders->'items') as elem
    ) s
group by name;

Db<>fiddle.