MySQL中auto表info列嵌套JSON数组的查询问题

你之前调用JSON相关函数返回NULL的核心原因是没有先将外层JSON数组拆分为独立行，直接对数组整体调用函数无法定位到内层元素，需要搭配JSON_TABLE函数（MySQL 8.0及以上版本支持）完成数组打平操作，以下是对应实现方案：

基础查询：拆解外层JSON数组为独立行 #

先将info列中每个id对应的对象拆成单独行，拿到对应id的values值：

SELECT 
  j.id,
  j.`values`
FROM auto,
JSON_TABLE(
  auto.info,
  '$[*]' COLUMNS(
    id INT PATH '$.id',
    `values` JSON PATH '$.values'
  )
) AS j
WHERE j.id IN (7,8,13);

按要求格式输出拼接结果 #

针对id7、id8的数值数组，和id13的对象数组两种结构分别处理，拼接为你需要的输出格式：

SELECT 
  CONCAT('id',j.id) AS id_label,
  CASE 
    -- 处理纯数值类型的values数组
    WHEN j.id IN (7,8) THEN CONCAT_WS(' ', 
      JSON_UNQUOTE(JSON_EXTRACT(j.`values`, '$[0]')), 
      JSON_UNQUOTE(JSON_EXTRACT(j.`values`, '$[1]'))
    )
    -- 处理对象类型的values数组，提取name字段拼接
    WHEN j.id =13 THEN (
      SELECT GROUP_CONCAT(JSON_UNQUOTE(JSON_EXTRACT(item, '$.name')) SEPARATOR ' ')
      FROM JSON_TABLE(j.`values`, '$[*]' COLUMNS(item JSON PATH '$')) AS t
    )
  END AS values_content
FROM auto,
JSON_TABLE(
  auto.info,
  '$[*]' COLUMNS(
    id INT PATH '$.id',
    `values` JSON PATH '$.values'
  )
) AS j
WHERE j.id IN (7,8,13);

拆分values为独立列做聚合运算 #

如果需要对id7的数值做最大值、最小值等聚合运算，可将values数组进一步拆分为独立行后计算，支持长度不固定的数组场景：

SELECT 
  MIN(val) AS id7_min_value,
  MAX(val) AS id7_max_value,
  AVG(val) AS id7_avg_value
FROM auto,
-- 第一层拆解外层JSON数组
JSON_TABLE(
  auto.info,
  '$[*]' COLUMNS(
    id INT PATH '$.id',
    `values` JSON PATH '$.values'
  )
) AS j,
-- 第二层拆解id7的values数值数组为独立行
JSON_TABLE(
  j.`values`,
  '$[*]' COLUMNS(val INT PATH '$')
) AS vals
WHERE j.id =7;

如果你使用的是MySQL 5.7及以下版本，不支持JSON_TABLE函数，需要通过自定义变量+递归的方式手动拆分数组，实现复杂度较高，建议升级到MySQL 8.0以上版本使用上述方案。

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