Oracle 12c 对NoSQL的支持（JSON）

2022-04-13 00:00:00 查询 数据 数据库 对象 利用

什么是JSON?

JSON对象是基于JavaScript的对象定义方法，完全遵循的语法JavaScript。它的目的就是非常快捷地阅读和修改，同时也便于解析和生成。在序列化结构的数据中使用比较广泛，特别适合web应用。

PS：如果大家使用过awrcrt.sql（数据库性能趋势图表）,阅读过代码就知道，awrcrt就是利用SQL/PLSQL生成JSON数据，然后利用chart.js来绘制图表。

 

Oracle database对JSON的支持?

从12.1.0.2版本，Oracle就可以对JSON数据存储，查询，和创建索引，并且可以校验JSON的语法必须满足规则。该特性还允许使用基于路径的表示法对JSON数据进行查询，并添加新的操作符，允许基于JSON路径的查询集成到SQL操作中。 JSON对比XML的大优点，JSON的长度比XML小得多。


下面用例子来说明：


在Oracle中创建一个存储JSON的表



CREATE TABLE j_purchaseorder

       (id          RAW (16) NOT NULL,

        date_loaded TIMESTAMP WITH TIME ZONE,

       po_document CLOB

       CONSTRAINT ensure_json CHECK (po_document IS JSON));


插入数据成功

SQL> INSERT INTO j_purchaseorder

  2  VALUES

  3  (SYS_GUID(),

  4   SYSTIMESTAMP,

  5   '{"PONumber"             : 1600,

  6     "Reference"            : "ABULL-20140421",

  7     "Requestor"            : "Alexis Bull",

  8     "User"                 : "ABULL",

  9     "CostCenter"           : "A50",

 10     "ShippingInstructions" : {"name"   : "Alexis Bull",

 11                               "Address": {"street"  : "200 Sporting Green",

 12                                           "city"    : "South San Francisco",

 13                                           "state"   : "CA",

 14                                           "zipCode" : 99236,

 15                                           "country" : "United States of America"},

 16                               "Phone"  : [{"type" : "Office", "number" : "909-555-7307"},

 17                                           {"type" : "Mobile", "number" : "415-555-1234"}]},

 18     "Special Instructions" : null,

 19     "AllowPartialShipment" : false,

 20     "LineItems"            : [{"ItemNumber" : 1,

 21                                "Part"       : {"Description" : "One Magic Christmas",

 22                                                "UnitPrice"   : 19.95,

 23                                                "UPCCode"     : 13131092899},

 24                                "Quantity"   : 9.0 },

 25                               {"ItemNumber" : 2,

 26                                "Part"       : {"Description" : "Lethal Weapon",

 27                                                "UnitPrice"   : 19.95,

 28                                                "UPCCode"     : 85391628927},

 29                                "Quantity"   : 5.0}]}');

 

1 row inserted


故意破坏JSON的格式，再次插入，失败。


INSERT INTO j_purchaseorder

VALUES

