Update 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"
                }]
            }
        ]
    }
}'::jsonb)

I want to update the filed z4 based on id "id": "RO".

I had similar use case here when i needed to update the x3 field (but without any filter criteria) I used :

UPDATE test.demotbl SET details = jsonb_set(data, '{x3}', [{"x_id": "sam"},{"x_id": "Rohit"}])

And request was updated successfully. output:

{
    "x1": "Americas",
    "x2": "West",
    "x3": [{
        "x_id": "sam"
    },
    {
        "x_id": "Rohit"
    }],
    "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"
                }]
            }
        ]
    }
}

I have similar use case ,now i need to update the z4 filed also based on id value ,the problem is the z4 element is nested and also i cant figure out how to put the filter condition.

Question : how can I add/update the z4 where id is RW

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"
                },
                {
                    "name": "Sandy"
                }]
            },
            {
                "id": "RO",
                "z2": false,
                "z3": "SELECT",
                "z4": [{
                    "name": "salin"
                }]
            }
        ]
    }
}

Bjarni Ragnarsson · Accepted Answer · 2019-12-13 12:03:52Z

0

If I understand your question correctly, you might use something like this to update the field z3 to 'UPDATE' for example:

with zd as (select ('{x4,y1,'||index-1||',z3}')::text[] as path
            from table1
            ,jsonb_array_elements((field1->>'x4')::jsonb->'y1') 
            with ordinality arr(x,index)
            where x->>'id'='RO'
        )
update table1
set field1=jsonb_set(field1,zd.path,to_jsonb('UPDATE'::text),false)
from zd

This assumes the jsonb object is stored in table1, in field1 of type jsonb. It also assumes there is only this one record in the table.

This code is taken from https://www.freecodecamp.org/news/how-to-update-objects-inside-jsonb-arrays-with-postgresql-5c4e03be256a/ and adjusted.

Best regards,
Bjarni

answered Dec 13, 2019 at 12:03

Bjarni Ragnarsson

1,7811 gold badge8 silver badges8 bronze badges

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

19 Comments

Priyanka Sahu Over a year ago

Thanks so much for the response Bjarni.The above solution you gave is updating filed z3 but i want to update filed z4.

Priyanka Sahu Over a year ago

The solution is very close can you please suggest /help to modify the query to update filed z4. Appreciate you help

Priyanka Sahu Over a year ago

i was able to modify the query to update filed z4.There is one problem which i missed earlier as well with the above query irrespective of filter condition x->>'id'=='RO' the query always updates the first array element of y1.(i.e the elemet with the field "id":"RW"

Priyanka Sahu Over a year ago

here is the query which i modified to update filed z4

Priyanka Sahu Over a year ago

with zd as (select ('{x4,y1,'||index-2||',z4}')::text[] as path from sandbox_service.demotbl ,jsonb_array_elements((data->>'x4')::jsonb->'y1') with ordinality arr(x,index) where x->>'id'='RO' ) update sandbox_service.demotbl set data=jsonb_set(data,zd.path,to_jsonb('[ { "name": "priyanka" } ]'::jsonb),false) from zd

|

Collectives™ on Stack Overflow

Update objects inside NESTED JSONB arrays with PostgreSQL

1 Answer 1

19 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

19 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related