JSON to Oracle Table

Question

我正在尝试使用Oracle JSON TBALE将JSON转换为Table。这是我的样本数据

with table1 as (
select '
{
"id" : "Example",
"address" : [
{
"city" : "NY",
"state" : "TN"
},
{
"city" : "Jersey city",
"state" : "NJ"
}
],

"dep" : [
{
"id" : "1",
"name" : "HR"
},
{
"id" : "1",
"name" : "Sales"
}
],
}' as col1 from dual
) 

代码：

select jt.* from 
table1 rt,
JSON_TABLE ( col1 FORMAT JSON, '$[*]'
COLUMNS
id VARCHAR2 PATH '$.id',
NESTED PATH '$.address[*]'
COLUMNS (
city  PATH '$.city',
state1  PATH '$.state'

),

NESTED PATH '$.dep[*]'
COLUMNS (
dep_id  PATH '$.id',
dept_name  PATH '$.name'

)
)jt

我得到的输出：

ID
示例		示例		示例

您可以使用以下示例。事实上，它生成了2个嵌套表并将它们连接起来。也许在json_table方面有比我更专业的人可以提供更好的解决方案。

with table1 as (
select '
{
"id" : "Example",
"address" : [
{
"city" : "NY",
"state" : "TN"
},
{
"city" : "Jersey city",
"state" : "NJ"
}
],

"dep" : [
{
"id" : "1",
"name" : "HR"
},
{
"id" : "1",
"name" : "Sales"
}
],
}' as col1 from dual
) 
select jt.id, jt.city, jt.state1, jt1.dep_id, jt1.dept_name from 
table1 rt,
JSON_TABLE ( col1 FORMAT JSON, '$[*]'
COLUMNS
id VARCHAR2 PATH '$.id',
NESTED PATH '$.address[*]'
COLUMNS (
city  PATH '$.city',
state1  PATH '$.state'

)                    )jt,
JSON_TABLE ( col1 FORMAT JSON, '$[*]'
COLUMNS
id VARCHAR2 PATH '$.id',
NESTED PATH '$.dep[*]'
COLUMNS (
dep_id  PATH '$.id',
dept_name  PATH '$.name'

)
)jt1
where jt.id=jt1.id;

使用两个JSON_TABLE和CROSS JOIN(或CROSS APPLY(：

select a.*, d.*
from   table1 rt
CROSS APPLY JSON_TABLE(
col1 FORMAT JSON,
'$'
COLUMNS
id VARCHAR2 PATH '$.id',
NESTED PATH '$.address[*]'
COLUMNS (
city  PATH '$.city',
state1  PATH '$.state'
)
) a
CROSS APPLY JSON_TABLE(
col1 FORMAT JSON,
'$.dep[*]'
COLUMNS (
dep_id  PATH '$.id',
dept_name  PATH '$.name'
)
) d

对于样本数据，输出：

ID
示例		示例		示例