如何将嵌套JSON键转换为关系型数据库列？

我的表中有一个名为run_dtls_document的JSON类型列，结构如下：

{
  "ACT_CASHFLOW_FE_COLS": [
    {
      "MCR": "MCR_1",
      "COLUMN_VALUE": "UVW"
    },
    {
      "MCR": "MCR_2",
      "COLUMN_VALUE": "XYZ"
    }
  ],
  "ACT_CASHFLOW_INT_RATE": [
    {
      "MCR": "MCR_1",
      "COLUMN_VALUE": "UVW1"
    },
    {
      "MCR": "MCR_2",
      "COLUMN_VALUE": "XYZ1"
    }
  ],
  "ACT_CASHFLOW_INP_COLS": [
    {
      "MCR": "MCR_1",
      "COLUMN_VALUE": "UVW2"
    },
    {
      "MCR": "MCR_2",
      "COLUMN_VALUE": "XYZ2"
    }
  ],
  "ASS_CASHFLOW_FE_COLS": [
    {
      "MCR": "MCR_1",
      "COLUMN_VALUE": "UVW3"
    },
    {
      "MCR": "MCR_2",
      "COLUMN_VALUE": "XYZ3"
    }
  ],
  "ASS_CASHFLOW_INT_RATE": [
    {
      "MCR": "MCR_1",
      "COLUMN_VALUE": "UVW4"
    },
    {
      "MCR": "MCR_2",
      "COLUMN_VALUE": "XYZ4"
    }
  ],
  "ASS_CASHFLOW_INP_COLS": [
    {
      "MCR": "MCR_1",
      "COLUMN_VALUE": "UVW5"
    },
    {
      "MCR": "MCR_2",
      "COLUMN_VALUE": "XYZ5"
    }
  ]
}

预期输出 #

需要转换为如下关系型表结构：

原查询问题分析 #

原查询中多次使用NESTED PATH会生成笛卡尔积，导致结果行数远超预期，且无法按MCR将对应值关联到同一行。

解决方案 #

可以先将所有JSON数组拆分为包含类型标识、MCR和值的行，再通过PIVOT操作将类型转为列：

WITH json_unpivoted AS (
  SELECT
    jt.mcr,
    jt.col_type,
    jt.col_value
  FROM frd,
       JSON_TABLE(
         frd.run_dtls_document,
         '$.*[*]' COLUMNS(
           mcr VARCHAR2(320) PATH '$.MCR',
           col_type VARCHAR2(100) PATH '$._key',
           col_value VARCHAR2(320) PATH '$.COLUMN_VALUE'
         )
       ) jt
)
SELECT *
FROM json_unpivoted
PIVOT (
  MAX(col_value)
  FOR col_type IN (
    'ACT_CASHFLOW_FE_COLS' AS act_cashflow_fe_cols,
    'ACT_CASHFLOW_INT_RATE' AS act_cashflow_int_rate,
    'ACT_CASHFLOW_INP_COLS' AS act_cashflow_inp_cols,
    'ASS_CASHFLOW_FE_COLS' AS ass_cashflow_fe_cols,
    'ASS_CASHFLOW_INT_RATE' AS ass_cashflow_int_rate,
    'ASS_CASHFLOW_INP_COLS' AS ass_cashflow_inp_cols
  )
)
ORDER BY mcr;

说明 #

1. JSON_TABLE拆分：使用$.*[*]遍历所有顶级数组及其元素，同时通过$._key获取数组的名称（即列类型标识），将所有数据拆分为三列：mcr、col_type、col_value。
2. PIVOT转换：将col_type的不同值转为对应的列名，用MAX(col_value)聚合确保每个MCR+类型组合只返回一个值。

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