无需UNION ALL：高效解析CLOB中JSON双列表的SQL方案

针对CLOB列中包含required和optional两个数组的JSON数据，要生成包含FIELD、VALUE、REQUIRED字段的结果集，可通过单次JSON_TABLE解析同时处理两个数组的方式替代UNION ALL，避免重复扫描表和解析CLOB，提升大JSON场景下的性能。

核心SQL实现 #

假设表名为your_table，存储JSON的CLOB列名为json_clob，JSON结构示例如下：

{
  "required": [{"field": "name", "value": "张三"}, {"field": "id", "value": "1001"}],
  "optional": [{"field": "email", "value": "zhangsan@xxx.com"}, {"field": "phone", "value": "138xxxx1234"}]
}

使用以下SQL生成目标结果集：

SELECT
  jt.field AS FIELD,
  jt.field_value AS VALUE,
  jt.is_required AS REQUIRED
FROM
  your_table t,
  JSON_TABLE(
    t.json_clob,
    '$'
    COLUMNS (
      -- 解析required数组，标记为必填（Y）
      NESTED PATH '$.required[*]' COLUMNS (
        field VARCHAR2(100) PATH '$.field',
        field_value VARCHAR2(200) PATH '$.value',
        is_required VARCHAR2(1) PATH '''Y'''
      ),
      -- 解析optional数组，标记为非必填（N）
      NESTED PATH '$.optional[*]' COLUMNS (
        field VARCHAR2(100) PATH '$.field',
        field_value VARCHAR2(200) PATH '$.value',
        is_required VARCHAR2(1) PATH '''N'''
      )
    )
  ) jt;

性能优化说明 #

1. 单次解析CLOB：该SQL仅对CLOB列做一次JSON解析，而原UNION ALL方案会扫描两次表、解析两次CLOB，大JSON场景下性能差异明显。
2. 利用JSON索引：如果CLOB列上创建了JSON搜索索引，解析速度会进一步提升，创建语句示例：

   CREATE SEARCH INDEX json_clob_idx ON your_table(json_clob) FOR JSON;
   

关于$.(required|optional)[*]语法无效的说明 #

Oracle JSON路径不支持正则匹配式的路径写法（如$.(required|optional)[*]），因此需要通过NESTED PATH分别指定两个数组的路径进行解析。

内容的提问来源于stack exchange，提问作者marciel.deg