Pair top-level key with nested value from jsonb document

Given the following data:

create table datas (id int, data jsonb);

insert into datas (id, data) 
values 
  (1, '{"key1": {"foo": "bar1","some":"thing1"}}'), 
  (2, '{"key2": {"foo": "bar2","some":"thing2"}}');

I've been able to extract the top level JSON as an array here given the following:

SELECT ARRAY(SELECT jsonb_object_keys(data)) AS keys;

Result:

[ "key1", "key2" ]

However, I'm looking to extract the following as columns and I'm having issue pairing a nested value with the top level JSON:

Top Level JSON
Nested Value

key1
bar1

key2
bar2

Could someone please recommend a way to access the sublevel data for each object and output it as per my example?

jsonb_each() and jsonb_each_text() should help. Details depend on possible input and desired outout.

Seems there is always at least one top-level key, and one nested value, and you want one result per table row.

For a given nested key (as per comment) it's even simpler. Say, the nested key is 'foo':

SELECT d0.key AS top_lvl_key, d0.value->>'foo' AS foo_value
FROM   tbl t
CROSS  JOIN LATERAL (SELECT * FROM jsonb_each (t.data) LIMIT 1) d0;

Else, to pick the "first" nested value:

SELECT d0.key AS top_lvl_key, d1.value AS lvl1_value
FROM   tbl t
CROSS  JOIN LATERAL (SELECT * FROM jsonb_each (t.data) LIMIT 1) d0
CROSS  JOIN LATERAL (SELECT * FROM jsonb_each_text(d0.value) LIMIT 1) d1;

LIMIT 1 usually works to get the "first" element. To positively pick the "first" element (according to Postgres sort order) , use WITH ORDINALITY:

SELECT d0.key AS top_lvl_key, d1.value AS lvl1_value
FROM   tbl t
JOIN   LATERAL (SELECT * FROM jsonb_each (t.data) WITH ORDINALITY) d0 ON d0.ordinality = 1
JOIN   LATERAL (SELECT * FROM jsonb_each_text(d0.value) WITH ORDINALITY) d1 ON d1.ordinality = 1;

About WITH ORDINALITY:

• PostgreSQL unnest() with element number

To simply output all combinations of top-level keys and nested values - or if there is always exactly one:

SELECT d0.key AS lvl0, d1.value AS lvl1
FROM   tbl t, jsonb_each (t.data) d0, jsonb_each_text (d0.value) d1;

More variants:

fiddle

StackExchange Database Administrators Q#323184, answer score: 3