如何在PL/SQL中将单列逗号分隔值转换为JSON数组

方案1：通用稳妥方案（支持特殊字符自动转义） #

核心思路是先将逗号分隔的字符串拆分为单行单个值，再通过JSON_ARRAYAGG函数聚合为标准JSON数组，该方法会自动处理值中的双引号、反斜杠等JSON特殊字符，不会出现格式错误。
示例代码如下：

SELECT 
  JSON_ARRAYAGG(TRIM(split_val) ORDER BY seq) AS result_json
FROM 
  your_table t,
  -- 拆分逗号分隔字符串为行，seq保留原始元素顺序
  (SELECT 
     LEVEL AS seq,
     REGEXP_SUBSTR(t.Col1, '[^,]+', 1, LEVEL) AS split_val
   FROM DUAL
   CONNECT BY LEVEL <= REGEXP_COUNT(t.Col1, ',') + 1)
WHERE 
  t.Col1 IS NOT NULL
-- 按原表主键分组，处理多行数据时避免串值，单条数据转换可删除该子句
GROUP BY t.primary_key_col, t.Col1;

如果你的环境支持JSON_TABLE，可以用更简洁的拆分写法：

SELECT 
  JSON_ARRAYAGG(TRIM(value) ORDER BY rownum) AS result_json
FROM 
  your_table t,
  JSON_TABLE(
    '["' || REPLACE(t.Col1, ',', '","') || '"]',
    '$[*]' COLUMNS (value VARCHAR2(4000) PATH '$')
  )
WHERE 
  t.Col1 IS NOT NULL
-- 多数据行场景保留分组，单条转换可删除
GROUP BY t.primary_key_col, t.Col1;

方案2：快速字符串拼接方案（仅适用于数据干净的场景） #

如果确认Col1存储的所有分隔元素都不包含双引号、逗号、反斜杠等JSON特殊字符，可以直接用字符串替换实现，性能更高：

SELECT 
  '["' || REPLACE(Col1, ',', '","') || '"]' AS result_json
FROM your_table
WHERE Col1 IS NOT NULL;

注意：该方案没有特殊字符转义逻辑，数据不符合前提条件时会生成非法JSON，不建议生产环境通用。

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