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.

Answer 1

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