如何在 JSONB 数组的每个对象中添加新的键值对 PostgreSQL

发布于07月06日

我的Postgres数据库中有一个表master_data_approval_table，其中包含字段approval_value(Jsonb类型)，其中包含以下JSON struct :

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

现在，我们希望在数组的每个对象中再添加一个键-值对，即"comment": null，如下所示:

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

更重要的是，我们需要对表中的所有记录执行此操作. 我已经try 了不同的现有答案和查询，但没有帮助，所以请有人指导我如何在上面的场景中用PostgreSQL编写查询.

先谢谢你.

推荐答案

您可以使用jsonb_array_elements()取消JSON数组的嵌套，使用||修改每个对象以添加新的键/值，最后按照原始顺序聚合回jsonb_agg():

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

如果要实际修改列中的值，则应为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个