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