Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
drop table if exists my_table;
create table my_table(id serial primary key, jdata jsonb);
insert into my_table (jdata) values
('
{
    "key1" : "value1",
    "key2" : {
        "messagebody" : {
            "version" : "1"
        },
        "messageheader" : {
            "reference" : "a reference"
        }
    }
}');

DROP TABLE



CREATE TABLE



INSERT 0 1


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


key
value


version
"1"

reference
"a reference"



SELECT 2


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


key
value


version
"1"

reference
"a reference"



SELECT 2