MySQL 8中根据属性移除JSON数组对象及重组数组的方法咨询

问题背景 #

假设MySQL 8某列存储如下JSON数据：

{
  "values": [
    {
      "id": 1,
      "value": "test1"
    },
    {
      "id": 2,
      "value": "test2"
    },
    {
      "id": 3,
      "value": "test3"
    }
  ]
}

需求是移除数组中id=2的对象，尝试用JSON_REMOVE但不确定路径写法，同时已完成JSON_TABLE转表过滤步骤，需了解如何用JSON_ARRAYAGG重新生成包含多列的对象数组。

一、JSON_REMOVE的局限性 #

MySQL的JSON_REMOVE需要明确指定数组元素的索引位置（如$.values[1]），但它不支持通过属性值（如id=2）直接定位元素。因为MySQL的JSON Path语法不支持在路径中添加条件过滤，无法直接写$.values[*]?(@.id==2)这类路径来删除元素。

若要使用JSON_REMOVE，需先查询目标元素的索引，再传入索引路径，示例如下：

SET @j = '{ "values": [ { "id": 1, "value": "test1" }, { "id": 2, "value": "test2" }, { "id": 3, "value": "test3" } ] }';
-- 获取id=2的元素路径，提取索引
SET @index_path = JSON_UNQUOTE(JSON_SEARCH(@j, 'one', 2, NULL, '$.values[*].id'));
SET @index = SUBSTRING_INDEX(SUBSTRING_INDEX(@index_path, '[', -1), ']', 1);
-- 执行删除
SELECT JSON_REMOVE(@j, CONCAT('$.values[', @index, ']')) AS result;

这种方法需额外步骤获取索引，且当数组存在多个相同id元素时，JSON_SEARCH的'one'参数仅返回第一个匹配项，灵活性较差。

二、JSON_TABLE + JSON_ARRAYAGG 生成对象数组 #

你已完成转表过滤步骤，接下来可通过JSON_OBJECT将每行的列数据转为单个JSON对象，再用JSON_ARRAYAGG将这些对象聚合为数组，最后用JSON_OBJECT包裹成外层JSON结构，完整SQL如下：

SET @j = '{ "values": [ { "id": 1, "value": "test1" }, { "id": 2, "value": "test2" }, { "id": 3, "value": "test3" } ] }';

SELECT JSON_OBJECT(
  'values',
  JSON_ARRAYAGG(
    JSON_OBJECT('id', jt.id, 'value', jt.value)
  )
) AS filtered_json
FROM JSON_TABLE(
  @j,
  '$.values[*]' COLUMNS (
    id INT PATH '$.id',
    value LONGTEXT PATH '$.value'
  )
) AS jt
WHERE jt.id != 2;

执行后将得到过滤后的完整JSON结构：

{
  "values": [
    {"id": 1, "value": "test1"},
    {"id": 3, "value": "test3"}
  ]
}

三、更新原表JSON列的实际场景用法 #

如果需要更新表中的JSON列，可直接结合上述逻辑编写UPDATE语句：

UPDATE your_table
SET json_column = (
  SELECT JSON_OBJECT(
    'values',
    JSON_ARRAYAGG(
      JSON_OBJECT('id', jt.id, 'value', jt.value)
    )
  )
  FROM JSON_TABLE(
    json_column,
    '$.values[*]' COLUMNS (
      id INT PATH '$.id',
      value LONGTEXT PATH '$.value'
    )
  ) AS jt
  WHERE jt.id != 2
)
-- 添加你的行过滤条件
WHERE id = 1;

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