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 Bjarni Ragnarsson · Accepted Answer · 2019-12-13T12:03:52

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

Update objects inside NESTED JSONB arrays with PostgreSQL

1 Answers