sqlarraysjsonpostgresqlpostgresql-14

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