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

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.

>Solution :

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

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