Oracle 19.2嵌套JSON数组数据提取SQL查询技术求助

示例JSON（匹配你的业务结构） #

{
  "account": {
    "accountId": "ACC001",
    "twrrNof": [
      {"period": "D1", "value": 0.012},
      {"period": "D7", "value": 0.035},
      {"period": "M1", "value": 0.089},
      {"period": "M3", "value": 0.21},
      {"period": "M6", "value": 0.45},
      {"period": "Y1", "value": 0.92},
      {"period": "Y3", "value": 2.76},
      {"period": "Y5", "value": 4.81}
    ],
    "sleeves": [
      {
        "sleeveId": "SLV001",
        "twrrNof": [
          {"period": "D1", "value": 0.015},
          {"period": "D7", "value": 0.042},
          {"period": "M1", "value": 0.095},
          {"period": "M3", "value": 0.23},
          {"period": "M6", "value": 0.48},
          {"period": "Y1", "value": 0.98},
          {"period": "Y3", "value": 2.89},
          {"period": "Y5", "value": 5.02}
        ]
      },
      {
        "sleeveId": "SLV002",
        "twrrNof": [
          {"period": "D1", "value": 0.011},
          {"period": "D7", "value": 0.033},
          {"period": "M1", "value": 0.087},
          {"period": "M3", "value": 0.19},
          {"period": "M6", "value": 0.42},
          {"period": "Y1", "value": 0.87},
          {"period": "Y3", "value": 2.61},
          {"period": "Y5", "value": 4.65}
        ]
      }
    ]
  }
}

目标输出格式 #

最终SQL语句 #

假设JSON数据存储在表json_data的json_col字段中：

-- 提取账户层级的twrrNof数据
SELECT
  account_id,
  'ACCOUNT' AS level_type,
  account_id AS level_id,
  period,
  twrr_value AS twrr_nof_value
FROM json_data jd,
  JSON_TABLE(jd.json_col, '$.account'
    COLUMNS (
      account_id VARCHAR2(50) PATH '$.accountId',
      -- 嵌套解析账户的twrrNof数组
      NESTED PATH '$.twrrNof[*]'
        COLUMNS (
          period VARCHAR2(10) PATH '$.period',
          twrr_value NUMBER(10,4) PATH '$.value'
        )
    )
  )
UNION ALL
-- 提取子账户（sleeve）层级的twrrNof数据
SELECT
  account_id,
  'SLEEVE' AS level_type,
  sleeve_id AS level_id,
  period,
  twrr_value AS twrr_nof_value
FROM json_data jd,
  JSON_TABLE(jd.json_col, '$.account'
    COLUMNS (
      account_id VARCHAR2(50) PATH '$.accountId',
      -- 第一层嵌套：遍历所有sleeve
      NESTED PATH '$.sleeves[*]'
        COLUMNS (
          sleeve_id VARCHAR2(50) PATH '$.sleeveId',
          -- 第二层嵌套：遍历当前sleeve的twrrNof数组
          NESTED PATH '$.twrrNof[*]'
            COLUMNS (
              period VARCHAR2(10) PATH '$.period',
              twrr_value NUMBER(10,4) PATH '$.value'
            )
        )
    )
  );

核心要点说明 #

1. NESTED PATH用法：这是Oracle JSON_TABLE处理嵌套数组的核心，外层字段（如account_id、sleeve_id）会自动与内层数组的每一行关联，无需额外JOIN
2. JSON路径规则：
   • $表示JSON根节点
   • [*]表示遍历数组中的所有元素
   • PATH指定字段在JSON中的具体位置
3. 类型匹配：提取字段的类型（VARCHAR2、NUMBER）需与JSON中对应值的类型一致，避免转换报错
4. 19.2版本兼容性：该语法完全支持Oracle 19.2，无需启用额外特性

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