如何用JSON_TABLE处理MariaDB/MySQL中动态顺序的JSON属性数组？

问题根源 #

你原查询依赖数组索引$.attributes[0].value取值，但当attributes元素顺序不固定、数量可变时，这种方式会导致取值错误。而直接在JSON_TABLE的PATH参数中使用REPLACE(JSON_SEARCH(...))会报错，因为PATH要求是静态JSON路径表达式，不能是动态生成的字符串结果。

推荐方案一：拆分数组后用条件聚合转列 #

这种方法逻辑清晰，扩展性强，适合处理多属性的场景，同时能优雅兼容元素缺失的情况：

SELECT
  main.id,
  main.position,
  COALESCE(MAX(CASE WHEN attr.name = 'First Name' THEN attr.value END), '') AS firstName,
  COALESCE(MAX(CASE WHEN attr.name = 'Last Name' THEN attr.value END), '') AS lastName
FROM mytable
-- 提取主数据和完整的attributes数组
JOIN JSON_TABLE(mytable.data, '$'
  COLUMNS (
    id INT(10) PATH '$.id',
    position VARCHAR(20) PATH '$.position',
    attributes JSON PATH '$.attributes'
  )
) AS main
-- 将attributes数组拆分为每行一个name-value对
LEFT JOIN JSON_TABLE(main.attributes, '$[*]'
  COLUMNS (
    name VARCHAR(50) PATH '$.name',
    value VARCHAR(50) PATH '$.value'
  )
) AS attr ON 1=1
-- 按主数据字段分组，聚合得到对应列
GROUP BY main.id, main.position;

关键细节： #

• 用LEFT JOIN确保即使attributes数组为空或缺少指定元素，主数据依然会被返回；
• COALESCE配合MAX聚合，将多行的name-value对转为单列，同时为缺失元素设置默认值（这里用空字符串，可按需修改）；
• 新增属性时只需在CASE WHEN中添加新的判断，无需修改JSON_TABLE结构。

备选方案二：动态路径取值（适合少量属性场景） #

如果不需要处理大量属性，也可以直接在SELECT中通过动态生成路径来取值：

SELECT
  t.id,
  t.position,
  -- 动态获取First Name对应的值
  COALESCE(
    JSON_UNQUOTE(
      JSON_EXTRACT(
        mytable.data,
        REPLACE(
          JSON_UNQUOTE(JSON_SEARCH(mytable.data, 'one', 'First Name', NULL, '$.attributes[*].name')),
          '.name',
          '.value'
        )
      )
    ),
    ''
  ) AS firstName,
  -- 动态获取Last Name对应的值
  COALESCE(
    JSON_UNQUOTE(
      JSON_EXTRACT(
        mytable.data,
        REPLACE(
          JSON_UNQUOTE(JSON_SEARCH(mytable.data, 'one', 'Last Name', NULL, '$.attributes[*].name')),
          '.name',
          '.value'
        )
      )
    ),
    ''
  ) AS lastName
FROM mytable
JOIN JSON_TABLE(mytable.data, '$'
  COLUMNS (
    id INT(10) PATH '$.id',
    position VARCHAR(20) PATH '$.position'
  )
) AS t;

关键细节： #

• JSON_SEARCH查找目标name的路径，返回带引号的字符串（如"$.attributes[1].name"）；
• JSON_UNQUOTE去除路径的引号，REPLACE将路径中的.name替换为.value，得到目标值的路径；
• JSON_EXTRACT取出对应值，再用JSON_UNQUOTE去除值的引号（针对字符串类型）；
• COALESCE处理元素缺失的情况，返回默认值。

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