Postgres Group by json array column

Question

I'm having a hard time understanding Postgres json array type. How to do a group by using json array column. For example:

select product, avg(sales)
from Order 
group by product

"Error: could not identify an equality operator for type json"

--Order--
id | sales | product                 
1  | 36    | ["874746", "474657"] 
2  | 120   | ["874748"] 
3  | 15    | ["874736", "474654"]

Clodoaldo Neto · Accepted Answer · 2016-06-28 18:48:09Z

5

You need to use jsonb (binary) 9.4+:

select product::jsonb, avg(sales)
from Order 
group by product::jsonb

answered Jun 28, 2016 at 18:48

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

1 Answer 1