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.
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)
)