Focus On Oracle

Installing, Backup & Recovery, Performance Tuning,
Troubleshooting, Upgrading, Patching

Oracle Engineered System


当前位置: 首页 » 技术文章 » Oracle 12c+

在Oracle数据库中使用JSON

从12.1开始,Oracle数据库支持JSON,没有新增该数据类型。我们可以将JSON内容存放在VARCHAR2或CLOB数据列中,当然也可以存放在NVARCHAR、NCLOB和BLOB列,但不太建议您这样做。然后为该列添加IS JSON约束,表示该列是有效的JSON数据。本文内容大部分来源于官方白皮书JSON Developer's Guide,该书主要涵盖了一下的内容:
   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
672
SQL> SELECT json_value(po_document, '$.PONumber') FROM j_purchaseorder;
JSON_VALUE(PO_DOCUMENT,'$.PONUMBER')
--------------------------------------------------------------------------------
1600
672
SQL> 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

SQL> select json_object(key 'Purchase ID' value ID,key 'Purchase Date' value DATE_LOADED) from j_purchaseorder;
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.html
JSON 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)


Reference
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)

  
关键词:max_string_size json inmemory 12c 

相关文章

关于max_string_size
在Oracle数据库中使用JSON
19c新特性之Automatic In-memory
保障业务连续性的神器
Oracle事务卫士(Transaction Guard)和应用连续性(Application Continuity)
容器数据库(CDB)和可插拔数据库(PDB)概述
How to generate AWR on PDB and ADG(12.2 afterwards)
在12c上使用wm_concat
Exadata with database 12.2
如何在oracle 12c中正确的应用补丁?
在OEL6.8上安装12.2 RAC
Oracle Database 12.2 Hands-On Lab
Top