Arunraj Arunraj - 1 year ago 63
JSON Question

select or extract all key values inside a jsonb data in postgres

I have a jsonb column in my postgres table and data is populated and stored in it.

Here is a sample format of data that is stored in the jsonb column

{"unknown_value1": {"code": "code 1", "title": "sample title 1", "parent_title": "parent1", "framework_code": "ABC"}, "unknown_value2": {"code": "code 2", "title": "sample title 2", "parent_title": "parent2", "framework_code": "ABC"}}

Now I need to extract all values from key 'parent_title' without specifying the outermost key(unknown_value1, unknown_value2).

The outermost keys are unknown it could be any value, changes for each data.

But the key inside the json(code, title, parent_title and framework_code) is constant, it wont change for any value.

Help me extract this jsonb data.

Thanks in advance!

Answer Source

Use cascaded jsonb_each() in a lateral join:

with data(json_value) as (
values (
        "unknown_value1": {
            "code": "code 1",
            "title": "sample title 1",
            "parent_title": "parent1",
            "framework_code": "ABC"
        "unknown_value2": {
            "code": "code 2",
            "title": "sample title 2",
            "parent_title": "parent2",
            "framework_code": "ABC"

select j1.key as outer_key, j2.key, j2.value
from data, 
lateral jsonb_each(json_value) j1,
lateral jsonb_each(value) j2

   outer_key    |      key       |      value       
 unknown_value1 | code           | "code 1"
 unknown_value1 | title          | "sample title 1"
 unknown_value1 | parent_title   | "parent1"
 unknown_value1 | framework_code | "ABC"
 unknown_value2 | code           | "code 2"
 unknown_value2 | title          | "sample title 2"
 unknown_value2 | parent_title   | "parent2"
 unknown_value2 | framework_code | "ABC"
(8 rows)