如何使用JSON_TABLE拼接NESTED PATH提取的嵌套JSON数组值

你现在得到多行结果是因为使用了NESTED PATH '$.keyNames[*]'把keyNames数组里的每个元素都拆成了单独的行，要合并成单个逗号分隔的字符串有两种常用方案：

方案1：分组聚合（兼容原有逻辑，改动最小） #

不需要修改JSON_TABLE的结构，只需要在查询末尾加分组和字符串聚合函数即可，不同数据库的聚合函数略有差异：

MySQL/MariaDB 写法 #

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

PostgreSQL/SQL Server 2017+ 写法 #

把聚合函数替换为STRING_AGG即可：

SELECT 
  j.keyId,
  STRING_AGG(j.keyNames, ', ') AS keyNames,
  j.keyDesc
FROM asknow a, JSON_TABLE(value, '$[*]' 
    COLUMNS(
      keyId TEXT PATH '$.keyId',
      NESTED PATH '$.keyNames[*]' COLUMNS (keyNames TEXT PATH '$'),
      keyDesc TEXT PATH '$.keyDesc')
    ) AS j
GROUP BY j.keyId, j.keyDesc;

Oracle 11g+ 写法 #

SELECT 
  j.keyId,
  LISTAGG(j.keyNames, ', ') WITHIN GROUP (ORDER BY j.keyNames) AS keyNames,
  j.keyDesc
FROM asknow a, JSON_TABLE(value, '$[*]' 
    COLUMNS(
      keyId TEXT PATH '$.keyId',
      NESTED PATH '$.keyNames[*]' COLUMNS (keyNames TEXT PATH '$'),
      keyDesc TEXT PATH '$.keyDesc')
    ) AS j
GROUP BY j.keyId, j.keyDesc;

方案2：直接提取数组不拆分（性能更优） #

如果不需要先拆成单行处理，完全可以去掉NESTED PATH配置，直接把keyNames数组转成逗号分隔的字符串，避免后续分组操作，以MySQL为例：

SELECT 
  j.keyId,
  REPLACE(REPLACE(REPLACE(JSON_UNQUOTE(JSON_EXTRACT(value, '$[0].keyNames')), '["', ''), '"]', ''), '","', ', ') AS keyNames,
  j.keyDesc
FROM asknow a, JSON_TABLE(value, '$[*]' 
    COLUMNS(
      keyId TEXT PATH '$.keyId',
      keyDesc TEXT PATH '$.keyDesc')
    ) AS j;

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