PostgreSQL 13.12中如何移除JSON列数组属性的指定值？

针对PostgreSQL 13.12中json类型列的数组属性移除特定值的需求，可以通过拆分数组、过滤元素、重新聚合的方式实现，以下是具体操作：

1. 查询验证处理结果 #

如果只是想查看处理后的数据而不修改原表，执行以下SQL：

SELECT
  json_build_object(
    'name', (data->>'name')::text,
    'actions', json_agg(action)
  ) AS processed_data
FROM test,
     json_array_elements(data->'actions') AS action
WHERE action->>'$' != 'D'
GROUP BY data->>'name';

执行后会返回移除了所有"D"值的新json对象。

2. 直接更新原表数据 #

如果需要修改原表中的数据，执行更新语句：

UPDATE test
SET data = (
  SELECT json_build_object(
           'name', (test.data->>'name')::text,
           'actions', json_agg(action)
         )
  FROM json_array_elements(test.data->'actions') AS action
  WHERE action->>'$' != 'D'
  GROUP BY test.data->>'name'
);

逻辑说明 #

• json_array_elements(data->'actions')：将json列中的actions数组拆分为单个元素的行记录
• WHERE action->>'$' != 'D'：过滤掉值为"D"的元素
• json_agg(action)：把过滤后的元素重新聚合成json数组
• json_build_object：重新构造包含原name和处理后actions的json对象，替换原列值

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