PostgreSQL如何删除jsonb数组所有元素中的指定字段

实现方案 #

你可以通过拆分数组→逐元素删除字段→重新聚合数组的逻辑实现批量删除，兼容PostgreSQL 9.4及以上所有版本：

UPDATE items
SET data = (
    SELECT jsonb_agg(elem - 'count')
    FROM jsonb_array_elements(data) AS elem
)
-- 可选过滤条件，避免无意义更新
WHERE jsonb_typeof(data) = 'array' -- 只处理数组类型的data值
  AND data @> '[{"count": 1}]'::jsonb; -- 只处理数组中确实存在count字段的行

原理解释 #

• jsonb_array_elements(data)：将data字段的jsonb数组拆分为多行，每行对应数组中的一个对象元素
• elem - 'count'：jsonb对象的减号操作符，直接删除当前对象的count键值对，若元素本身没有count字段也不会报错，会原样返回
• jsonb_agg(...)：将处理后的所有对象元素重新聚合为jsonb数组，回写至data字段

可选优化（PostgreSQL 12+版本） #

如果你使用PostgreSQL 12及以上版本，可以通过JSON路径表达式实现更简洁的写法：

UPDATE items
SET data = jsonb_path_query_array(data, '$[*] - "count"')
WHERE jsonb_typeof(data) = 'array';

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