CODEWITHSUNDEEP
postgresqljsonb
Mehdi Monzavi
Mehdi Monzavi

Reputation: 137

extract all values of postgresql jsonb object

i have a postgresql table t1 , id integer , data jsonb

   id    |   data
--------------------
    1    | {"1":{"11":11},"2":{"12":12}}

and i need a function to extract all key/value in separate rows like this

   key   |   values
----------------------
    1    |  {"11":11}
    2    |  {"12":12}

in "hstore" dataType , there was "hvals" function , do this
but in jsonb i dont find similar function

Upvotes: 4

Views: 9594

Answers (1)

user330315
user330315

Reputation:

You are looking for jsonb_each

with t1 (id, data) as (
  values (1, '{"1":{"11":11},"2":{"12":12}}'::jsonb)
)
select t.*
from t1, jsonb_each(data) as t(k,v)

returns:

k | v         
--+-----------
1 | {"11": 11}
2 | {"12": 12}

Upvotes: 8

Related Questions