4

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?

2
  • Thanks @a_horse_with_no_name - issue was with my example not my actual data, sorry. Corrected above.
    – RMcLellan
    Commented Feb 7, 2023 at 22:26
  • Do you always want the value of the nested key foo ?
    – user1822
    Commented Feb 8, 2023 at 7:51

2 Answers 2

3

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:

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

2
  • Thiis is awesome and definately helped me along. SELECT d0.key AS top_lvl_key, d1.value AS lvl1_value FROM tbl t CROSS JOIN LATERAL (SELECT * FROM jsonb_each (t.data)) d0 JOIN LATERAL (SELECT * FROM jsonb_each_text(d0.value) WITH ORDINALITY) d1 ON d1.ordinality = 2; gets me really close but I need to be able to select the level 1 key by name as the position changes depending on the data. Any ideas would be welcome.
    – RMcLellan
    Commented Feb 8, 2023 at 0:01
  • 1
    @RMcLellan: A given key name only makes it simpler. See added query. If you know the outer key name as well, it gets simpler, yet ... Commented Feb 8, 2023 at 0:14
2

If the nested key is known and you only have one top level key, you can also use a JSON path expression:

select id, 
       jsonb_path_query_first(data, '$.keyvalue()') ->> 'key' as top_level_key,
       jsonb_path_query_first(data, '$.*.foo') #>> '{}' as foo
from the_table

jsonb_path_query_first() returns a jsonb, the #>> '{}' is a hack to convert that to text as there is no easy or direct cast.

3
  • I assume this would only work if the top level key was uniform? The issue I was running into is that my top level key was not (it's a date in my case).
    – RMcLellan
    Commented Feb 8, 2023 at 18:16
  • 1
    @RMcLellan: the actual value of the top level key doesn't really matter. JSON only knows strings, numbers and booleans anyway (and keys can only be strings)
    – user1822
    Commented Feb 8, 2023 at 18:47
  • jsonb_path_query_first -> jsonb_path_query and it works great, thanks!
    – RMcLellan
    Commented Feb 8, 2023 at 20:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.