Oracle中JSON_TABLE解析JSON转表时如何避免行重复与空列缺失

• 原查询使用多个同级NESTED PATH解析_1/_2/_3子对象，Oracle中JSON_TABLE的多个同级嵌套路径默认执行笛卡尔积关联，会把同一个obj_type下存在的多个子对象拆成多行，且子对象不存在时会直接过滤行，不会返回空值。
• 原查询列定义存在错误：_1节点下的end字段被错误命名为_2_end，_2/_3节点的列名也和预期输出的字段名不匹配。

不需要使用嵌套路径拆分子对象，直接在$.r[*]的列定义中通过完整路径直接取每个子对象的start、end属性即可，不存在的属性会自动返回null，且每个数组元素只会生成一行数据。
修正后的SQL如下：

WITH d (department_data) AS (SELECT (UTL_RAW.cast_to_raw ('{
  "r": [
    {
      "obj_type": "A",
      "_1":  {
        "start": "1",
        "end": "2"
        },
        "_2":  {
        "start": "15",
        "end": "25"
        },
        "_3":  {
        "start": "26",
        "end": "33"
        }
    },
    {
      "obj_type": "B",
      "_1": {
        "start": "1",
        "end": "2"
    },
        "_2":  {
        "start": "3",
        "end": "12"
        }
    },    {
      "obj_type": "C",
      "_2":{
        "start": "1",
        "end": "2"
    }
    },    {
      "obj_type": "D",
      "_3": {
        "start": "",
        "end": "2"
    }
    }
  ]
}')) FROM DUAL)
SELECT j.*
  FROM d,
       JSON_TABLE (
           d.department_data,
           '$.r[*]'
           COLUMNS (
               obj_type PATH '$.obj_type',
               "1_start" PATH '$._1.start',
               "1_end" PATH '$._1.end',
               "2_start" PATH '$._2.start',
               "2_end" PATH '$._2.end',
               "3_start" PATH '$._3.start',
               "3_end" PATH '$._3.end'
           )
       ) j

执行后会得到4行数据，完全匹配预期结构：

• obj_type为A的行返回所有_1/_2/_3的start、end值
• obj_type为B的行3_start、3_end返回null
• obj_type为C的行1_start、1_end、3_start、3_end返回null
• obj_type为D的行1_start、1_end、2_start、2_end返回null，3_start为空字符串

内容的提问来源于stack exchange，提问作者Pierre-olivier Gendraud