Extract multiple values from JSONB in Postgres

user_2899630 0
Viewed 180

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"}, .....

I would like to extract all the unique value, so the output would be:
val_1
val_2
val_4
...

I tried to use jsonb_each method for it, but without any luck

jsonb postgresql
1 Answers

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'
user_3303150
Related