Select distinct values by key from JSONB column

Question

Postgres v12.0

I have a table

data
{"a": "1", "b": "1"}
{"a": "2", "b": "1"}

And I'd like to retrieve a distinct list of keys and the set of values for each key

key	values
a	[ "1", "2" ]
b	[ "1" ]

Not sure how to formulate a query to achieve those results.

Jeff · Accepted Answer · 2023-02-11 21:22:25Z

3

here is one way:

select key , array_agg(distinct value) 
from table
join lateral (select * from jsonb_each_text(datacolumn)) j on true 
group by key

db<>fiddle here

Jeff

3,9222 gold badges25 silver badges26 bronze badges

answered May 12, 2021 at 23:33

eshirvana

24.7k3 gold badges28 silver badges43 bronze badges

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

1 Answer 1