Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit



SELECT 1


create table master_data_approval_table ( 
  id int generated always as identity, 
  approval_value jsonb);

CREATE TABLE


insert into master_data_approval_table (approval_value) 
values ('[{
        "name": "abc",
        "email": "abc.pqr@gmail.com"
    },
    {
        "name": "xyz",
        "email": "xyz.pqr@gmail.com"
    }
]');

INSERT 0 1


select jsonb_pretty(t.approval_value) approval_value, 
  jsonb_pretty(e.new_approval_value) 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
    }
]



SELECT 1


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

UPDATE 1


select * from master_data_approval_table


id
approval_value


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



SELECT 1