更新jsonb数组值时触发「json输入语法无效」错误求助

我有一个jsonb类型的列inflectionlinks，存储格式如下：

[
  {
    "Id": 62497,
    "Text": "BlaBla"
  }
]

需要将数组中每个对象的Id字段，替换为word_copy表中对应id（bigint类型）的word_id（uuid类型）值。

尝试的SQL语句 #

update inflection_copy
SET inflectionlinks = s.json_array
FROM (
        SELECT jsonb_agg(
                CASE
                    WHEN elems->>'Id' = (
                        SELECT word_copy.id::text
                        from word_copy
                        where word_copy.id::text = elems->>'Id'
                    ) THEN jsonb_set(
                        elems,
                        '{Id}'::text [],
                        (
                            SELECT jsonb(word_copy.word_id::text)
                            from word_copy
                            where word_copy.id::text = elems->>'Id'
                        )
                    )
                    ELSE elems
                END
            ) as json_array
        FROM inflection_copy,
            jsonb_array_elements(inflectionlinks) elems
    ) s;

报错信息 #

invalid input syntax for type json
DETAIL:  Token "c66a4353" is invalid.
CONTEXT:  JSON data, line 1: c66a4353...

其中c66a4353是目标UUID的一部分。

补充信息 #

• word_copy表核心字段：id（bigint）、word_id（uuid，默认值gen_random_uuid()），示例数据：

  row
  ('27733', '078c979d-e479-4fce-b27c-d14087f467c2')
  ('72337', 'ef288256-1599-4f0f-a932-aad85d666c9a')

• 执行select to_jsonb(word_id::text) from word_copy limit(5);返回带双引号的UUID字符串，符合JSON格式要求。

错误原因 #

问题出在jsonb(word_copy.word_id::text)这部分：PostgreSQL的jsonb()函数要求输入是合法的JSON文本，直接传入不带双引号的UUID字符串会被识别为JSON标识符而非字符串，触发语法错误。虽然做了::text转换，但jsonb()不会自动为字符串添加双引号，导致生成的内容不符合JSON格式。

另外原语句的子查询会将所有行的数组元素聚合为一个数组，导致所有行的inflectionlinks被设置为相同值，这是隐性逻辑错误。

正确的SQL语句 #

UPDATE inflection_copy ic
SET inflectionlinks = (
    SELECT jsonb_agg(
        CASE 
            WHEN wc.word_id IS NOT NULL THEN jsonb_set(elem, '{Id}', to_jsonb(wc.word_id::text))
            ELSE elem
        END
    )
    FROM jsonb_array_elements(ic.inflectionlinks) elem
    LEFT JOIN word_copy wc ON wc.id = (elem->>'Id')::bigint
)
WHERE EXISTS (
    SELECT 1
    FROM jsonb_array_elements(ic.inflectionlinks) elem
    JOIN word_copy wc ON wc.id = (elem->>'Id')::bigint
);

语句说明 #

1. 按行独立处理：直接针对每一行的inflectionlinks数组解析处理，避免全局聚合导致的逻辑错误。
2. 合法生成JSONB值：使用to_jsonb(wc.word_id::text)自动将UUID字符串转换为带双引号的JSON字符串类型，符合JSONB格式要求。
3. 高效关联查询：用LEFT JOIN替代重复子查询，通过(elem->>'Id')::bigint匹配word_copy.id，提升查询效率。
4. 条件更新优化：WHERE EXISTS确保只更新存在匹配UUID记录的行，避免无意义的计算。

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