JSON_VALUE的作用，简单来说，就是从JSON到SQL：
SQL/JSON function JSON_VALUE selects JSON data and returns a SQL scalar or an instance of a user-defined SQL object type or SQL collection type (varray, nested table)
所以，JSON_VALUE是JSON到SQL间的桥梁。JSON_VALUE只返回一个标量。
JSON_VALUE需2个参数，第一个为JSON文档，第二个为JSON路径。
{
"PONumber" : 1,
"Reference" : "MSULLIVA-20141102",
"Requestor" : "Martha Sullivan",
"User" : "MSULLIVA",
"CostCenter" : "A50",
"ShippingInstructions" :
{
"name" : "Martha Sullivan",
"Address" :
{
"street" : "200 Sporting Green",
"city" : "South San Francisco",
"state" : "CA",
"zipCode" : 99236,
"country" : "United States of America"
},
"Phone" :
[
{
"type" : "Office",
"number" : "979-555-6598"
}
]
},
"Special Instructions" : "Surface Mail",
"LineItems" :
[
{
"ItemNumber" : 1,
"Part" :
{
"Description" : "Run Lola Run",
"UnitPrice" : 19.95,
"UPCCode" : 43396040144
},
"Quantity" : 7
},
{
"ItemNumber" : 2,
"Part" :
{
"Description" : "Felicia's Journey",
"UnitPrice" : 19.95,
"UPCCode" : 12236101345
},
"Quantity" : 1
},
{
"ItemNumber" : 3,
"Part" :
{
"Description" : "Lost and Found",
"UnitPrice" : 19.95,
"UPCCode" : 85391756323
},
"Quantity" : 8
},
{
"ItemNumber" : 4,
"Part" :
{
"Description" : "Karaoke: Rock & Roll Hits of 80's & 90's 8",
"UnitPrice" : 19.95,
"UPCCode" : 13023009592
},
"Quantity" : 8
},
{
"ItemNumber" : 5,
"Part" :
{
"Description" : "Theremin: An Electronic Odyssey",
"UnitPrice" : 19.95,
"UPCCode" : 27616864451
},
"Quantity" : 8
}
]
}
我们使用标准的示例数据。
查看字段的数据类型：
SQL> select j.po_document."User".type() from j_purchaseorder j where j.po_document.PONumber = 1;
J.PO_DOCUMENT."USER".TYPE()
----------------------------------------------------------------------------------------------------
string
SQL> select j.po_document.PONumber.type() from j_purchaseorder j where j.po_document.PONumber = 1;
J.PO_DOCUMENT.PONUMBER.TYPE()
----------------------------------------------------------------------------------------------------
number
从下例可知，JSON文档的字段名是区分大小写的：
SQL> select json_value(po_document, '$.user') from j_purchaseorder where id =1;
JSON_VALUE(PO_DOCUMENT,'$.USER')
---------------------------------------
SQL>
select json_value(po_document, '$.User') from j_purchaseorder where id =1;
-- 或
select json_value(po_document, '$.User') from j_purchaseorder where json_value(po_document, '$.PONumber') = 1;
-- 或
select json_value(po_document, '$.User') from j_purchaseorder j where j.po_document.PONumber = 1;
-- 或
select j.po_document."User" from j_purchaseorder j where j.po_document.PONumber = 1;
JSON_VALUE(PO_DOCUMENT,'$.USER')
--------------------------------------
MSULLIVA
其中的$是JSON路径表达式，语法说明参见17.2 SQL/JSON Path Expression Syntax
返回null的情形：
JSON_VALUE(PO_DOCUMENT,'$.SHIPPINGINSTRUCTIONS.LINEITEMS')
---------------------------------------------------------------------------
这个也返回空，不明白：
select json_value(po_document, '$.LineItems[1].Part.UnitPrice') from j_purchaseorder where id =1;
出错的情形：
SQL> select json_value(po_document, '$.ShippingInstructions.name').type() from j_purchaseorder where id =1;
select json_value(po_document, '$.ShippingInstructions.name').type() from j_purchaseorder where id =1
*
ERROR at line 1:
ORA-22806: not an object or REF
Help: https://docs.oracle.com/error-help/db/ora-22806/
RETURNING 子句
文档说：
Use the RETURNING clause to specify the data type of the return value. If you omit this clause, then JSON_VALUE returns a value of type VARCHAR2(4000).
总之，返回的是SQL Data Type。
例如，以下返回的是VARCHAR2(4000)：
SQL> select json_value(po_document, '$.User') from j_purchaseorder where id =1;
JSON_VALUE(PO_DOCUMENT,'$.USER')
---------------------------------------------
MSULLIVA
结合文档，以下证明其确实为VARCHAR2类型：
SQL> select dump(json_value(po_document, '$.User')) from j_purchaseorder where id =1;
DUMP(JSON_VALUE(PO_DOCUMENT,'$.USER'))
--------------------------------------------------
Typ=1 Len=8: 77,83,85,76,76,73,86,65
指定RETURNING子句，以下显示了准确的数据类型：
create table test as select json_value(po_document, '$.User' RETURNING VARCHAR2(32)) as result from j_purchaseorder j where 1=2;
DESC test;
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
RESULT VARCHAR2(4000)
本例还可以转换为NUMBER类型：
create table test as select json_value(po_document, '$.User' RETURNING NUMBER) as result from j_purchaseorder j where 1=2;
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
RESULT NUMBER
返回BOOLEAN的例子，23ai才支持：
SQL> SELECT json_value(po_document, '$.AllowPartialShipment' RETURNING BOOLEAN) as aps
FROM j_purchaseorder j where j.po_document.AllowPartialShipment is not null;
APS
-----
true
还可以将true/false转换为数字1/0：
SELECT json_value(po_document, '$.AllowPartialShipment'
RETURNING NUMBER
ALLOW BOOLEAN TO NUMBER CONVERSION)
FROM j_purchaseorder j where j.po_document.AllowPartialShipment is not null;
JSON_VALUE(PO_DOCUMENT,'$.ALLOWPARTIALSHIPMENT'RETURNINGNUMBERALLOWBOOLEANTONUMBERCONVERSION)
---------------------------------------------------------------------------------------------
1
返回标量是最常见的场景，还可以返回User-Defined Object-Type or Collection-Type Instance。
参考
- JSON Developer’s Guide - SQL/JSON Function JSON_VALUE
- SQL Language Reference - JSON_VALUE
- The new SQL/JSON Query operators (Part1): JSON_VALUE
