Recursive filtering JSONB with hierarchy in Postgres

Question

Using Postgres JSONB for storing my data. JSON hierarchy root -> sections -> chars. Here possibilities of sections contains child of sections.
Each level contains data objects. I need filter the data object using recursive filter with data.name ="ABC". it should return filter data with full original JSON structure. I'm new to Postgres. So, I'm not sure about achieving this functionality.

Original JSON

    "id": 1,
    "description": "user description",
    "datas": [
        {
            "id": "1231",
            "name": "ABC"
        },
        {
            "id": "123s1",
            "name": "AewqewC"
        }
    ],
    "sections":[
        {
            "id": "1sd231",
            "description" : "description",
            "datas": [
                {
                    "id": "1231",
                    "name": "ABC"
                },
                {
                    "id": "123s1",
                    "name": "AewqewC"
                }
            ],
            "chars" : [
                {
                    "id": "1sd231",
                    "description" : "description",
                    "datas": [
                        {
                            "id": "1231",
                            "name": "ABC"
                        },
                        {
                            "id": "123s1",
                            "name": "AewqewC"
                        }
                    ]
                },
                {
                    "id": "1sqwe231",
                    "description" : "description",
                    "datas": [
                        {
                            "id": "12re31",
                            "name": "ABC"
                        },
                        {
                            "id": "12ere3s1",
                            "name": "AewqewC"
                        }
                    ]
                }
            ]
        },
        {
            "id": "1sd231",
            "description" : "description",
            "datas": [
                {
                    "id": "1231",
                    "name": "ABC"
                },
                {
                    "id": "123s1",
                    "name": "AewqewC"
                }
            ],
            "chars" : [
                {
                    "id": "1sd231",
                    "description" : "description",
                    "datas": [
                        {
                            "id": "1231",
                            "name": "ABC"
                        },
                        {
                            "id": "123s1",
                            "name": "AewqewC"
                        }
                    ]
                },
                {
                    "id": "1sqwe231",
                    "description" : "description",
                    "datas": [
                        {
                            "id": "12re31",
                            "name": "ABC"
                        },
                        {
                            "id": "12ere3s1",
                            "name": "AewqewC"
                        }
                    ]
                }
            ]
        }
    ]
}]

Expected result

[
    {
        "datas": [
            {
                "id": "1231",
                "name": "ABC"
            }
        ],
        "sections": [
            {
                "datas": [
                    {
                        "id": "1231",
                        "name": "ABC"
                    }
                ],
                "chars": [
                    {
                        "datas": [
                            {
                                "id": "1231",
                                "name": "ABC"
                            }
                        ]
                    },
                    {
                        "datas": [
                            {
                                "id": "12re31",
                                "name": "ABC"
                            }
                        ]
                    }
                ]
            }
        ]
    }
]

Thank you.

Can you create a DBFiddle with sample tables and values and add it to the question? — Lajos Arpad
– Lajos Arpad, Commented Oct 29, 2024 at 5:58

Michael Eliot · Accepted Answer · 2025-09-30 15:54:29Z

If this is the core of your data, I would recommend converting it from raw json to actual rows in postgres. This will make queries much faster, and allow the usage of actual queries, type checking, relations, and all the other goodies in postgres. This is a nice write up on working with hierarchical data in postgres.

If it must be saved in a json column, I can't think of a way to preserve an arbitrary hierarchy while keeping the query in postgres. However, to just return the list of filtered nodes as a json object you can use jsonb_path_query to do the filtering, and then jsonb_agg to return the json values:

SELECT jsonb_agg(filtered_object) AS filtered_json
FROM (
    SELECT jsonb_path_query(data, '$.** ? (@.name == "ABC")') AS filtered_object
    FROM nested_json_data
) sub;

[
  {"id": "1231", "name": "ABC"},
  {"id": "1231", "name": "ABC"},
  {"id": "1231", "name": "ABC"},
  {"id": "12re31", "name": "ABC"},
  {"id": "1231", "name": "ABC"},
  {"id": "1231", "name": "ABC"},
  {"id": "12re31", "name": "ABC"}
]

If that json data is static (i.e. you only need to filter datas, sections->datas, and section->chars->datas) then you can just recreate the json structure in a postgres query:

SELECT jsonb_build_object(
    'id', data->'id',
    'description', data->'description',
    'datas', (
        SELECT jsonb_agg(d) 
        FROM jsonb_array_elements(data->'datas') AS d
        WHERE d->>'name' = 'ABC'
    ),
    'sections', (
        SELECT jsonb_agg(
            jsonb_build_object(
                'id', s->'id',
                'description', s->'description',
                'datas', (
                    SELECT jsonb_agg(sd) 
                    FROM jsonb_array_elements(s->'datas') AS sd
                    WHERE sd->>'name' = 'ABC'
                ),
                'chars', (
                    SELECT jsonb_agg(
                        jsonb_build_object(
                            'id', c->'id',
                            'description', c->'description',
                            'datas', (
                                SELECT jsonb_agg(cd)
                                FROM jsonb_array_elements(c->'datas') AS cd
                                WHERE cd->>'name' = 'ABC'
                            )
                        )
                    )
                    FROM jsonb_array_elements(s->'chars') AS c
                )
            )
        )
        FROM jsonb_array_elements(data->'sections') AS s
    )
) AS filtered_json
FROM nested_json_data;

Hope this helps!

Collectives™ on Stack Overflow

Recursive filtering JSONB with hierarchy in Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related