使用jsonb_set同步_city与_building表location字段的问题求助

问题背景 #

现有两张PostgreSQL表：

• _building表：location为JSONB类型，包含regionId、regionName（部分值为null）和full address字段
• _city表：location为JSONB类型，包含完整的regionId和regionName值

需要将对应城市的区域信息补充到_building表的location字段中，保留原有full address的同时，替换regionId和regionName的null值或旧值。

原SQL的问题 #

你写的UPDATE语句未生效，核心问题有三点：

1. JSON路径错误：'{location,regionName}'错误假设_building的location字段下还有嵌套的location子对象，实际目标键直接在顶层JSON中，正确路径应为'{regionName}'。
2. 覆盖范围不全：仅处理了regionName字段，未覆盖_building表中regionId为null的情况。
3. 更新逻辑有局限：jsonb_set第四个参数设为false时，仅更新已存在的键；虽然当前场景下键已存在，但设为true可确保键不存在时自动创建，兼容性更强。

正确的UPDATE语句 #

方法1：JSONB合并操作符（简洁高效） #

PostgreSQL的JSONB类型支持||合并操作符，会自动用右侧对象的键值覆盖左侧同名键，同时保留未提及的键（如full address）：

UPDATE _building b
SET location = b.location || jsonb_build_object(
    'regionId', c.location -> 'regionId',
    'regionName', c.location -> 'regionName'
)
FROM _city c
WHERE c.id = b.cityId;

方法2：嵌套jsonb_set（逻辑更显式） #

如果需要明确控制每个键的更新逻辑，可嵌套调用jsonb_set：

UPDATE _building b
SET location = jsonb_set(
    -- 先更新regionId
    jsonb_set(
        b.location,
        '{regionId}',
        c.location -> 'regionId',
        true
    ),
    -- 再更新regionName
    '{regionName}',
    c.location -> 'regionName',
    true
)
FROM _city c
WHERE c.id = b.cityId;

执行结果 #

执行上述语句后，_building表的location字段会按预期补充区域信息，最终数据如下：

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