19c新特性
介绍JSON
创建、存储和管理JSON
插入、更新JSON数据
查询JSON数据
生成JSON格式数据
PL/SQL JSON数据类型
通过Oracle Spatial and Graph存储、索引和管理GeoJSON数据
JSON性能调优
Oracle JSON的限制
在19c中也增加了一些新特性，比如开始支持GeoJSON数据，支持基于JSON数据的物化视图。JSON Data Guide，JSON_OBJECT，JSON_MERGEPATCH，JSON_SERIALIZE功能更强，通过SQL NESTED替代JSON_TABLE等。下面的脚本都可以从该链接获取，Oracle官方白皮书JSON Developer's Guide主要分为以下几部分
https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/loe.html创建、存储和管理JSON
CREATE TABLE j_purchaseorder (id VARCHAR2 (32) NOT NULL PRIMARY KEY, date_loaded TIMESTAMP (6) WITH TIME ZONE, po_document VARCHAR2 (23767) CONSTRAINT purchaseorder_json CHECK (po_document IS JSON));IS JSON约束会执行JSON语法检查，如果要强检查，可以使用(po_document IS JSON (STRICT))
除此之外，JSON也可以存在分区表中
CREATE TABLE j_purchaseorder_partitioned
(id VARCHAR2 (32) NOT NULL PRIMARY KEY,
date_loaded TIMESTAMP (6) WITH TIME ZONE,
po_document CLOB,
po_num_vc NUMBER GENERATED ALWAYS AS
(json_value (po_document, '$.PONumber' RETURNING NUMBER)))
LOB (po_document) STORE AS (CACHE)
PARTITION BY RANGE (po_num_vc)
(PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (2000));
还可以创建为外部表CREATE OR REPLACE DIRECTORY order_entry_dir
AS '$ORACLE_HOME/demo/schema/order_entry';
CREATE TABLE json_dump_file_contents (json_document BLOB)
ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY order_entry_dir
ACCESS PARAMETERS
(RECORDS DELIMITED BY 0x'0A'
DISABLE_DIRECTORY_LINK_CHECK
FIELDS (json_document CHAR(5000)))
LOCATION (order_entry_dir:'PurchaseOrders.dmp'))
PARALLEL
REJECT LIMIT UNLIMITED;
插入JSON数据INSERT INTO j_purchaseorder
VALUES (
SYS_GUID(),
to_date('30-DEC-2014'),
'{"PONumber" : 1600,
"Reference" : "ABULL-20140421",
"Requestor" : "Alexis Bull",
"User" : "ABULL",
"CostCenter" : "A50",
"ShippingInstructions" : {"name" : "Alexis Bull",
"Address" : {"street" : "200 Sporting Green",
"city" : "South San Francisco",
"state" : "CA",
"zipCode" : 99236,
"country" : "United States of America"},
"Phone" : [{"type" : "Office", "number" : "909-555-7307"},
{"type" : "Mobile", "number" : "415-555-1234"}]},
"Special Instructions" : null,
"AllowPartialShipment" : true,
"LineItems" : [{"ItemNumber" : 1,
"Part" : {"Description" : "One Magic Christmas",
"UnitPrice" : 19.95,
"UPCCode" : 13131092899},
"Quantity" : 9.0},
{"ItemNumber" : 2,
"Part" : {"Description" : "Lethal Weapon",
"UnitPrice" : 19.95,
"UPCCode" : 85391628927},
"Quantity" : 5.0}]}');
INSERT INTO j_purchaseorder
VALUES (
SYS_GUID(),
to_date('30-DEC-2014'),
'{"PONumber" : 672,
"Reference" : "SBELL-20141017",
"Requestor" : "Sarah Bell",
"User" : "SBELL",
"CostCenter" : "A50",
"ShippingInstructions" : {"name" : "Sarah Bell",
"Address" : {"street" : "200 Sporting Green",
"city" : "South San Francisco",
"state" : "CA",
"zipCode" : 99236,
"country" : "United States of America"},
"Phone" : "983-555-6509"},
"Special Instructions" : "Courier",
"LineItems" : [{"ItemNumber" : 1,
"Part" : {"Description" : "Making the Grade",
"UnitPrice" : 20,
"UPCCode" : 27616867759},
"Quantity" : 8.0},
{"ItemNumber" : 2,
"Part" : {"Description" : "Nixon",
"UnitPrice" : 19.95,
"UPCCode" : 717951002396},
"Quantity" : 5},
{"ItemNumber" : 3,
"Part" : {"Description" : "Eric Clapton: Best Of 1981-1999",
"UnitPrice" : 19.95,
"UPCCode" : 75993851120},
"Quantity" : 5.0}
]}');
我们可以通过视图来确认包含JSON类型的表和列，DBA_JSON_COLUMNS, USER_JSON_COLUMNS, ALL_JSON_COLUMNS更新JSON数据
SQL> desc j_purchaseorder Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL VARCHAR2(32) DATE_LOADED TIMESTAMP(6) WITH TIME ZONE PO_DOCUMENT VARCHAR2(23767)我们也可以更新JSON，如果格式或语法有问题，会失败
SQL> update j_purchaseorder set PO_DOCUMENT ='{"type":"Office"';
update j_purchaseorder set PO_DOCUMENT ='{"type":"Office"'
*
ERROR at line 1:
ORA-02290: check constraint (SYS.PURCHASEORDER_JSON) violated
UPDATE j_purchaseorder SET po_document =
json_mergepatch(po_document, '{"Special Instructions":null}');
查询JSON数据
下面让我们看一下JSON_VALUE，JSON_QUERY，JSON_EXISTS， JSON_TABLE ，JSON_SERIALIZE可以用来查询JSON数据。在18c中添加了JSON_EQUAL函数
SQL> select count(*) from j_purchaseorder;
SQL> select count(*) from j_purchaseorder;
COUNT(*)
----------
2
SQL>SELECT po.po_document.PONumber FROM j_purchaseorder po;
其实po_document.PONumber中间的.后台是通过JSON函数实现的，相当于做了一次查询转换，有兴趣的话，可以通过10053去跟踪一下。SELECT JSON_QUERY("PO"."PO_DOCUMENT" /*+ LOB_BY_VALUE */ FORMAT JSON , '$.PONumber' RETURNING VARCHAR2(4000) ASIS WITHOUT ARRAY WRAPPER NULL ON ERROR) "PONUMBER" FROM "SYS"."J_PURCHASEORDER" "PO";还可以看到错误处理NULL ON ERROR，常用的异常处理有{ ERROR | NULL | DEFAULT literal } ON ERROR
SQL> SELECT po.po_document.PONumber FROM j_purchaseorder po; PONUMBER -------------------------------------------------------------------------------- 1600 672SQL> SELECT json_value(po_document, '$.PONumber') FROM j_purchaseorder;
JSON_VALUE(PO_DOCUMENT,'$.PONUMBER') -------------------------------------------------------------------------------- 1600 672SQL> SELECT po.po_document.ShippingInstructions.Phone FROM j_purchaseorder po;
SHIPPINGINSTRUCTIONS
--------------------------------------------------------------------------------
[{"type":"Office","number":"909-555-7307"},{"type":"Mobile","number":"415-555-12
34"}]
983-555-6509
SQL> SELECT json_query(po_document, '$.ShippingInstructions.Phone') FROM j_purchaseorder; 2
JSON_QUERY(PO_DOCUMENT,'$.SHIPPINGINSTRUCTIONS.PHONE')
--------------------------------------------------------------------------------
[{"type":"Office","number":"909-555-7307"},{"type":"Mobile","number":"415-555-12
34"}]
SQL> SELECT po.po_document.ShippingInstructions.Phone.type FROM j_purchaseorder po;SHIPPINGINSTRUCTIONS -------------------------------------------------------------------------------- ["Office","Mobile"]SQL> SELECT json_query(po_document, '$.ShippingInstructions.Phone.type' WITH WRAPPER)
FROM j_purchaseorder; 2
JSON_QUERY(PO_DOCUMENT,'$.SHIPPINGINSTRUCTIONS.PHONE.TYPE'WITHWRAPPER) -------------------------------------------------------------------------------- ["Office","Mobile"]SELECT po_document FROM j_purchaseorder
WHERE json_exists(po_document, '$.ShippingInstructions.Address.country');
SQL> SELECT jt.*
FROM j_purchaseorder,
json_table(po_document, '$.ShippingInstructions.Phone[*]'
COLUMNS (phone_type VARCHAR2(10) PATH '$.type',
phone_num VARCHAR2(20) PATH '$.number')) AS "JT"; 2 3 4 5
PHONE_TYPE PHONE_NUM ---------- -------------------- Office 909-555-7307 Mobile 415-555-1234 SQL>生成JSON格式数据
通过该函数JSON_OBJECT，JSON_OBJECTAGG，JSON_ARRAY，JSON_ARRAYAGG，JSON_OBJECT，可以生成JSON格式。比如实现行列转换，数据分组等。
SQL/JSON函数也可以处理空值
默认是NULL ON NULL(如果是空，显示为空)，json_object，json_objectagg
还可设置为ABSENT ON NULL(如果为空，则不显示)，json_array，json_arrayagg
RETURNING语句
RETURNING VARCHAR2，默认是VARCHAR2(4000)
RETURNING VARCHAR2(32767 BYTE)，定义返回为返回VARCHAR2(32767)RETURNING VARCHAR2(200 CHAR)，定义返回为
RETURNING CLOB
FORMAT JSON
如果返回的值BLOB，则需要使用FORMAT JSON，其他情况下一般是不需要的
SQL/JSON不接受数字作为KEY
如果使用数字作为key，会碰到下面的错误。可通过to_char来转换
ORA-00932: inconsistent datatypes: expected CHAR got NUMBER
JSON_OBJECT(KEY'PURCHASEID'VALUEID,KEY'PURCHASEDATE'VALUEDATE_LOADED)
--------------------------------------------------------------------------------
{"Purchase ID":"9FC1173B3A0E53E2E053330AA8C0656D","Purchase Date":"2014-12-29T16
:00:00.000000Z"}
{"Purchase ID":"9FC1173B3A0F53E2E053330AA8C0656D","Purchase Date":"2014-12-29T16
:00:00.000000Z"}
SQL> select json_objectagg (key 'Purchase Date' value DATE_LOADED) from j_purchaseorder;JSON_OBJECTAGG(KEY'PURCHASEDATE'VALUEDATE_LOADED)
--------------------------------------------------------------------------------
{"Purchase Date":"2014-12-29T16:00:00.000000Z","Purchase Date":"2014-12-29T16:00
:00.000000Z"}
SQL> select json_array(rownum,json_object(key 'Purchase ID' value ID,key 'Purchase Date' value DATE_LOADED)) from j_purchaseorder;JSON_ARRAY(ROWNUM,JSON_OBJECT(KEY'PURCHASEID'VALUEID,KEY'PURCHASEDATE'VALUEDATE_
--------------------------------------------------------------------------------
[1,{"Purchase ID":"9FC1173B3A0E53E2E053330AA8C0656D","Purchase Date":"2014-12-29
T16:00:00.000000Z"}]
[2,{"Purchase ID":"9FC1173B3A0F53E2E053330AA8C0656D","Purchase Date":"2014-12-29
T16:00:00.000000Z"}]
SQL>
SQL> select json_array(id) from j_purchaseorder;JSON_ARRAY(ID) -------------------------------------------------------------------------------- ["9FC1173B3A0E53E2E053330AA8C0656D"] ["9FC1173B3A0F53E2E053330AA8C0656D"]SQL> select json_arrayagg(id) from j_purchaseorder;
JSON_ARRAYAGG(ID) -------------------------------------------------------------------------------- ["9FC1173B3A0E53E2E053330AA8C0656D","9FC1173B3A0F53E2E053330AA8C0656D"]PL/SQL JSON数据类型
CREATE OR REPLACE FUNCTION add_totals(purchaseOrder IN VARCHAR2) RETURN VARCHAR2 IS
po_obj JSON_OBJECT_T;
li_arr JSON_ARRAY_T;
li_item JSON_ELEMENT_T;
li_obj JSON_OBJECT_T;
unitPrice NUMBER;
quantity NUMBER;
totalPrice NUMBER := 0;
totalQuantity NUMBER := 0;
BEGIN
po_obj := JSON_OBJECT_T.parse(purchaseOrder);
li_arr := po_obj.get_Array('LineItems');
FOR i IN 0 .. li_arr.get_size - 1 LOOP
li_obj := JSON_OBJECT_T(li_arr.get(i));
quantity := li_obj.get_Number('Quantity');
unitPrice := li_obj.get_Object('Part').get_Number('UnitPrice');
totalPrice := totalPrice + (quantity * unitPrice);
totalQuantity := totalQuantity + quantity;
END LOOP;
po_obj.put('totalQuantity', totalQuantity);
po_obj.put('totalPrice', totalPrice);
RETURN po_obj.to_string;
END;
/
UPDATE j_purchaseorder SET (po_document) = add_totals(po_document);
SELECT po_document FROM j_purchaseorder po WHERE po.po_document.PONumb;
存储、索引和管理GeoJSON数据CREATE TABLE j_geo
(id VARCHAR2 (32) NOT NULL,
geo_doc VARCHAR2 (4000) CHECK (geo_doc IS JSON));
INSERT INTO j_geo
VALUES (1,
'{"type" : "FeatureCollection",
"features" : [{"type" : "Feature",
"geometry" : {"type" : "Point",
"coordinates" : [-122.236111, 37.482778]},
"properties" : {"Name" : "Redwood City"}},
{"type" : "Feature",
"geometry" : {"type" : "LineString",
"coordinates" : [[102.0, 0.0],
[103.0, 1.0],
[104.0, 0.0],
[105.0, 1.0]]},
"properties" : {"prop0" : "value0",
"prop1" : 0.0}},
{"type" : "Feature",
"geometry" : {"type" : "Polygon",
"coordinates" : [[[100.0, 0.0],
[101.0, 0.0],
[101.0, 1.0],
[100.0, 1.0],
[100.0, 0.0]]]},
"properties" : {"prop0" : "value0",
"prop1" : {"this" : "that"}}}]}');
SELECT json_value(geo_doc, '$.features[0].geometry'
RETURNING SDO_GEOMETRY
ERROR ON ERROR)
FROM j_geo;
SELECT jt.*
FROM j_geo,
json_table(geo_doc, '$.features[*]'
COLUMNS (sdo_val SDO_GEOMETRY PATH '$.geometry')) jt;
CREATE INDEX geo_first_feature_idx
ON j_geo (json_value(geo_doc, '$.features[0].geometry'
RETURNING SDO_GEOMETRY))
INDEXTYPE IS MDSYS.SPATIAL_INDEX;
SELECT id,
json_value(geo_doc, '$features[0].properties.Name') "Name",
SDO_GEOM.sdo_distance(
json_value(geo_doc, '$features[0].geometry') RETURNING SDO_GEOMETRY,
SDO_GEOMETRY(2001,
4326,
SDO_POINT_TYPE(-122.416667, 37.783333, NULL),
NULL,
NULL),
100, -- Tolerance in meters
'unit=KM') "Distance in kilometers"
FROM j_geo
WHERE sdo_within_distance(
json_value(geo_doc, '$.features[0].geometry' RETURNING SDO_GEOMETRY),
SDO_GEOMETRY(2001,
4326,
SDO_POINT_TYPE(-122.416667, 37.783333, NULL),
NULL,
NULL),
'distance=100 unit=KM')
= 'TRUE';
在JSON优化方面我们可以使用InMemory，可以创建索引，还可以基于JSON数据创建物化视图
在索引方面
ALTER TABLE j_purchaseorder ADD (userid VARCHAR2(20)
GENERATED ALWAYS AS (json_value(po_document, '$.User' RETURNING VARCHAR2(20))));
ALTER TABLE j_purchaseorder ADD (costcenter VARCHAR2(6)
GENERATED ALWAYS AS (json_value(po_document, '$.CostCenter'
RETURNING VARCHAR2(6))));
CREATE INDEX user_cost_ctr_idx on j_purchaseorder(userid, costcenter);
SELECT po_document FROM j_purchaseorder WHERE userid = 'ABULL'
AND costcenter = 'A50';
SELECT po_document
FROM j_purchaseorder WHERE json_value(po_document, '$.User') = 'ABULL'
AND json_value(po_document, '$.CostCenter') = 'A50';
CREATE SEARCH INDEX po_search_idx ON j_purchaseorder (po_document) FOR JSON;
在InMmemory方面
数据库初始化参数compatibility必须是12.2.0.0，甚至更高
数据库初始化参数max_string_size 必须设置为extended
SGA要有足够的容量来配置InMemory（inmemory_size）
DBA需要对包含JSON列的表空间、表或者物化视图通过CREATE或ALERT方式指定INMEMORY
存储JSON数据的列，必须有is json的约束
其他初始化参数要设置为如下
IMMEMORY_EXPRESSIONS_USAGE=ENABLE
IMMEMORY_VIRTUAL_COLUMNS =ENABLE
注意：如果compatibility或者max_string_size不满足以上情况，必须运行下面的脚本（rdbms/admin/utlimcjson.sql）已提上JSON性能。
将JSON列标记为INMEMORY之后，将为每个JSON列添加一个内存中的虚拟列。对应的虚拟列用于查询给定的JSON列。虚拟列包含与对应JSON列相同的JSON数据，但采用的是Oracle二进制格式OSON。
IM列存储用于查询小于32,767字节，会从IM列存储中受益。如果大于32767字节，则不会。可以考虑将该列拆分
SELECT COUNT(1) FROM j_purchaseorder
WHERE json_exists(po_document,
'$.ShippingInstructions?(@.Address.zipCode == 99236)');
--执行计划显示: TABLE ACCESS FULL
ALTER TABLE j_purchaseorder INMEMORY;
SELECT COUNT(1) FROM j_purchaseorder
WHERE json_exists(po_document,
'$.ShippingInstructions?(@.Address.zipCode == 99236)');
--执行计划显示: TABLE ACCESS INMEMORY FULL
Oracle JSON的限制https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/oracle-json-restrictions.html
JSON各版本新特性
JSON 19c
https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/changes.htmlJSON 18c
https://docs.oracle.com/en/database/oracle/oracle-database/18/adjsn/changes.html
JSON 12.2
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/changes.html
JSON 12.1
https://docs.oracle.com/database/121/ADXDB/json.htm
JSON Hands-On手册
https://github.com/oracle/json-in-db，里面有很多脚本和样例
JSON健康检查
JSON DB and SODA DB Health-Check Script (Doc ID 2212664.1)
https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/oracle-json-restrictions.html#GUID-1DB81125-54A7-4CB6-864B-78E0E7E407C9
https://jsonformatter.curiousconcept.com/
https://www.json.org/json-en.html
JSON DB and SODA DB Health-Check Script (Doc ID 2212664.1)
