如何在单条SQL中批量移除jsonb的指定键与数组元素？

需求说明 #

我们的metaTable表有一个meta列，存储的JSONB结构如下：

{
 "records": {
    "record1": {
        "GUID-1": {
           "values": ["GUID-value-1", "GUID-value-2", "GUID-value-3"],
           "type": "..."
        },
        "GUID-2": {
           "values": ["GUID-value-4", "GUID-value-5"],
           "type": "..."
        },
        "GUID-3": {
           "values": ["GUID-value-6"],
           "type": "..."
        },
        "GUID-4": {
           "values": ["GUID-value-7"],
           "type": "..."
        }
    }
 },
 "miscellaneous": {
    .....
 }
}

需要实现：

• 从JSON文档中移除给定数组（如["GUID-3", "GUID-value-4", "GUID-value-1"]）里的键（比如GUID-3）和嵌套数组元素（比如GUID-value-4）
• 自动移除处理后values数组为空的键（比如处理后GUID-1的values为空，要删掉这个键）
• 要求是通用SQL，不需要提前指定具体GUID，能作用于表中每一行

通用SQL解决方案 #

以下是无需预判GUID角色的单条SQL，使用PostgreSQL的JSONB函数实现：

WITH target_items AS (
    -- 替换这里的数组为你要移除的目标列表
    SELECT unnest('["GUID-3", "GUID-value-4", "GUID-value-1"]'::text[]) AS item
),
processed_records AS (
    SELECT
        mt.id, -- 替换为你的表主键或唯一标识列
        jsonb_object_agg(
            guid_key,
            jsonb_set(guid_val, '{values}', 
                (guid_val->'values') - (SELECT array_agg(item) FROM target_items WHERE item = ANY((guid_val->'values')::text[]))
            )
        ) AS cleaned_record1
    FROM metaTable mt,
         jsonb_each(mt.meta->'records'->'record1') AS guid(guid_key, guid_val)
    WHERE guid_key NOT IN (SELECT item FROM target_items) -- 先移除指定的键
    GROUP BY mt.id
),
final_cleaned_records AS (
    SELECT
        id,
        jsonb_object_agg(guid_key, guid_val) AS final_record1
    FROM processed_records,
         jsonb_each(cleaned_record1) AS guid(guid_key, guid_val)
    WHERE jsonb_array_length(guid_val->'values') > 0 -- 移除values为空的键
    GROUP BY id
)
UPDATE metaTable mt
SET meta = jsonb_set(
    mt.meta,
    '{records, record1}',
    (SELECT final_record1 FROM final_cleaned_records fcr WHERE fcr.id = mt.id)
)
WHERE EXISTS (SELECT 1 FROM final_cleaned_records fcr WHERE fcr.id = mt.id);

代码说明 #

• target_items CTE：定义要移除的目标列表，直接替换数组内容即可
• processed_records CTE：
  • 遍历record1下的所有GUID键值对
  • 先过滤掉目标列表中的键
  • 对每个保留的键，从其values数组中移除目标列表中的元素
• final_cleaned_records CTE：过滤掉values数组为空的键，得到最终的清理后结构
• UPDATE语句：将清理后的record1写回原JSONB列

注意事项 #

1. 把SQL中的id替换为你表的主键或唯一标识列，确保能正确匹配每一行
2. 如果records下有多个类似record1的节点，可以调整jsonb_each的路径来适配，或者扩展逻辑处理多个节点
3. 测试时可以先把UPDATE换成SELECT，验证清理后的结果是否符合预期

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