【博客文章2025】Oracle数据库中的JSON_VALUE、JSON_QUERY和JSON_EXISTS运算符
Author: Bo Tang
1. JSON_QUERY和JSON_VALUE的区别：
如之前的博客所描述：JSON_VALUE只能返回标量数据。如果将要返回的JSON数据是复杂的矢量值，那么JSON_VALUE就不返回值（实验环境来自https://www.botangdb.com/mytec/mytec_basicadmin/202412/00900130.html）：
SELECT JSON_VALUE (doc, '$.ShippingInstructions') SI
FROM j1 where id=1;
null
|
以上情况，使用JSON_QUERY能够返回JSON数据：
SELECT JSON_QUERY (doc, '$.ShippingInstructions') SI
FROM j1 where id=1;
SI
{"name":"赵先生","Address":{"street":"第1街道","city":"哈尔滨","province":"黑龙江","zipCode":150000,"country":"中国"},"Phone":[{"type":"固定电话","number":"0451-86779817"}]} |
为了更加明确地说明问题，再向hr.j1表插入一行JSON数据：
INSERT INTO j1 VALUES
(4,
'{
"Number":4,
"Reference":"钱先生-20241202",
"Requestor":"钱先生",
"User":"SIRQIAN",
"CostCenter":"A",
"ShippingInstructions":
{
"name":"钱先生",
"Address":
{
"street":"第2街道",
"city":"长春",
"province":"吉林",
"zipCode":130000,
"country":"中国"
},
"Phone":
[
{
"type":"固定电话",
"number":"0431-88777223"
}
]
},
"Special Instructions":"",
"LineItems":
[
{
"ItemNumber":1,
"Part":
{
"Description":"《大教堂》",
"UnitPrice":19.95,
"UPCCode":53396040194
},
"Quantity":7.0
}
]
}'
);
commit;
|
注意：即使"Special Instructions"项没有值，也应该写成"Special Instructions":""。否则IS JSON约束会阻止插入。 现在，表里的数据如下：
SELECT id, JSON_VALUE(doc, '$.Requestor') Requestor, JSON_VALUE (doc, '$."Special Instructions"') SpecInstr
FROM j1 order by id;
| ID |
REQUESTOR |
SPECINSTR |
|---|
| 1 |
赵先生 |
平寄邮件 |
| 2 |
|
|
| 3 |
|
|
| 4 |
钱先生 |
|
|
2. 作非空判断时，要使用JSON_VALUE不能使用JSON_QUERY：
根据表数据，判断“Speical Instruction”为非空的值只有一行，即ID为1的那行，使用JSON_VALUE的查询正确：
SELECT id, JSON_VALUE(doc, '$.Requestor') Requestor, JSON_VALUE (doc, '$."Special Instructions"') SpecInstr
FROM j1 WHERE JSON_VALUE (doc, '$."Special Instructions"') is not null order by id;
| ID |
REQUESTOR |
SPECINSTR |
|---|
| 1 |
赵先生 |
平寄邮件 |
|
使用JSON_QUERY的查询，结果错误：
SELECT id, JSON_VALUE(doc, '$.Requestor') Requestor, JSON_VALUE (doc, '$."Special Instructions"') SpecInstr
FROM j1 WHERE JSON_QUERY (doc, '$."Special Instructions"') is not null order by id;
| ID |
REQUESTOR |
SPECINSTR |
|---|
| 1 |
赵先生 |
平寄邮件 |
| 4 |
钱先生 |
|
|
3. JSON_EXISTS运算符：
ID为2和3的两行没有“Speical Instruction”项，ID为4的行有“Speical Instruction”项，但是没有值。从以下的查询结果来看，JSON_EXISTS只管是否有“Speical Instruction”项，即使该项没有值也把该行包括于结果集内：
SELECT id, JSON_VALUE(doc, '$.Requestor') Requestor, JSON_VALUE (doc, '$."Special Instructions"') SpecInstr
FROM j1 WHERE JSON_EXISTS (doc, '$."Special Instructions"') order by id;
| ID |
REQUESTOR |
SPECINSTR |
|---|
| 1 |
赵先生 |
平寄邮件 |
| 4 |
钱先生 |
|
|
进一步推论：JSON_QUERY的非空判断就是JSON_EXISTS。因此，在JSON键值对中即使值为空，JSON_QUERY非空判断也返回真：
SELECT id, JSON_VALUE(doc, '$.Requestor') Requestor, JSON_VALUE (doc, '$."Special Instructions"') SpecInstr
FROM j1 WHERE JSON_QUERY (doc, '$."Special Instructions"') is not null order by id;
| ID |
REQUESTOR |
SPECINSTR |
|---|
| 1 |
赵先生 |
平寄邮件 |
| 4 |
钱先生 |
|
|
|
