【博客文章2025】使用JSON_TABLE和NESTED PATH运算符构造所需结果集
Author: Bo Tang
1. JSON数据可以有多个层次的数据包裹关系：
如下JSON数据，按照以下顺序： 红色- 黄色- 绿色- 灰色，来依次 展示4层包裹关系（实验环境来自https://www.botangdb.com/mytec/mytec_basicadmin/202412/00900130.html）：
{
"Number":1,
"Reference":"赵先生-20241201",
"Requestor":"赵先生",
"User":"SIRZHAO",
"CostCenter":"A",
"ShippingInstructions":
{
"name":"赵先生",
"Address":
{
"street":"第1街道",
"city":"哈尔滨",
"province":"黑龙江",
"zipCode":150000,
"country":"中国"
},
"Phone":
[
{
"type":"固定电话",
"number":"0451-86779817"
}
]
},
"Special Instructions":"平寄邮件",
"LineItems":
[
{
"ItemNumber":1,
"Part":
{
"Description":"《哲学与社会——老年沉思录》",
"UnitPrice":19.95,
"UPCCode":43396040144
},
"Quantity":7.0
},
{
"ItemNumber":2,
"Part":
{
"Description":"《陈平原文集》",
"UnitPrice":19.95,
"UPCCode":43396040145
},
"Quantity":7.0
},
{
"ItemNumber":3,
"Part":
{
"Description":"《蘑菇猎人：探寻北美野生蘑菇的地下世界》",
"UnitPrice":19.95,
"UPCCode":12236101345
},
"Quantity":1.0
},
{
"ItemNumber":4,
"Part":
{
"Description":"《楼兰新史（增订本）》",
"UnitPrice":19.95,
"UPCCode":85391756323
},
"Quantity":8.0
},
{
"ItemNumber":5,
"Part":
{
"Description":"《人类思想发展史：关于古代近东思辨思想的讨论》",
"UnitPrice":19.95,
"UPCCode":13023009592
},
"Quantity":8.0
},
{
"ItemNumber":6,
"Part":
{
"Description":"《韩家往事》",
"UnitPrice":19.95,
"UPCCode":27616864451
},
"Quantity":8.0
}
]
}
|
在之前的博客中介绍过的JSON_VALUE只能显示JSON第1层数据：
SELECT JSON_VALUE (doc, '$.Number') NUM,
JSON_VALUE (doc, '$.Reference') Ref,
JSON_VALUE (doc, '$.Requestor') Requestor,
JSON_VALUE (doc, '$.User') account_name,
JSON_VALUE (doc, '$.CostCenter') CC,
JSON_VALUE (doc, '$."Special Instructions"') SI
FROM j1 where id=1;
| NUM | REF | REQUESTOR | ACCOUNT_NAME | CC | SI |
|---|
| 1 | 赵先生-20241201 | 赵先生 | SIRZHAO | A | 平寄邮件 |
|
“点层次表示法”（"LineItems"."ItemNumber"）不是JSON_VALUE/JSON_QUERY允许的语法。以下的语句无法显示第2层及更深层的数据包裹关系的数据（如下面的LI和LQ用 “点层次表示法”，均返回空值）：
SELECT JSON_VALUE (doc, '$.Number') NUM,
JSON_VALUE (doc, '$.Reference') Ref,
JSON_VALUE (doc, '$.Requestor') Requestor,
JSON_VALUE (doc, '$.User') account_name,
JSON_VALUE (doc, '$.CostCenter') CC,
JSON_VALUE (doc, '$."Special Instructions"') SI,
JSON_VALUE (doc, '$."LineItems"."ItemNumber"' ) LI,
JSON_QUERY (doc, '$."LineItems"."Part"' ) LQ
FROM j1 where id=1;
| NUM |
REF |
REQUESTOR |
ACCOUNT_NAME |
CC |
SI |
LI |
LQ |
|---|
| 1 |
赵先生-20241201 |
赵先生 |
SIRZHAO |
A |
平寄邮件 |
|
|
|
2. JSON_TABLE运算符在内存中映射JSON数据为表结构：
SQL/JSON 标准允许使用JSON_TABLE和NESTED PATH运算符来提取多层数据。上一层数据（比如：上面例子中红色Number键的值：“订单号1”） 被提取并传送到下一层甚至是下下一层数据（比如：上面例子中的银色的表示“订单号为1”的订单详情），从而组成有商业意义的“明细清单”表结构。 因此，JSON_TABLE是看成解除一个级别数据嵌套的运算符。而NESTED PATH则实现了“嵌套列”钻探。JSON_TABLE一次只能解除一个层次的数据包裹（嵌套），配合NESTED PATH（甚至嵌套的NESTED PATH）逐层向下级层次钻探，以解除更多级别的数据嵌套。 以下的例子利用上面所介绍的知识，来构造结果集： 显示订单号1和该订单的明细：
SELECT d.* FROM j1 j,
JSON_TABLE (
j.doc,'$'
COLUMNS (
unumber NUMBER(10) PATH '$.Number',
NESTED PATH '$.ShippingInstructions[*]' COLUMNS (
name VARCHAR2(14 CHAR) PATH '$.name' ,
NESTED PATH '$.Address[*]' COLUMNS (
street VARCHAR2(14 CHAR) PATH '$.street'
)
),
NESTED PATH '$.LineItems[*]' COLUMNS (
itemno NUMBER(16) PATH '$.ItemNumber',
upccode VARCHAR2(14) PATH '$.Part.UPCCode',
pdescription VARCHAR2(2000) PATH '$.Part.Description',
price VARCHAR2(16) PATH '$.Part.UnitPrice'
)
)
) d
WHERE JSON_VALUE(doc, '$.User')='SIRZHAO';
| UNUMBER | NAME | STREET | ITEMNO | UPCCODE | PDESCRIPTION | PRICE |
|---|
| 1 | 赵先生 | 第1街道 | | | | | | 1 | | | 1 | 43396040144 | 《哲学与社会——老年沉思录》 | 19.95 | | 1 | | | 2 | 43396040145 | 《陈平原文集》 | 19.95 | | 1 | | | 3 | 12236101345 | 《蘑菇猎人：探寻北美野生蘑菇的地下世界》 | 19.95 | | 1 | | | 4 | 85391756323 | 《楼兰新史（增订本）》 | 19.95 | | 1 | | | 5 | 13023009592 | 《人类思想发展史：关于古代近东思辨思想的讨论》 | 19.95 | | 1 | | | 6 | 27616864451 | 《韩家往事》 | 19.95 |
|
上面的COLUMNS子句，构造出第一层包裹关系中的数据列“UNUMBER”，其他列则由NESTED PATH COLUMNS子句构造：
SELECT d.* FROM j1 j,
JSON_TABLE (
j.doc,'$'
COLUMNS (
unumber NUMBER(10) PATH '$.Number',
......
)
) d
WHERE JSON_VALUE(doc, '$.User')='SIRZHAO';
|
NESTED PATH COLUMNS子句还可以嵌套:
SELECT d.* FROM j1 j,
JSON_TABLE (
j.doc,'$'
COLUMNS (
unumber NUMBER(10) PATH '$.Number',
NESTED PATH '$.ShippingInstructions[*]' COLUMNS (
name VARCHAR2(14 CHAR) PATH '$.name' ,
NESTED PATH '$.Address[*]' COLUMNS (
street VARCHAR2(14 CHAR) PATH '$.street'
)
),
......
|
第1个NESTED PATH和COLUMNS之间的 '$.ShippingInstructions[*]'代表了以下这个数组：
"ShippingInstructions":
{
"name":"赵先生",
"Address":
{
"street":"第1街道",
"city":"哈尔滨",
"province":"黑龙江",
"zipCode":150000,
"country":"中国"
},
"Phone":
[
{
"type":"固定电话",
"number":"0451-86779817"
}
]
},
|
第2个NESTED PATH和COLUMNS之间的'$.Address[*]'代表了以下这个小一点的数组：
"Address":
{
"street":"第1街道",
"city":"哈尔滨",
"province":"黑龙江",
"zipCode":150000,
"country":"中国"
},
|
分析到此处，可以看出：JSON_TABLE行源使用NESTED PATH子句展平了多层嵌套数据。
3. “点层次表示法”是JSON_TABLE允许的语法，但是如果有多个同级别的数据嵌套并存时, 必须使用NESTED PATH展平这些多层嵌套数据：
“点层次表示法”是JSON_TABLE允许的语法。如下这样写，结果集与本博客第2节的查询结果完全相同：
SELECT d.* FROM j1 j,
JSON_TABLE (
j.doc,'$'
COLUMNS (
unumber NUMBER(10) PATH '$.Number',
NESTED PATH '$.ShippingInstructions[*]' COLUMNS (
name VARCHAR2(14 CHAR) PATH '$.name' ,
street VARCHAR2(104 CHAR) PATH '$.Address.street'
),
NESTED PATH '$.LineItems[*]' COLUMNS (
itemno NUMBER(16) PATH '$.ItemNumber',
upccode VARCHAR2(14) PATH '$.Part.UPCCode',
pdescription VARCHAR2(2000) PATH '$.Part.Description',
price VARCHAR2(16) PATH '$.Part.UnitPrice'
)
)
) d
WHERE JSON_VALUE(doc, '$.User')='SIRZHAO';
| UNUMBER | NAME | STREET | ITEMNO | UPCCODE | PDESCRIPTION | PRICE |
|---|
| 1 | 赵先生 | 第1街道 | | | | | | 1 | | | 1 | 43396040144 | 《哲学与社会——老年沉思录》 | 19.95 | | 1 | | | 2 | 43396040145 | 《陈平原文集》 | 19.95 | | 1 | | | 3 | 12236101345 | 《蘑菇猎人：探寻北美野生蘑菇的地下世界》 | 19.95 | | 1 | | | 4 | 85391756323 | 《楼兰新史（增订本）》 | 19.95 | | 1 | | | 5 | 13023009592 | 《人类思想发展史：关于古代近东思辨思想的讨论》 | 19.95 | | 1 | | | 6 | 27616864451 | 《韩家往事》 | 19.95 |
|
其中的 “点层次表示法”：
| street VARCHAR2(104 CHAR) PATH '$.Address.street' |
代表以下数据：
但是如果有多个同级别的数据嵌套并存时, 必须使用NESTED PATH展平多层嵌套数据，以下这样使用 “点层次表示法”是错误的（没有形成“明细清单”结果集）：
SELECT d.* FROM j1 j,
JSON_TABLE (
j.doc,'$'
COLUMNS (
unumber NUMBER(10) PATH '$.Number',
name VARCHAR2(14 CHAR) PATH '$.ShippingInstructions.name' ,
street VARCHAR2(104 CHAR) PATH
'$.ShippingInstructions.Address.street',
itemno NUMBER(16) PATH '$.LineItems.ItemNumber',
upccode VARCHAR2(14) PATH '$.LineItems.Part.UPCCode',
pdescription VARCHAR2(2000) PATH '$.LineItems.Part.Description',
price VARCHAR2(16) PATH '$.Part.UnitPrice'
)
) d
WHERE JSON_VALUE(doc, '$.User')='SIRZHAO';
Table created.
| UNUMBER | NAME | STREET | ITEMNO | UPCCODE | PDESCRIPTION | PRICE |
|---|
| 1 | 赵先生 | 第1街道 | | | | |
|
这是因为 '$.LineItems.ItemNumber'是多个同级别的数据嵌套并存结构。比如："ItemNumber":1、"ItemNumber":2、"ItemNumber":3、"ItemNumber":4、"ItemNumber":5和"ItemNumber":6。
|
