Add/Update objects and elements 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"

            },
            {
                "id": "RO",
                "z2": false,
                "z3": "SELECT"

            }
        ]
    }
}'::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 z4 field used below query:

with zd as (select ('{x4,y1,'||index-1||',z4}')::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('[ { "name": "john" } ]'::jsonb),false)
from zd

But now in the current json the filed X4 is not there and i need to add "z4": [{ "name": "john" } instead of just updating the field

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"

            },
            {
                "id": "RO",
                "z2": false,
                "z3": "SELECT",
                "z4": [{
                    "name": "john"
                }]
            }
        ]
    }
}

Can the above query be modified or suggest a new query to work for both add(if filed z4 is not there) and update filed z4 example "z4": [{ "name": "john" },{ "name": "Steve" }] if filed z4 is present.

The "z4": [{ "name": "john", "name":"steve" }] looks differently in the expected output. Do you want z4 to be an array? — Bjarni Ragnarsson
@Bjarni Ragnarsson yes sorry for the typo , it should be "z4": [{ "name": "john" },{ "name": "Steve" }] — Priyanka Sahu

Bjarni Ragnarsson Bjarni Ragnarsson · Accepted Answer · 2020-02-22T13:07:28

If you want to add z4, you only need to change the last argument in the jsonb_set function to "true" instead of "false". This instructs the function to create the field if it doesn't exist. You need to change the to_jsonb(...) to '....'::jsonb to parse the array.
This should work:

with zd as (select ('{x4,y1,'||index-1||',z4}')::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,'[{ "name": "john" },{ "name": "Steve" }]'::jsonb,true)
from zd

I hope I got this correctly pasted and modified in here :-)
Best regards,
Bjarni

Add/Update objects and elements inside NESTED JSONB arrays with PostgreSQL

1 Answers