PostgreSQL中用UUID更新JSONB字段值报错求助

问题背景 #

我有一张包含jsonb类型列的public.applications表，data列存储键值对形式的JSON对象，需要将其中id键的值替换为关联public.devices表的UUID。执行以下UPDATE语句时出现报错：

UPDATE
    public.applications a
SET
    data = jsonb_set(data, '{id}', (SELECT b.uuid FROM public.devices b WHERE b.id = (a.data ->> 'id')::integer)::text)

PostgreSQL 12.12返回错误信息：

ERROR:  function jsonb_set(jsonb, unknown, text) does not exist

示例数据 #

id | data
---------
1  | {"id": 1}
2  | {"id": 2}

预期输出 #

id | data
---------
1  | {"id": device_uuid_here}
2  | {"id": device_uuid_here}

报错原因 #

jsonb_set函数的第三个参数要求是jsonb类型，但你将查询到的UUID转为了text类型，PostgreSQL找不到匹配参数类型的jsonb_set重载函数，因此触发报错。

正确解决方案 #

需要将查询到的UUID转换为jsonb类型，推荐两种可靠实现方式：

方式1：使用to_jsonb()转换（更直观） #

UPDATE
    public.applications a
SET
    data = jsonb_set(
        a.data,
        '{id}',
        to_jsonb((SELECT b.uuid FROM public.devices b WHERE b.id = (a.data ->> 'id')::integer))
    );

方式2：将UUID转为JSON字符串后强制转jsonb #

UPDATE
    public.applications a
SET
    data = jsonb_set(
        a.data,
        '{id}',
        ('"' || (SELECT b.uuid FROM public.devices b WHERE b.id = (a.data ->> 'id')::integer) || '"')::jsonb
    );

逻辑说明 #

1. a.data ->> 'id'提取data字段中id的文本值，转为integer后匹配devices表的主键id
2. 查询到对应的UUID后，通过to_jsonb()或字符串拼接转JSON格式的方式，将其转为jsonb类型
3. jsonb_set函数会将data字段中的id键值替换为目标UUID，最终得到预期的JSONB结构

额外注意事项 #

• 若public.devices表中不存在与applications.data->>'id'匹配的记录，子查询会返回NULL，导致data字段的id被设为null
• 如需过滤无匹配的记录，可添加WHERE EXISTS条件：

UPDATE
    public.applications a
SET
    data = jsonb_set(
        a.data,
        '{id}',
        to_jsonb((SELECT b.uuid FROM public.devices b WHERE b.id = (a.data ->> 'id')::integer))
    )
WHERE EXISTS (
    SELECT 1 FROM public.devices b WHERE b.id = (a.data ->> 'id')::integer
);

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