如何更新PostgreSQL表中JSON数组内的每个JSON对象？

DevOps 小满

2026-7-31

为JSON数组中的每个对象添加新字段的SQL解决方案

表结构与数据格式

table_b表结构：

id (integer)	data (json)	text (text)
1	{}	yes
2	{}	no

data字段的JSON格式示例：

{"types": [{"key": "first_event", "value": false}, {"key": "second_event", "value": false}, {"key": "third_event", "value": false}]}

需求

仅更新text = 'yes'的记录，为data->'types'数组中的每个JSON对象添加"can": ["test1", "test2"]字段，最终效果：

{"types": [{"key": "first_event", "value": false, "can":["test1", "test2"] }, {"key": "second_event", "value": false , "can":["test1", "test2"]}, {"key": "third_event", "value": false , "can":["test1", "test2"]}]}

原SQL失效原因

你之前使用的jsonb_set语句只能针对固定路径的单个节点修改，无法遍历数组中的所有元素批量添加字段，因此无法生效。

正确解决方案

针对`jsonb`类型字段的更新语句

如果data字段是jsonb类型，直接使用以下SQL：

UPDATE table_b
SET data = jsonb_set(
    data,
    '{types}',
    (
        SELECT jsonb_agg(elem || '{"can": ["test1", "test2"]}'::jsonb)
        FROM jsonb_array_elements(data->'types') AS elem
    ),
    true
)
WHERE text = 'yes';

针对`json`类型字段的更新语句

如果data字段是json类型，需要先转换为jsonb处理，再转回json：

UPDATE table_b
SET data = (
    jsonb_set(
        data::jsonb,
        '{types}',
        (
            SELECT jsonb_agg(elem || '{"can": ["test1", "test2"]}'::jsonb)
            FROM jsonb_array_elements(data::jsonb->'types') AS elem
        ),
        true
    )
)::json
WHERE text = 'yes';