是最基础和较全面的Oracle数据库中JSON学习教程。
SQL/JSON Features in Database 12.2
12.2版本JSON的增强主要有5方面，本教程不包括和In-Memory的集成。
所有的教程来自于Oracle LiveSQL Code Library
Oracle中没有专门的JSON数据类型，JSON可存放在VARCHAR2，BLOB和CLOB中。
IS JSON约束可判断是否为有效JSON：
create table J_PURCHASEORDER (
ID RAW(16) NOT NULL,
DATE_LOADED TIMESTAMP(6) WITH TIME ZONE,
PO_DOCUMENT CLOB CHECK (PO_DOCUMENT IS JSON)
)
JSON数据的导入可以使用标准的API，本例使用的是外部表方式，即直接从外部JSON文件导入。
insert into J_PURCHASEORDER
select SYS_GUID(), SYSTIMESTAMP, PO_DOCUMENT
from JSON_DUMP_CONTENTS
where PO_DOCUMENT IS JSON
and rownum < 1001
/
commit
/
访问JSON数据可使用点分割(Dot-Notation)方式，点分割方式只适用于带有IS JSON约束的列，而且必须为表加别名。
select j.PO_DOCUMENT.CostCenter, count(*)
from J_PURCHASEORDER j
group by j.PO_DOCUMENT.CostCenter
order by j.PO_DOCUMENT.CostCenter
先来看一行示例数据：
select po_document from J_PURCHASEORDER fetch first 1 rows only;
{
"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
}
]
}
JSON_VALUE和JSON_QUERY
另一种更强大和更复杂的方式是函数JSON_VALUE和JSON_QUERY。
JSON_VALUE
JSON_VALUE只支持scalar value，即只返回一行一列，通常用在select语句或where条件中。
JSON_VALUE接受两个参数，即JSON文档(document)和到指定属性的路径(path)，返回值可以格式化。
select JSON_VALUE(PO_DOCUMENT ,'$.LineItems[0].Part.UPCCode')
from J_PURCHASEORDER p
where JSON_VALUE(PO_DOCUMENT ,'$.PONumber' returning NUMBER(10)) = 450
JSON_VALUE支持错误处理，例如当指定的path不正确（如路径不存在，大小写不匹配），返回多个值（非scalar）时。错误处理方式有3种，默认为返回空值（NULL ON ERROR），其它为返回指定默认值（DEFAULT on ERROR），报错（ERROR ON ERROR）。
-- 错误示例1：Address返回多个属性，不是scalar. Address改为Address.city就正确了。
select JSON_VALUE(PO_DOCUMENT ,'$.ShippingInstructions.Address')
from J_PURCHASEORDER
where JSON_VALUE(PO_DOCUMENT ,'$.PONumber' returning NUMBER(10))
-- 错误示例2：大小写错误，Name应为name
select JSON_VALUE(PO_DOCUMENT ,'$.ShippingInstructions.Name')
from J_PURCHASEORDER
where JSON_VALUE(PO_DOCUMENT ,'$.PONumber' returning NUMBER(10)) = 450
DEFAULT on ERROR示例：
select JSON_VALUE(PO_DOCUMENT, '$.ShippingInstruction.Address' DEFAULT 'N/A' ON ERROR)
from J_PURCHASEORDER
where JSON_VALUE(PO_DOCUMENT ,'$.PONumber' returning NUMBER(10)) = 450
Result Set:
JSON_VALUE(PO_DOCUMENT,'$.SHIPPINGINSTRUCTION.ADDRESS'DEFAULT'N/A'ONERROR)
N/A
ERROR ON ERROR示例：
select JSON_VALUE(PO_DOCUMENT, '$.ShippingInstructions.Address' ERROR ON ERROR)
from J_PURCHASEORDER
where JSON_VALUE(PO_DOCUMENT ,'$.PONumber' returning NUMBER(10)) = 450
ORA-40456: JSON_VALUE evaluated to non-scalar value
以上所的错误处理只针对运行时错误，例如以下Address前的.写成了,号，则不在以上所说错误处理的范畴：
select JSON_VALUE(PO_DOCUMENT, '$.ShippingInstructions,Address' NULL ON ERROR)
from J_PURCHASEORDER
where JSON_VALUE(PO_DOCUMENT ,'$.PONumber' returning NUMBER(10)) = 450
ORA-40597: JSON path expression syntax error ('$.ShippingInstructions,Address')
JZN-00209: Unexpected characters after end of path
at position 23
JSON_QUERY
JSON_QUERY是JSON_VALUE的补充，参数个数与类型与其一样，但可返回一个对象或array。
-- 返回一个JSON对象，所以是{}
select JSON_QUERY(PO_DOCUMENT ,'$.ShippingInstructions.Address')
from J_PURCHASEORDER p
where JSON_VALUE(PO_DOCUMENT ,'$.PONumber' returning NUMBER(10)) = 450
{
"street" : "200 Sporting Green",
"city" : "South San Francisco",
"state" : "CA",
"zipCode" : 99236,
"country" : "United States of America"
}
-- 返回一个JSON array，所以是[]
select JSON_QUERY(PO_DOCUMENT ,'$.LineItems')
from J_PURCHASEORDER p
where JSON_VALUE(PO_DOCUMENT ,'$.PONumber' returning NUMBER(10)) = 450
[
{
"ItemNumber" : 1,
"Part" : {
"Description" : "The Lost Empire",
"UnitPrice" : 27.95,
"UPCCode" : 707729118329
},
"Quantity" : 3
},
{
"ItemNumber" : 2,
"Part" : {
"Description" : "The Bat",
"UnitPrice" : 19.95,
"UPCCode" : 13131119695
},
"Quantity" : 3
},
{
"ItemNumber" : 3,
"Part" : {
"Description" : "Body Count",
"UnitPrice" : 19.95,
"UPCCode" : 12236102014
},
"Quantity" : 2
},
{
"ItemNumber" : 4,
"Part" : {
"Description" : "Jurassic Park / Lost World",
"UnitPrice" : 27.95,
"UPCCode" : 25192111327
},
"Quantity" : 7
},
{
"ItemNumber" : 5,
"Part" : {
"Description" : "Family Plot",
"UnitPrice" : 19.95,
"UPCCode" : 25192065927
},
"Quantity" : 4
}
]
为性能计，输出中省去了很多空格，但为了美观，你也可以加PRETTY关键字以添加缩进和对齐：
select JSON_QUERY(PO_DOCUMENT ,'$.LineItems' PRETTY)
from J_PURCHASEORDER p
where JSON_VALUE(PO_DOCUMENT ,'$.PONumber' returning NUMBER(10)) = 450
下例为使用array index以返回array中的一个对象：
select JSON_QUERY(PO_DOCUMENT ,'$.LineItems[0]' PRETTY)
from J_PURCHASEORDER p
where JSON_VALUE(PO_DOCUMENT ,'$.PONumber' returning NUMBER(10)) = 450
{
"ItemNumber" : 1,
"Part" : {
"Description" : "The Lost Empire",
"UnitPrice" : 27.95,
"UPCCode" : 707729118329
},
"Quantity" : 3
}
select JSON_QUERY(PO_DOCUMENT, '$.LineItems[0].Part')
from J_PURCHASEORDER p
where JSON_VALUE(PO_DOCUMENT ,'$.PONumber' returning NUMBER(10)) = 450
{
"Description" : "The Lost Empire",
"UnitPrice" : 27.95,
"UPCCode" : 707729118329
}
注意JSON_QUERY只能返回对象，如果path指定是一个scalar值，则默认返回空值，例如：
select JSON_QUERY(PO_DOCUMENT, '$.LineItems[0].Quantity')
from J_PURCHASEORDER p
where JSON_VALUE(PO_DOCUMENT ,'$.PONumber' returning NUMBER(10)) = 450
JSON_QUERY的错误处理有三种，默认为NULL ON ERROR, ERROR ON ERROR与JSON_VALUE类似，EMPTY ON ERROR返回空的array。
最常见的错误是返回值不是object或array，而是scalar值。
不过还有一种特殊方式，可将scalar转换为array：
select JSON_QUERY(PO_DOCUMENT, '$.LineItems[0].Part.UPCCode' WITH CONDITIONAL ARRAY WRAPPER)
from J_PURCHASEORDER p
where JSON_VALUE(PO_DOCUMENT ,'$.PONumber' returning NUMBER(10)) = 450、
[707729118329]
利用WITH ARRAY WRAPPER将结果强制转换为array:
select JSON_QUERY(PO_DOCUMENT, '$.LineItems[*].Part.*' WITH ARRAY WRAPPER)
from J_PURCHASEORDER p
where JSON_VALUE(PO_DOCUMENT ,'$.PONumber' returning NUMBER(10)) = 450
["The Lost Empire",27.95,707729118329,"The Bat",19.95,13131119695,"Body Count",19.95,12236102014,"Jurassic Park / Lost World",27.95,25192111327,"Family Plot",19.95,25192065927]
JSON_TABLE
JSON_TABLE用在where语句之后，输入为JSON对象或array，输出为inline的关系型表。
例如，其中的$表示所有：
select M.*
from J_PURCHASEORDER p,
JSON_TABLE(
p.PO_DOCUMENT ,
'$'
columns
PO_NUMBER NUMBER(10) path '$.PONumber',
REFERENCE VARCHAR2(30 CHAR) path '$.Reference',
REQUESTOR VARCHAR2(32 CHAR) path '$.Requestor',
USERID VARCHAR2(10 CHAR) path '$.User',
COSTCENTER VARCHAR2(16 CHAR) path '$.CostCenter',
TELEPHONE VARCHAR2(16 CHAR) path '$.ShippingInstructions.Phone[0].number'
) M
where PO_NUMBER between 450 and 455
如果输出的列仍是对象或array，则可使用嵌套（NESTED PATH）：
select D.*
from J_PURCHASEORDER p,
JSON_TABLE(
p.PO_DOCUMENT ,
'$'
columns(
PO_NUMBER NUMBER(10) path '$.PONumber',
REFERENCE VARCHAR2(30 CHAR) path '$.Reference',
REQUESTOR VARCHAR2(32 CHAR) path '$.Requestor',
USERID VARCHAR2(10 CHAR) path '$.User',
COSTCENTER VARCHAR2(16) path '$.CostCenter',
NESTED PATH '$.LineItems[*]'
columns(
ITEMNO NUMBER(16) path '$.ItemNumber',
DESCRIPTION VARCHAR2(32 CHAR) path '$.Part.Description',
UPCCODE VARCHAR2(14 CHAR) path '$.Part.UPCCode',
QUANTITY NUMBER(5,4) path '$.Quantity',
UNITPRICE NUMBER(5,2) path '$.Part.UnitPrice'
)
)
) D
where PO_NUMBER between 450 and 455
PO_NUMBER REFERENCE REQUESTOR USERID COSTCENTER ITEMNO DESCRIPTION UPCCODE QUANTITY UNITPRICE
450 TJOLSON-20140909 TJ Olson TJOLSON A50 1 The Lost Empire 707729118329 3 27.95
450 TJOLSON-20140909 TJ Olson TJOLSON A50 2 The Bat 13131119695 3 19.95
450 TJOLSON-20140909 TJ Olson TJOLSON A50 3 Body Count 12236102014 2 19.95
450 TJOLSON-20140909 TJ Olson TJOLSON A50 4 Jurassic Park / Lost World 25192111327 7 27.95
450 TJOLSON-20140909 TJ Olson TJOLSON A50 5 Family Plot 25192065927 4 19.95
451 TJOLSON-20140919 TJ Olson TJOLSON A50 1 Indigo Girls: Live at the Fillmore Denver, November 20, 1999 74645022598 6 19.95
451 TJOLSON-20140919 TJ Olson TJOLSON A50 2 Wonders of the Deep: Australia & Queensland 56775017897 6 19.95
451 TJOLSON-20140919 TJ Olson TJOLSON A50 3 Let It Ride 97363220046 9 19.95
451 TJOLSON-20140919 TJ Olson TJOLSON A50 4 Bonanza 56775013790 6 19.95
452 TJOLSON-20140923 TJ Olson TJOLSON A50 1 Mach 2 97368603042 5 19.95
452 TJOLSON-20140923 TJ Olson TJOLSON A50 2 Romancing the Stone 86162104015 8 19.95
452 TJOLSON-20140923 TJ Olson TJOLSON A50 3 Penny Serenade 56775005191 8 19.95
452 TJOLSON-20140923 TJ Olson TJOLSON A50 4 Ricky Martin: Video Collection 74645020594 3 19.95
453 SSEWALL-20141031 Sarath Sewall SSEWALL A80 1 The Curve 31398709237 5 19.95
453 SSEWALL-20141031 Sarath Sewall SSEWALL A80 2 Agent Red 43396064829 9 19.95
453 SSEWALL-20141031 Sarath Sewall SSEWALL A80 3 Hanson: At The Fillmore 44005329896 7 19.95
453 SSEWALL-20141031 Sarath Sewall SSEWALL A80 4 Hook & Little Women 43396056879 2 27.95
453 SSEWALL-20141031 Sarath Sewall SSEWALL A80 5 Wet Shorts: Best of Liquid Television Vol.1&2 74644943597 1 19.95
454 TRAJS-20141015 Trenna Rajs TRAJS A50 1 Exotica 717951003249 7 19.95
454 TRAJS-20141015 Trenna Rajs TRAJS A50 2 Dragon Ball Z: Mysterious Youth 704400030024 3 27.95
454 TRAJS-20141015 Trenna Rajs TRAJS A50 3 Air Rage 97368787841 1 19.95
454 TRAJS-20141015 Trenna Rajs TRAJS A50 4 The Brothers McMullen 24543005681 4 19.95
454 TRAJS-20141015 Trenna Rajs TRAJS A50 5 South Pacific 786936159479 7 27.95
455 CJOHNSON-20140918 Charles Johnson CJOHNSON A80 1 The City of Lost Children 43396400191 2 19.95
455 CJOHNSON-20140918 Charles Johnson CJOHNSON A80 2 Annie 717951006707 8 19.95
455 CJOHNSON-20140918 Charles Johnson CJOHNSON A80 3 Shanghai Noon 717951010605 4 27.95
455 CJOHNSON-20140918 Charles Johnson CJOHNSON A80 4 One Tough Cop 43396033344 9 19.95
455 CJOHNSON-20140918 Charles Johnson CJOHNSON A80 5 Poirot: Dumb Witness 54961470396 3 29.95
JSON_TABLE常用于建立关系型视图，然后可以用标准的SQL语句操作。
-- master view
create or replace view PURCHASEORDER_MASTER_VIEW
as
select m.*
from J_PURCHASEORDER p,
JSON_TABLE(
p.PO_DOCUMENT ,
'$'
columns (
PO_NUMBER NUMBER(10) path '$.PONumber',
REFERENCE VARCHAR2(30 CHAR) path '$.Reference',
REQUESTOR VARCHAR2(128 CHAR) path '$.Requestor',
USERID VARCHAR2(10 CHAR) path '$.User',
COSTCENTER VARCHAR2(16) path '$.CostCenter',
SHIP_TO_NAME VARCHAR2(20 CHAR) path '$.ShippingInstructions.name',
SHIP_TO_STREET VARCHAR2(32 CHAR) path '$.ShippingInstructions.Address.street',
SHIP_TO_CITY VARCHAR2(32 CHAR) path '$.ShippingInstructions.Address.city',
SHIP_TO_COUNTY VARCHAR2(32 CHAR) path '$.ShippingInstructions.Address.county',
SHIP_TO_POSTCODE VARCHAR2(32 CHAR) path '$.ShippingInstructions.Address.postcode',
SHIP_TO_STATE VARCHAR2(2 CHAR) path '$.ShippingInstructions.Address.state',
SHIP_TO_PROVINCE VARCHAR2(2 CHAR) path '$.ShippingInstructions.Address.province',
SHIP_TO_ZIP VARCHAR2(8 CHAR) path '$.ShippingInstructions.Address.zipCode',
SHIP_TO_COUNTRY VARCHAR2(32 CHAR) path '$.ShippingInstructions.Address.country',
SHIP_TO_PHONE VARCHAR2(24 CHAR) path '$.ShippingInstructions.Phones[0].number',
INSTRUCTIONS VARCHAR2(2048 CHAR) path '$.SpecialInstructions'
)
) m
-- detail view
create or replace view PURCHASEORDER_DETAIL_VIEW
as
select D.*
from J_PURCHASEORDER p,
JSON_TABLE(
p.PO_DOCUMENT ,
'$'
columns (
PO_NUMBER NUMBER(10) path '$.PONumber',
REFERENCE VARCHAR2(30 CHAR) path '$.Reference',
REQUESTOR VARCHAR2(128 CHAR) path '$.Requestor',
USERID VARCHAR2(10 CHAR) path '$.User',
COSTCENTER VARCHAR2(16) path '$.CostCenter',
SHIP_TO_NAME VARCHAR2(20 CHAR) path '$.ShippingInstructions.name',
SHIP_TO_STREET VARCHAR2(32 CHAR) path '$.ShippingInstructions.Address.street',
SHIP_TO_CITY VARCHAR2(32 CHAR) path '$.ShippingInstructions.Address.city',
SHIP_TO_COUNTY VARCHAR2(32 CHAR) path '$.ShippingInstructions.Address.county',
SHIP_TO_POSTCODE VARCHAR2(10 CHAR) path '$.ShippingInstructions.Address.postcode',
SHIP_TO_STATE VARCHAR2(2 CHAR) path '$.ShippingInstructions.Address.state',
SHIP_TO_PROVINCE VARCHAR2(2 CHAR) path '$.ShippingInstructions.Address.province',
SHIP_TO_ZIP VARCHAR2(8 CHAR) path '$.ShippingInstructions.Address.zipCode',
SHIP_TO_COUNTRY VARCHAR2(32 CHAR) path '$.ShippingInstructions.Address.country',
SHIP_TO_PHONE VARCHAR2(24 CHAR) path '$.ShippingInstructions.Phones[0].number',
INSTRUCTIONS VARCHAR2(2048 CHAR) path '$.SpecialInstructions',
NESTED PATH '$.LineItems[*]'
columns (
ITEMNO NUMBER(38) path '$.ItemNumber',
DESCRIPTION VARCHAR2(256 CHAR) path '$.Part.Description',
UPCCODE VARCHAR2(14 CHAR) path '$.Part.UPCCode',
QUANTITY NUMBER(12,4) path '$.Quantity',
UNITPRICE NUMBER(14,2) path '$.Part.UnitPrice'
)
)
) D
/
-- Transparent Relational Query
select SHIP_TO_STREET, SHIP_TO_CITY, SHIP_TO_STATE, SHIP_TO_ZIP
from PURCHASEORDER_MASTER_VIEW
where PO_NUMBER = 450;
select PO_NUMBER, REFERENCE, SHIP_TO_PHONE, DESCRIPTION, QUANTITY, UNITPRICE
from PURCHASEORDER_DETAIL_VIEW
where UPCCODE = '27616854773';
定义完这些视图后，开发者就可以完全利用SQL的能力了。
JSON_EXISTS
用在where语句中，和EXISITS类似，测试JSON document中是否存在指定的path。
select count(*)
from J_PURCHASEORDER
where JSON_EXISTS(PO_DOCUMENT ,'$.ShippingInstructions.Address.state')
JSON_EXISTS可以区分key不存在或key存在，但value不存在或为空的情形，试比较以下输出：
select JSON_VALUE(PO_DOCUMENT ,'$.ShippingInstructions.Address.county'),
count(*)
from J_PURCHASEORDER
group by JSON_VALUE(PO_DOCUMENT ,'$.ShippingInstructions.Address.county')
select JSON_VALUE(PO_DOCUMENT ,'$.ShippingInstructions.Address.county'),
count(*)
from J_PURCHASEORDER
where JSON_EXISTS(PO_DOCUMENT ,'$.ShippingInstructions.Address.county')
group by JSON_VALUE(PO_DOCUMENT ,'$.ShippingInstructions.Address.county')
JSON_EXISTS还支持predicate，就是可以带条件。
select j.PO_DOCUMENT
from J_PURCHASEORDER j
where JSON_EXISTS(
PO_DOCUMENT,
'$?(@.PONumber == $PO_NUMBER)'
passing 450 as "PO_NUMBER"
)
select count(*)
from J_PURCHASEORDER j
where JSON_EXISTS(
PO_DOCUMENT,
'$?(@.User == $USER && @.LineItems.Quantity > $QUANTITY)'
passing 'AKHOO' as "USER", 8 as "QUANTITY"
)
JSON索引
使用JSON_VALUE创建的索引，基于的值必须是scalar，而且必须唯一。可以是B-Tree索引或Bitmap索引。
create unique index PO_NUMBER_IDX
on J_PURCHASEORDER (
JSON_VALUE(
PO_DOCUMENT,'$.PONumber' returning NUMBER(10) ERROR ON ERROR NULL ON EMPTY
)
)
JSON 搜索索引
A JSON Search Index allows a single index to optimize JSON Path operations on any key.
create search Index JSON_SEARCH_INDEX
on J_PURCHASEORDER (PO_DOCUMENT) for json
JSON DataGuide
JSON DataGuide可以发现JSON Document的结构和内容，以便生成JSON Schema，关系型视图等。DataGuide基于JSON search index同样的结构，因此语法也类似。
以下创建DataGuide，但只开启DataGuide，而禁用了search index。
drop index JSON_SEARCH_INDEX
/
create search index JSON_SEARCH_INDEX
on J_PURCHASEORDER (PO_DOCUMENT) for json
parameters('search_on none dataguide on')
接下来就可以用DBMS_JSON PL/SQL包来探索结构了。
select DBMS_JSON.GET_INDEX_DATAGUIDE(
SYS_CONTEXT('USERENV','CURRENT_USER'),
'J_PURCHASEORDER',
'PO_DOCUMENT',
DBMS_JSON.FORMAT_HIERARCHICAL,
DBMS_JSON.PRETTY
) "HIERARCHICAL DATA GUIDE"
from dual
{
"type" : "object",
"properties" : {
"User" : {
"type" : "string",
"o:length" : 8,
"o:preferred_column_name" : "PO_DOCUMENT$User"
},
"PONumber" : {
"type" : "number",
"o:length" : 4,
"o:preferred_column_name" : "PO_DOCUMENT$PONumber"
},
"LineItems" : {
"type" : "array",
"o:length" : 1024,
"o:preferred_column_name" : "PO_DOCUMENT$LineItems",
"items" : {
"properties" : {
"Part" : {
"type" : "object",
"o:length" : 256,
"o:preferred_column_name" : "PO_DOCUMENT$Part",
"properties" : {
"UPCCode" : {
"type" : "number",
"o:length" : 16,
"o:preferred_column_name" : "PO_DOCUMENT$UPCCode"
},
"UnitPrice" : {
"type" : "number",
"o:length" : 8,
"o:preferred_column_name" : "PO_DOCUMENT$UnitPrice"
},
"Description" : {
"type" : "string",
"o:length" : 128,
"o:preferred_column_name" : "PO_DOCUMENT$Description"
}
}
},
"Quantity" : {
"type" : "number",
"o:length" : 4,
"o:preferred_column_name" : "PO_DOCUMENT$Quantity"
},
"ItemNumber" : {
"type" : "number",
"o:length" : 1,
"o:preferred_column_name" : "PO_DOCUMENT$ItemNumber"
}
}
}
},
"Reference" : {
"type" : "string",
"o:length" : 32,
"o:preferred_column_name" : "PO_DOCUMENT$Reference"
},
"Requestor" : {
"type" : "string",
"o:length" : 32,
"o:preferred_column_name" : "PO_DOCUMENT$Requestor"
},
"CostCenter" : {
"type" : "string",
"o:length" : 4,
"o:preferred_column_name" : "PO_DOCUMENT$CostCenter"
},
"ShippingInstructions" : {
"type" : "object",
"o:length" : 256,
"o:preferred_column_name" : "PO_DOCUMENT$ShippingInstructions",
"properties" : {
"name" : {
"type" : "string",
"o:length" : 32,
"o:preferred_column_name" : "PO_DOCUMENT$name"
},
"Phone" : {
"type" : "array",
"o:length" : 64,
"o:preferred_column_name" : "PO_DOCUMENT$Phone",
"items" : {
"properties" : {
"type" : {
"type" : "string",
"o:length" : 8,
"o:preferred_column_name" : "PO_DOCUMENT$type"
},
"number" : {
"type" : "string",
"o:length" : 16,
"o:preferred_column_name" : "PO_DOCUMENT$number"
}
}
}
},
"Address" : {
"oneOf" : [
{
"type" : "string",
"o:length" : 1,
"o:preferred_column_name" : "PO_DOCUMENT$Address"
},
{
"type" : "object",
"o:length" : 256,
"o:preferred_column_name" : "PO_DOCUMENT$Address_1",
"properties" : {
"city" : {
"type" : "string",
"o:length" : 32,
"o:preferred_column_name" : "PO_DOCUMENT$city"
},
"state" : {
"type" : "string",
"o:length" : 2,
"o:preferred_column_name" : "PO_DOCUMENT$state"
},
"county" : {
"type" : "string",
"o:length" : 8,
"o:preferred_column_name" : "PO_DOCUMENT$county"
},
"street" : {
"type" : "string",
"o:length" : 64,
"o:preferred_column_name" : "PO_DOCUMENT$street"
},
"country" : {
"type" : "string",
"o:length" : 32,
"o:preferred_column_name" : "PO_DOCUMENT$country"
},
"zipCode" : {
"type" : "number",
"o:length" : 8,
"o:preferred_column_name" : "PO_DOCUMENT$zipCode"
},
"postcode" : {
"type" : "string",
"o:length" : 8,
"o:preferred_column_name" : "PO_DOCUMENT$postcode"
},
"province" : {
"type" : "string",
"o:length" : 2,
"o:preferred_column_name" : "PO_DOCUMENT$province"
}
}
}
]
}
}
},
"Special Instructions" : {
"type" : "string",
"o:length" : 32,
"o:preferred_column_name" : "PO_DOCUMENT$SpecialInstructions"
}
}
}
利用DataGuide创建关系型视图：
begin
DBMS_JSON.CREATE_VIEW_ON_PATH(
'J_PURCHASEORDER_VIEW',
'J_PURCHASEORDER',
'PO_DOCUMENT',
'$'
);
end;
select * from J_PURCHASEORDER_VIEW;
生成JSON
从SQL结果集到JSON。相关函数为JSON_ARRAY，JSON_OBJECT，JSON_ARRAYAGG ，JSON_OBJECTAGG。
JSON_ARRAY从SQL生成JSON array，每一列为array的成员：
select JSON_ARRAY(
DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID
) DEPARTMENT
from HR.DEPARTMENTS
where DEPARTMENT_ID < 110;
DEPARTMENT
[10,"Administration",200,1700]
[20,"Marketing",201,1800]
[30,"Purchasing",114,1700]
[40,"Human Resources",203,2400]
[50,"Shipping",121,1500]
[60,"IT",103,1400]
[70,"Public Relations",204,2700]
[80,"Sales",145,2500]
[90,"Executive",100,1700]
[100,"Finance",108,1700]
JSON_OBJECT将每一行转换为JSON 对象。
SELECT JSON_OBJECT(
'departmentId' IS d.DEPARTMENT_ID,
'name' IS d.DEPARTMENT_NAME,
'manager' IS d.MANAGER_ID,
'location' IS d.LOCATION_ID
) DEPARTMENT
from HR.DEPARTMENTS d
where DEPARTMENT_ID < 110;
DEPARTMENT
{
"departmentId" : 10,
"name" : "Administration",
"manager" : 200,
"location" : 1700
}
{
"departmentId" : 20,
"name" : "Marketing",
"manager" : 201,
"location" : 1800
}
{
"departmentId" : 30,
"name" : "Purchasing",
"manager" : 114,
"location" : 1700
}
{
"departmentId" : 40,
"name" : "Human Resources",
"manager" : 203,
"location" : 2400
}
{
"departmentId" : 50,
"name" : "Shipping",
"manager" : 121,
"location" : 1500
}
{
"departmentId" : 60,
"name" : "IT",
"manager" : 103,
"location" : 1400
}
{
"departmentId" : 70,
"name" : "Public Relations",
"manager" : 204,
"location" : 2700
}
{
"departmentId" : 80,
"name" : "Sales",
"manager" : 145,
"location" : 2500
}
{
"departmentId" : 90,
"name" : "Executive",
"manager" : 100,
"location" : 1700
}
{
"departmentId" : 100,
"name" : "Finance",
"manager" : 108,
"location" : 1700
}
PL/SQL 集成（API）
可以浏览PL/SQL结构，增删改JSON的键值对。
PL/SQL JSON API 包括3个 PL/SQL 对象： JSON_ELEMENT_T, JSON_OBJECT_T 和 JSON_ARRAY_T. 后两者是JSON_ELEMENT_T的扩展。
JSON_ELEMENT_T 提供多个method，最重要的是PARSE 和STRINGIFY。PARSE将JSON文本转换为JSON_ELEMENT_T实例。STRINGIFY则为逆操作。
Spatial 集成与GeoJSON支持
GeoJSON是用JSON表示地理信息的标准。下略。
3407
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
