extract all values of postgresql jsonb object

Question

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

user330315 · Accepted Answer · 2018-03-05 08:26:17Z

10

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}

answered Mar 5, 2018 at 8:26

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Is it possible to get a specific key's value instead of the entire JSON under the v column?