PostgreSQL中如何从JSON列表移除question键值对

由于你的列是JSON类型（非jsonb），且数组中混合了对象和简单值，直接用jsonb的-操作符无效。可以通过「转jsonb处理+数组过滤+重新聚合」的方式实现需求，具体步骤如下：

核心SQL语句 #

UPDATE your_table
SET json_column = (
  SELECT CASE
    WHEN json_column IS NULL THEN NULL
    ELSE json_agg(elem)
    END
  FROM jsonb_array_elements(json_column::jsonb) AS elem
  WHERE NOT (elem ? 'question')
)
WHERE json_column IS NOT NULL AND json_column != '[]'::json;

语句说明 #

1. 类型转换：json_column::jsonb将JSON类型转为jsonb，利用jsonb的?操作符快速判断元素是否包含question键
2. 数组拆分：jsonb_array_elements把数组拆分成单个元素，方便逐行过滤
3. 过滤逻辑：WHERE NOT (elem ? 'question')排除所有包含question键的对象，非对象元素（如字符串、数值）会被保留
4. 重新聚合：json_agg(elem)将过滤后的元素重新组合成JSON数组，刚好匹配原列的JSON类型
5. 边界处理：外层CASE保留null值，WHERE子句跳过空数组和null的行，避免无意义更新

效果验证 #

• 原数据：[{"color": "blue"}, {"question": "what time is it"}] → 处理后：[{"color": "blue"}]
• 原数据：[{"something": "something_else"}] → 处理后：保持不变
• 原数据：[]或null → 处理后：保持不变

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