Delete objects inside NESTED JSONB arrays with PostgreSQL

Question

json Request:

INSERT INTO test.demotbl (data)
VALUES ('{
    "x1": "Americas",
    "x2": "West",
    "x3": [{
        "x_id": "sam"
    }],
    "x4": {
        "a1": true,
        "a2": false,
        "a3": [
            "xx",
            "xx"
        ],
        "a4": [
            "Josh"
        ],
        "y1": [{
                "id": "RW",
                "z2": true,
                "z3": "USER",
                "z4": [{
                    "name": "john"
                }]
            },
             {
                "id": "RO",
                "z2": false,
                "z3": "SELECT",
                "z4": [{
                    "name": "salin"
                }
            {
                "id": "DBA",
                "z2": false,
                "z3": "SELECT",
                "z4": [{
                    "name": "Samule"
                }]
            }
        ]
    }
}'::jsonb)

Question 1 :how can i delete the the array values where "id":"RO" from y1 array ? There can be any no of element in the y1 json array i want to delete the array value based on id condition.

how can i delete the the array values where "id":"RO" from y1 array ? There can be any no of element in the y1 json array i want to delete the array value based on id condition.

Expected Output after deleting:

{
    "x1": "Americas",
    "x2": "West",
    "x3": [{
        "x_id": "sam"
    }],
    "x4": {
        "a1": true,
        "a2": false,
        "a3": [
            "xx",
            "xx"
        ],
        "a4": [
            "Josh"
        ],
        "y1": [{
                "id": "RW",
                "z2": true,
                "z3": "USER",
                "z4": [{
                    "name": "john"
                }]
            },

            {
                "id": "DBA",
                "z2": false,
                "z3": "SELECT",
                "z4": [{
                    "name": "Samule"
                }]
            }
        ]
    }
}

Question 2 : How can i only delete the "id":"RO" from the y1 array

Expected Output:

{
    "x1": "Americas",
    "x2": "West",
    "x3": [{
        "x_id": "sam"
    }],
    "x4": {
        "a1": true,
        "a2": false,
        "a3": [
            "xx",
            "xx"
        ],
        "a4": [
            "Josh"
        ],
        "y1": [{
                "id": "RW",
                "z2": true,
                "z3": "USER",
                "z4": [{
                    "name": "john"
                }]
            },
             {
                "z2": false,
                "z3": "SELECT",
                "z4": [{
                    "name": "salin"
                }
            {
                "id": "DBA",
                "z2": false,
                "z3": "SELECT",
                "z4": [{
                    "name": "Samule"
                }]
            }
        ]
    }
}

Bjarni Ragnarsson · Accepted Answer · 2019-12-15 14:30:01Z

1

Edit note: I misunderstood the question. Thought you wanted to delete the whole object containing 'RO'. I edited the answer so as just to delete the id.

There is a small error in the jsonb object you provide. It should probably look like this:

{
    "x1": "Americas",
    "x2": "West",
    "x3": [{
        "x_id": "sam"
    }],
    "x4": {
        "a1": true,
        "a2": false,
        "a3": [
            "xx",
            "xx"
        ],
        "a4": [
            "Josh"
        ],
        "y1": [{
                "id": "RW",
                "z2": true,
                "z3": "USER",
                "z4": [{
                    "name": "john"
                }]
            },
             {
                "id": "RO",
                "z2": false,
                "z3": "SELECT",
                "z4": [{
                    "name": "salin"
                }]
            },
            {
                "id": "DBA",
                "z2": false,
                "z3": "SELECT",
                "z4": [{
                    "name": "Samule"
                }]
            }
        ]
    }
}

With that said, this should work, but keep in mind - this will replace all entries in the working table. The jsonb objects are in the field "field".

with zd as (select ('{x4,y1,'||index-1||',id}')::text[] as path
            from table
            ,jsonb_array_elements((field->>'x4')::jsonb->'y1') 
            with ordinality arr(x,index)
            where x->>'id'='RO'
        )
        update table set field=
        field #- zd.path 
        from zd

Best regards,
Bjarni

edited Dec 15, 2019 at 14:30

answered Dec 15, 2019 at 14:11

Bjarni Ragnarsson

1,7811 gold badge8 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Priyanka Sahu Over a year ago

Thanks @Bjarni Ragnarsson that solves the Question 2.For Question 1 i want to delete the entire this value ==> { "id": "RO", "z2": false, "z3": "SELECT", "z4": [{ "name": "salin" }

Priyanka Sahu Over a year ago

yes i wanted to delete the whole object containing 'RO' that is my first question .Second question was to just delete "id":"RO" which you have provided the solution :)

Bjarni Ragnarsson Over a year ago

Ok. To delete the whole object, replace "with zd as (select ('{x4,y1,'||index-1||',id}')::text[] as path" with "with zd as (select ('{x4,y1,'||index-1||'}')::text[] as path".

Priyanka Sahu Over a year ago

Awesome that solves it.Thanks again for the solution @ Bjarni Ragnarsson

Collectives™ on Stack Overflow

Delete objects inside NESTED JSONB arrays with PostgreSQL

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related