Search code examples
postgresqljsonb

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

Solution

  • 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}