使用JSON_TABLE提取嵌套JSON数组时keyNames返回NULL如何修改查询

问题原因 #

你遇到的返回NULL的核心原因是：JSON结构里的keyNames是数组类型，直接将数组路径$.keyNames映射为TEXT类型时，JSON_TABLE无法完成隐式类型转换，所以返回空值。另外你原查询中keyId的定义行末尾缺少逗号，也会存在语法报错风险。

解决方案 #

根据你的实际使用场景选择对应修改方案即可：

• 若keyNames数组固定只有1个元素，直接提取数组第一位元素即可，修改后的查询如下：

SELECT j.keyId, j.keyNames, j.keyDesc
FROM table t, JSON_TABLE(value, '$[*]' 
    COLUMNS(
      keyId TEXT PATH '$.keyId',
      keyNames TEXT PATH '$.keyNames[0]',
      keyDesc TEXT PATH '$.keyDesc')
    ) AS j;

• 若keyNames数组可能包含多个元素，需要将所有元素拼接为逗号分隔的字符串，可通过嵌套解析数组再聚合的方式实现，修改后的查询如下：

SELECT j.keyId, GROUP_CONCAT(j.keyName SEPARATOR ', ') AS keyNames, j.keyDesc
FROM table t, 
JSON_TABLE(value, '$[*]' 
    COLUMNS(
      keyId TEXT PATH '$.keyId',
      keyDesc TEXT PATH '$.keyDesc',
      NESTED PATH '$.keyNames[*]' COLUMNS (
          keyName TEXT PATH '$'
      )
    )
) AS j
GROUP BY j.keyId, j.keyDesc;

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