如何在PostgreSQL的jsonb列中通过已有字段值新增对象

你遇到的错误是因为用字符串拼接生成JSON时，没有正确处理jsonb类型的值，导致最终的JSON语法错误。推荐使用PostgreSQL内置的jsonb构造函数来实现，更安全可靠：

方法一：使用jsonb_build_object拼接更新 #

UPDATE account a
SET data = data || jsonb_build_object(
    'accountedAmount',
    jsonb_build_object(
        'amount', NULL,
        'currency', NULL,
        'amountCzk', a.data->'amount'
    )
)
WHERE a.data->'amount' IS NOT NULL;

方法二：使用jsonb_set精准设置 #

如果需要确保accountedAmount字段不存在时才创建（或覆盖已有值），可以用jsonb_set：

UPDATE account a
SET data = jsonb_set(
    data,
    '{accountedAmount}',
    jsonb_build_object(
        'amount', NULL,
        'currency', NULL,
        'amountCzk', a.data->'amount'
    )
)
WHERE a.data->'amount' IS NOT NULL;

错误原因说明 #

你之前的字符串拼接方式存在两个核心问题：

1. 当a.data->'amount'的值包含特殊字符（如引号、反斜杠）时，拼接后的JSON会出现语法错误，导致PostgreSQL无法解析。
2. 直接将jsonb类型与字符串拼接，PostgreSQL会把jsonb转成文本格式，可能破坏JSON的结构完整性。

而使用jsonb_build_object可以直接操作jsonb对象，自动处理值的类型和格式，从根源避免语法错误。

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