如何在PostgreSQL中删除JSONB包含指定键的记录（无需CTE）

你的原查询未生效的原因有两点：

1. @>操作符用于检查左侧JSON是否完全包含右侧的键值对结构，但你构造的{"unit": null, "group": null}需要匹配同时包含这两个键且值为null的对象，而你的数据中对象的键对应实际值，无法匹配；
2. 你的需求是匹配任一键存在，而非同时存在两个键，原逻辑方向错误。

以下是两种无需CTE的实现方式，均可满足需求：

方法一：使用EXISTS子查询结合数组展开 #

DELETE FROM my_table
WHERE EXISTS (
  SELECT 1
  FROM jsonb_array_elements(jsonb_col) AS elem
  WHERE elem ?| ARRAY['unit', 'group']
);

• jsonb_array_elements(jsonb_col)将JSON数组拆分为单个JSON对象；
• ?|操作符用于检查JSON对象是否包含数组中的任一指定键，只要数组中有一个元素满足条件，就会删除对应的表记录。

方法二：使用JSON路径查询 #

DELETE FROM my_table
WHERE jsonb_path_exists(jsonb_col, '$[*] ? (@.unit || @.group)');

• JSON路径表达式$[*]遍历数组中的所有元素；
• ? (@.unit || @.group)判断元素是否存在unit或group键，满足条件则匹配删除。

两种方法都会删除id为1、2的记录，仅保留id为3的记录，符合你的预期。

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