如何使用MySQL从JSON数组中移除指定value的对象？

假设你的表名为your_table，存储JSON的字段是data_source，要移除entries数组中value等于目标值x的对象，不能直接用JSON_REMOVE的通配符路径批量操作，需要先拆解数组过滤后重新组装，具体步骤如下：

1. 验证过滤后的结果（可选） #

先执行查询确认处理后的JSON是否符合预期：

SELECT 
  data_source,
  JSON_OBJECT(
    'entries',
    JSON_ARRAYAGG(entry)
  ) AS filtered_data
FROM your_table,
     JSON_TABLE(
       data_source,
       '$.entries[*]' COLUMNS(
         entry JSON PATH '$',
         value INT PATH '$.value'
       )
     ) AS json_entries
WHERE json_entries.value != x -- 将x替换为你要移除的目标value值
GROUP BY data_source;

2. 直接更新表中的JSON字段 #

如果验证结果正确，执行更新语句替换原JSON：

UPDATE your_table t
JOIN (
  SELECT 
    data_source,
    JSON_OBJECT(
      'entries',
      JSON_ARRAYAGG(entry)
    ) AS updated_data
  FROM your_table,
       JSON_TABLE(
         data_source,
         '$.entries[*]' COLUMNS(
           entry JSON PATH '$',
           value INT PATH '$.value'
         )
       ) AS json_entries
  WHERE json_entries.value != x -- 替换为目标值x
  GROUP BY data_source
) AS sub_query ON t.data_source = sub_query.data_source
SET t.data_source = sub_query.updated_data;

原理说明 #

• JSON_TABLE：将JSON数组拆解为关系型行数据，每行对应数组中的一个对象，同时提取出value字段用于过滤。
• JSON_ARRAYAGG：将过滤后的对象重新聚合为JSON数组。
• JSON_OBJECT：将新数组包装回原JSON的结构（保持entries键名）。

注意：该方法要求MySQL版本≥8.0，因为JSON_TABLE是8.0新增的函数。

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