如何修改Oracle JSON_TABLE语句实现JSON到表的笛卡尔积转换

示例JSON数据 #

{
  "addresses": [
    {"city": "New York", "zip": "10001"},
    {"city": "London", "zip": "SW1A 1AA"}
  ],
  "departments": [
    {"dept_id": 1, "dept_name": "HR"},
    {"dept_id": 2, "dept_name": "Engineering"}
  ]
}

当前SQL代码（问题写法） #

SELECT jt.city, jt.zip, jt.dept_id, jt.dept_name
FROM your_table t,
     JSON_TABLE(t.json_col, '$'
       COLUMNS (
         city VARCHAR2(50) PATH '$.addresses[*].city',
         zip VARCHAR2(10) PATH '$.addresses[*].zip',
         dept_id NUMBER PATH '$.departments[*].dept_id',
         dept_name VARCHAR2(50) PATH '$.departments[*].dept_name'
       )
     ) jt;

实际输出（非笛卡尔积） #

预期输出（笛卡尔积） #

修改后的SQL代码 #

SELECT addr.city, addr.zip, dept.dept_id, dept.dept_name
FROM your_table t,
     -- 解析地址数组，生成独立的地址行
     JSON_TABLE(t.json_col, '$.addresses[*]'
       COLUMNS (
         city VARCHAR2(50) PATH '$.city',
         zip VARCHAR2(10) PATH '$.zip'
       )
     ) addr,
     -- 解析部门数组，生成独立的部门行
     JSON_TABLE(t.json_col, '$.departments[*]'
       COLUMNS (
         dept_id NUMBER PATH '$.dept_id',
         dept_name VARCHAR2(50) PATH '$.dept_name'
       )
     ) dept;

说明 #

原写法在同一个JSON_TABLE中同时解析两个数组，Oracle会按数组元素的索引位置配对输出，导致结果是一一对应而非笛卡尔积。

修改后通过**两个独立的JSON_TABLE**分别解析地址和部门数组，利用Oracle的隐式交叉连接（逗号分隔表）自动生成两个结果集的笛卡尔积，完全匹配预期输出。

如果需要显式指定交叉连接，也可以写成：

SELECT addr.city, addr.zip, dept.dept_id, dept.dept_name
FROM your_table t
CROSS JOIN JSON_TABLE(t.json_col, '$.addresses[*]'
       COLUMNS (
         city VARCHAR2(50) PATH '$.city',
         zip VARCHAR2(10) PATH '$.zip'
       )
     ) addr
CROSS JOIN JSON_TABLE(t.json_col, '$.departments[*]'
       COLUMNS (
         dept_id NUMBER PATH '$.dept_id',
         dept_name VARCHAR2(50) PATH '$.dept_name'
       )
     ) dept;

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