postgresql - Extract multiple values from JSONB in Postgres

Question

I have a table that looks like this:

id	attrs
1	{"a":{"kind":"kind_1", "value":"val_1"}, "b":{"kind":"kind_2", "value":"val_2"}
2	{"c":{"kind":"kind_3", "value":"val_1"}}
3	{"a":{"kind":"kind_1", "value":"val_1"}, "d":{"kind":"kind_4", "value":"val_4"}, .....

question from:https://stackoverflow.com/questions/65599434/extract-multiple-values-from-jsonb-in-postgres

Answer 1

You can use a JSON Path query:

select distinct v.item #>> '{}'
from the_table t
  cross join jsonb_array_elements(jsonb_path_query_array(t.attrs, '$.**.value')) as v(item);

The v.item #>> '{}' is a trick to convert a scalar JSON value to text (because casting it wouldn't work)

Alternatively you can use jsonb_each() twice:

select distinct v.value
from the_table t
  cross join jsonb_each(t.attrs) as i(key, item)
  cross join jsonb_each_text(i.item) as v(key, value)
where v.key = 'value'  

---