PostgreSQL中从多层嵌套JSONB删除指定ID元素的方法

假设你的表名为your_table，存储JSON数据的jsonb列名为data，可以通过以下SQL语句实现删除操作：

仅处理指定Drive（如C_Drive）的场景 #

如果确定要删除的key元素属于C_Drive，可以用更精准的语句：

UPDATE your_table
SET data = jsonb_set(
  data,
  '{drive}',
  (
    SELECT jsonb_agg(
      CASE
        WHEN d->>'name' = 'C_Drive' THEN
          d || jsonb_build_object(
            'key',
            (SELECT jsonb_agg(k) FROM jsonb_array_elements(d->'key') k WHERE (k->>'id')::int != 3)
          )
        ELSE d
      END
    )
    FROM jsonb_array_elements(data->'drive') d
  )
)
WHERE data @> '{"drive": [{"key": [{"id": 3}]}]}';

处理所有Drive的通用场景 #

如果不确定目标key属于哪个drive，需要遍历所有drive对象处理key数组：

UPDATE your_table
SET data = jsonb_set(
  data,
  '{drive}',
  (
    SELECT jsonb_agg(
      d || jsonb_build_object(
        'key',
        (SELECT jsonb_agg(k) FROM jsonb_array_elements(d->'key') k WHERE (k->>'id')::int != 3)
      )
    )
    FROM jsonb_array_elements(data->'drive') d
  )
)
WHERE data @> '{"drive": [{"key": [{"id": 3}]}]}';

关键逻辑说明 #

• jsonb_array_elements()：将数组类型的JSON字段展开为行数据，方便逐个处理对象
• jsonb_agg()：将处理后的行数据重新聚合为JSON数组
• d || jsonb_build_object('key', ...)：替换原drive对象中的key字段为过滤后的新数组
• WHERE条件：只更新包含目标id的记录，避免无意义的全表更新

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