Oracle数据库中JSON数组字段转拼接字符串的实现求助

你之前的写法无法得到预期结果，原因是$.Prenoms[*]返回的是数组元素的集合，直接用FORMAT JSON结合VARCHAR2类型无法将这些元素自动拼接为逗号分隔的字符串，因此返回空值。

以下是两种可行的解决方案：

方案一：嵌套JSON_TABLE + LISTAGG聚合 #

通过嵌套的JSON_TABLE拆分Prenoms数组，再用LISTAGG函数将元素拼接为逗号分隔的字符串：

SELECT 
  t.NOM,
  LISTAGG(p.prenom, ', ') WITHIN GROUP (ORDER BY p.rn) AS PRENOMS,
  t.DATE_NAISS
FROM JSON_TABLE(
  '[
    {
        "Id": 123,
        "Nom": "Toto",
        "Prenoms": [
            "Pascal",
            "Vincent"
        ],
        "DateNaissance": "1983-01-01T00:00:00"
    }]', 
  '$[*]' COLUMNS (
    "NOM"        PATH '$.Nom',
    "DATE_NAISS" DATE PATH '$.DateNaissance',
    "PRENOMS_ARR" JSON PATH '$.Prenoms'
  )
) t,
JSON_TABLE(
  t.PRENOMS_ARR,
  '$[*]' COLUMNS (
    prenom VARCHAR2(50) PATH '$',
    rn FOR ORDINALITY -- 保留数组元素的原始顺序
  )
) p
GROUP BY t.NOM, t.DATE_NAISS;

方案二：子查询聚合 #

在主查询中通过子查询完成数组元素的拆分与聚合，写法更紧凑：

SELECT 
  NOM,
  (SELECT LISTAGG(prenom, ', ') WITHIN GROUP (ORDER BY rn)
   FROM JSON_TABLE(PRENOMS_ARR, '$[*]' COLUMNS (
     prenom VARCHAR2(50) PATH '$',
     rn FOR ORDINALITY
   ))) AS PRENOMS,
  DATE_NAISS
FROM JSON_TABLE(
  '[
    {
        "Id": 123,
        "Nom": "Toto",
        "Prenoms": [
            "Pascal",
            "Vincent"
        ],
        "DateNaissance": "1983-01-01T00:00:00"
    }]', 
  '$[*]' COLUMNS (
    "NOM"        PATH '$.Nom',
    "DATE_NAISS" DATE PATH '$.DateNaissance',
    "PRENOMS_ARR" JSON PATH '$.Prenoms'
  )
) t;

执行以上任意一种SQL，都会得到你需要的结果：PRENOMS字段的值为Pascal, Vincent。

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