如何从PostgreSQL的jsonb嵌套数组中删除指定元素

假设你的表名为your_table，存储JSONB数组的列名为data_col，需要删除的目标UUID为fccba83f-519c-4623-8b0d-600ceea629a0，可以使用以下UPDATE语句实现需求：

UPDATE your_table
SET data_col = (
  SELECT jsonb_agg(
    elem || jsonb_build_object(
      'in', 
      jsonb_build_object(
        'categories', 
        (elem->'in'->'categories') - 'fccba83f-519c-4623-8b0d-600ceea629a0'
      )
    )
  )
  FROM jsonb_array_elements(data_col) AS elem
)
WHERE data_col @> '[{"in": {"categories": ["fccba83f-519c-4623-8b0d-600ceea629a0"]}}]';

语句解释： #

• 拆分JSONB数组：jsonb_array_elements(data_col)将列中的JSONB数组拆分为单个对象元素，方便逐个处理每个对象的categories数组。
• 删除指定UUID：(elem->'in'->'categories') - '目标UUID'利用PostgreSQL 12+支持的-操作符，精准移除categories数组中的指定UUID，保留其余元素。
• 重构对象与聚合：通过jsonb_build_object重新构造包含处理后categories的in对象，再用||合并原对象的其他属性（如out字段），最后用jsonb_agg将所有处理后的对象重新聚合为JSONB数组。
• 过滤需更新的行：WHERE子句使用@>操作符匹配包含目标UUID的行，避免对无相关数据的行执行无效更新，提升执行效率。

若需删除其他UUID，只需替换语句中对应的UUID字符串即可。

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