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?

"this appears to only work on exactly 1 array. If I use this in a generic query" - not sure what you mean by that. Can you show us the code that you used, the one that caused the error? — Bergi
– Bergi, Commented Jan 21, 2022 at 2:04

klin · Accepted Answer · 2022-01-21 00:56:23Z

3

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.

answered Jan 21, 2022 at 0:56

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Aserian Over a year ago

When I run your fiddle, it works fine - but when I swap out WITH ( VALUES ...), with WITH (SELECT * FROM my_local_table), it throws the same error

Aserian Over a year ago

How are you calling the table name like that? orders->'items' doesn't really make sense to me since it is not a column name. I am trying it on my local table using your syntax (without the WITH from the fiddle) and it says ERROR: column "orders" does not exist

klin Over a year ago

The orders in my query is the column name. Replace it with the actual jsonb column name that you forgot to include in the question.

Aserian Over a year ago

Right, sorry, the issue ended up actually being that there were null valued arrays in the data. Your answer worked, key was just to also add WHERE orders->>items IS NOT NULL

Collectives™ on Stack Overflow

PostgreSQL aggregate over json arrays

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related