How to add new key-value pair in each object of JSONB Array- PostgreSQL

Question

I have a table master_data_approval_table in my postgres DB having field approval_value (type Jsonb) which contains below JSON structure:

[{
        "name": "abc",
        "email": "abc.pqr@gmail.com"
    },
    {
        "name": "xyz",
        "email": "xyz.pqr@gmail.com"
    }
]

Now we want to add one more key-value pair i.e. "comment": null in each object of the array like the below one:

[{
        "name": "abc",
        "email": "abc.pqr@gmail.com",
        "comment": null
    },
    {
        "name": "xyz",
        "email": "xyz.pqr@gmail.com",
        "comment": null
    }
]

In more to that we need to do this for all the records of the table. I have tried different existing answers and queries but no help so please somebody guide me on how to write a query in PostgreSQL for the above scenario.

Thanks in advance.

Answer 1

You can unnest the JSON array with jsonb_array_elements(), use || to modify each object to add the new key/value, and finally aggregate back with jsonb_agg(), with respect to the original ordering:

select t.approval_value, e.new_approval_value
from master_data_approval_table t
cross join lateral (
    select jsonb_agg( e.elt || '{"comment": null}' order by e.seq) new_approval_value
    from jsonb_array_elements(t.approval_value) with ordinality as e(elt, seq)
) e

approval_value	new_approval_value
[     {         "name": "abc",         "email": "abc.pqr@gmail.com"     },     {         "name": "xyz",         "email": "xyz.pqr@gmail.com"     } ]	[     {         "name": "abc",         "email": "abc.pqr@gmail.com",         "comment": null     },     {         "name": "xyz",         "email": "xyz.pqr@gmail.com",         "comment": null     } ]

If you wanted to actually modify the values in the column, you would update:

update master_data_approval_table t
set approval_value = (
    select jsonb_agg( e.elt || '{"comment": null}' order by e.seq) new_approval_value
    from jsonb_array_elements(t.approval_value) with ordinality as e(elt, seq)
)

fiddle