postgresql jsonb find key in object

Question

I have a JSON object that could look like the following:

{
    "key1" : "value1",
    "key2" : {
        "messagebody" : {
            "version" : "1"
        },
        "messageheader" : {
            "reference" : "a reference"
        }
    }
}

This is stored in a PostgeSQL table in a column with type jsonb. My goal is now to find the field version and the field reference. But the structure of the json could vary, so eg. key2 could be named key3 in another record.

Is there a query in PostgreSQL that allow's me to give it a key and find the value of that key in the object no mather where in the object it is located?

The PostgreSQL version is 10.

klin klin · Accepted Answer · 2019-12-17T12:43:34

In Postgres 10 use a recursive query.

with recursive extract_all as
(
    select 
        key,
        value
    from my_table
    cross join lateral jsonb_each(jdata)
union all
    select
        obj_key,
        obj_value
    from extract_all
    left join lateral 
        jsonb_each(value) 
        as o(obj_key, obj_value) 
        on jsonb_typeof(value) = 'object'
    where obj_key is not null
)
select *
from extract_all
where key in ('version', 'reference')

Postgres 12+ allows an alternative solution with SQL/JSON Path.

select
    'version' as key,
    jsonb_path_query(jdata, '$.**.version') as value
from my_table
union all
select
    'reference' as key,
    jsonb_path_query(jdata, '$.**.reference') as value
from my_table

Db<>fidlle.

postgresql jsonb find key in object

1 Answers