Velmurugan K
Velmurugan K

Reputation: 21

Recursive filtering JSONB with hierarchy in Postgres

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.

Upvotes: 0

Views: 30

Answers (0)

Related Questions