(SYS_GUID(),

 SYSTIMESTAMP,

 '{"PONumber"             : 1600,

   "Reference"            : "ABULL-20140421",

   "Requestor"            : "Alexis Bull",

   "User"                 : "ABULL",

   "CostCenter"           : "A50",

   "ShippingInstructions" : {"name"   : "Alexis Bull",

                             "Address": {"street"  : "200 Sporting Green",

')

 报错，Oracle能够自动检查JSON的格式

ORA-02290: 违反检查约束条件 (OTEST.ENSURE_JSON)


也可以使用nosql数据库比如mongodb导出的数据，利用外部表，导入Oracle数据库。

SQL> CREATE OR REPLACE DIRECTORY order_entry_dir AS '<ORACLE_HOME>/demo/schema/order_entry';

Directory created.

SQL> CREATE OR REPLACE DIRECTORY loader_output_dir AS '/tmp';

Directory created.

SQL> CREATE TABLE DUMP_FILE_CONTENTS(
  2   json_document CLOB
  3  )
  4  ORGANIZATION EXTERNAL
  5  ( TYPE ORACLE_LOADER
  6    DEFAULT DIRECTORY order_entry_dir
  7    ACCESS PARAMETERS
  8    ( RECORDS DELIMITED BY 0x'0A'
  9      BADFILE loader_output_dir: 'JSON_DUMPFILE_CONTENTS.bad'
 10      LOGFILE loader_output_dir: 'JSON_DUMPFILE_CONTENTS.log'
 11      FIELDS
 12      ( json_document CHAR(5000) ))
 13    LOCATION
 14    ( order_entry_dir:'PurchaseOrders.dmp' )
 15  )
 16  PARALLEL
 17  REJECT LIMIT UNLIMITED;

Table created.

SQL> INSERT INTO j_purchaseorder
  2  SELECT SYS_GUID(), SYSTIMESTAMP, json_document
  3  FROM dump_file_contents
  4  WHERE json_document IS JSON;

10000 rows created.

SQL> COMMIT;

Commit complete.


JSONO 在Oracle database的存储格式?

 VARCHAR2,RAW, CLOB and BLOB 均可

Oracle database如何查询JSON的数据?

用SQL查询NoSQL的data，非常的简单，方便。

查询REFERENCE

SQL>  SELECT   t.po_document.Reference FROM j_purchaseorder t where rownum =1  ;

 

REFERENCE

---------------------------------------------------------------------

ABULL-20140421

如果json数据中包含oracle关键字，需要用双引号扩起来

SELECT t.po_document."User"

    FROM  j_purchaseorder t

  WHERE JSON_TEXTCONTAINS(po_document, '$.LineItems.Part.Description', 'Magic');

Json_value函数也可以用于显示json的值

SQL>  SELECT json_value(po_document, '$.ShippingInstructions.name') FROM j_purchaseorder;

 

JSON_VALUE(PO_DOCUMENT,'$.SHIP

--------------------------------------------------------------------------------

Alexis Bull

 查询ShippingInstructions

SQL>  SELECT   t.po_document.ShippingInstructions FROM j_purchaseorder t where rownum =1  ;

 

SHIPPINGINSTRUCTIONS

--------------------------------------------------------------------------------

{"name":"Alexis Bull","Address":{"street":"200 Sporting Green","city":"South San

 

SQL> SELECT   t.po_document.ShippingInstructions.name FROM j_purchaseorder t where rownum =1  ;

 

SHIPPINGINSTRUCTIONS

--------------------------------------------------------------------------------

Alexis Bull

 查询更深的子节点

SQL>  SELECT   t.po_document.ShippingInstructions.Address.city FROM j_purchaseorder t where rownum =1  ;

 

SHIPPINGINSTRUCTIONS

--------------------------------------------------------------------------------

South San Francisco

 

如果查询的是非子节点，Oracle会把JSON显示出来

 

SQL> SELECT   t.po_document.ShippingInstructions.Phone from j_purchaseorder t where rownum =1;

 

SHIPPINGINSTRUCTIONS

--------------------------------------------------------------------------------

[{"type":"Office","number":"909-555-7307"},{"type":"Mobile","number":"415-555-12

 

如下查询，可以让返回的JSON的格式化

SQL> SELECT json_query(po_document, '$.ShippingInstructions' RETURNING VARCHAR2(450) PRETTY)

  2    FROM  j_purchaseorder where rownum=1;

 

JSON_QUERY(PO_DOCUMENT,'$.SHIP

--------------------------------------------------------------------------------

{

  "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"

    }

  ]

}

 

利用JSON_TABLE函数可以把JSON转换为字段

 

SELECT d.*

FROM j_purchaseorder p,

     JSON_TABLE( p.PO_DOCUMENT , '$'

         COLUMNS(

                  PO_NUMBER NUMBER(10)         PATH '$.PONumber',

                  REFERENCE VARCHAR2(30 CHAR)  PATH '$.Reference',

                  NESTED PATH '$.ShippingInstructions.Address'

                     columns(

                              ZIPCODE NUMBER(16)            PATH '$.zipCode',

                              COUNTRY VARCHAR2(32 CHAR)     PATH '$.country'),

                  NESTED PATH '$.ShippingInstructions.Phone'

                     columns(

                              tp VARCHAR2(32 CHAR)             PATH '$.type'

                              )

                 ) ) D

如果JSON对象是array包含多个对象，怎么查

SELECT t.po_document.LineItems[0].Quantity  --查询个对象

     FROM j_purchaseorder t

LINEITEMS

--------------------------------------------------------------------------------

9


如何搜索 JSON？

创建一个JSON的全文索引

CREATEINDEX po_document_index

     ON j_purchaseorder(po_document)

     INDEXTYPE IS CTXSYS.CONTEXT

    PARAMETERS ('SECTION GROUPCTXSYS.JSON_SECTION_GROUP SYNC (ON COMMIT)');

以下查询可以看出利用了索引


SQL> SELECT SUM(quantity * unitprice) TOTAL_COST
  2  FROM json.j_purchaseorder,
  3       JSON_TABLE(po_document , '$.LineItems[*]'
  4        COLUMNS(
  5        QUANTITY NUMBER(12,4)  PATH '$.Quantity',
  6        UNITPRICE NUMBER(14,2) PATH '$.Part.UnitPrice')
  7  )
  8  WHERE JSON_VALUE(po_document ,'$.ShippingInstructions.Address.city') = 'South San Francisco';

TOTAL_COST
----------
     279.3

SQL> -- The JSON_VALUE operation is optimized by the use of the PO_DOCUMENT_INDEX text index
SQL> set autotrace traceonly explain
SQL>

Execution Plan
----------------------------------------------------------
Plan hash value: 3681081768

---------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |     1 |  2018 |    34   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |                   |     1 |  2018 |            |          |
|   2 |   NESTED LOOPS                |                   |     1 |  2018 |    34   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| J_PURCHASEORDER   |     1 |  2014 |     5   (0)| 00:00:01 |
|*  4 |     DOMAIN INDEX              | PO_DOCUMENT_INDEX |       |       |     4   (0)| 00:00:01 |
|   5 |    JSONTABLE EVALUATION       |                   |       |       |            |          |
---------------------------------------------------------------------------------------------------


判断JSON是否包含了某个值

SQL> SELECT po_document
  2  FROM json.j_purchaseorder
  3  WHERE JSON_TEXTCONTAINS(po_document, '$.LineItems.Part.Description', 'Magic');

PO_DOCUMENT
--------------------------------------------------------------------------------
{"PONumber":1600,"Reference":"ABULL-20140421","Requestor":"Alexis Bull","User":"
...
ions":null,"AllowPartialShipment":true,"LineItems":[{"ItemNumber":1,"Part":{"Des
cription":"One Magic Christmas","UnitPrice":19.95,"UPCCode":13131092899},"Quanti
ty":9.0},{"ItemNumber":2,"Part":{"Description":"Lethal Weapon","UnitPrice":19.95
,"UPCCode":85391628927},"Quantity":5.0}]}

SQL> set autotrace traceonly explain
SQL>

Execution Plan
----------------------------------------------------------
Plan hash value: 1033699331

-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |     1 |  2014 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| J_PURCHASEORDER   |     1 |  2014 |     5   (0)| 00:00:01 |
|*  2 |   DOMAIN INDEX              | PO_DOCUMENT_INDEX |       |       |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Oracle 如何存储和查询JSON就演示到这里。

Oracle如何生成JSON，将关系型数据转换为JSON，可以参考官方文档

https://docs.oracle.com/database/122/ADJSN/generation.htm#ADJSN-GUID-E4DDB4E8-A4B9-4EA9-BC26-1879AA661D37

现在发现Oracle对NoSQL的支持不可谓不强，加上之前的分片，想想都有点小激动。对于同时有Mongodb和Oracle DB的客户来说，Oracle提供这一套对JSON的支持，是非常方便我们在两种数据库之间转移数据的。


具体Oracle VS Mongodb 在相同数据结构下的性能测试情况，我将在下一次更新中提供。

来源 https://www.modb.pro/db/63089

相关文章