PostgreSQL更新jsonb字段 为符合条件的数组元素新增同值err字段

你可以直接通过PostgreSQL原生的JSONB操作函数完成这个更新，强烈建议操作前开启事务，验证结果正确后再提交，避免误改数据。

首先匹配你给出的示例结构：txn_data里的status_result字段是字符串类型存储的序列化JSON数组（值外层带双引号），对应处理语句如下：

1. 先执行查询预览更新后的结果，确认符合预期：

SELECT 
    id,
    txn_data AS old_txn_data,
    jsonb_set(
        txn_data,
        '{status_result}',
        (
            SELECT to_jsonb(
                array_agg(
                    CASE 
                        WHEN elem->>'status' = '2' THEN elem || jsonb_build_object('err', elem->>'message')
                        ELSE elem
                    END
                )::text
            )
            FROM jsonb_array_elements((txn_data->>'status_result')::jsonb) elem
        )
    ) AS new_txn_data
FROM transfer_test
WHERE txn_data->>'status' = '325'
AND EXISTS (
    SELECT 1
    FROM jsonb_array_elements((txn_data->>'status_result')::jsonb) elem
    WHERE elem->>'status' = '2'
);

2. 确认预览结果无误后，执行更新：

BEGIN; -- 开启事务，异常可回滚

UPDATE transfer_test
SET txn_data = jsonb_set(
    txn_data,
    '{status_result}',
    (
        SELECT to_jsonb(
            array_agg(
                CASE 
                    WHEN elem->>'status' = '2' THEN elem || jsonb_build_object('err', elem->>'message')
                    ELSE elem
                END
            )::text
        )
        FROM jsonb_array_elements((txn_data->>'status_result')::jsonb) elem
    )
)
WHERE txn_data->>'status' = '325'
AND EXISTS (
    SELECT 1
    FROM jsonb_array_elements((txn_data->>'status_result')::jsonb) elem
    WHERE elem->>'status' = '2'
);

-- 此时可以再次查询表数据验证结果，确认正确执行COMMIT，错误则执行ROLLBACK
-- COMMIT;
-- ROLLBACK;

如果你示例里status_result外层的双引号是内容转义导致的笔误，实际status_result直接存储的是JSONB数组而非序列化字符串，可以用更简洁的写法，省去类型转换步骤：

-- 预览查询
SELECT 
    id,
    txn_data AS old_txn_data,
    jsonb_set(
        txn_data,
        '{status_result}',
        (
            SELECT jsonb_agg(
                CASE 
                    WHEN elem->>'status' = '2' THEN elem || jsonb_build_object('err', elem->>'message')
                    ELSE elem
                END
            )
            FROM jsonb_array_elements(txn_data->'status_result') elem
        )
    ) AS new_txn_data
FROM transfer_test
WHERE txn_data->>'status' = '325'
AND EXISTS (
    SELECT 1
    FROM jsonb_array_elements(txn_data->'status_result') elem
    WHERE elem->>'status' = '2'
);

-- 更新语句逻辑和之前一致，只需要去掉字段类型转换部分即可

说明 #

• 语句中用CASE判断数组内元素的status值，仅当值为2时才拼接err字段，其他元素保持不变
• WHERE条件中的EXISTS子句用于过滤出真正需要更新的行，避免全表扫描，也能防止status_result格式异常的行被处理时报错
• 上述写法兼容PostgreSQL 9.5及以上所有支持JSONB类型的版本，如果你用的是12+版本，也可以用JSON路径表达式简化写法，但兼容性不如上述写法。

内容的提问来源于stack exchange，提问作者STTR