Oracle 12.1.0.2 对JSON的支持

郑全

Oracle 12.1.0.2版本有一个新功能就是可以存储、查询、索引JSON数据格式，而且也实现了使用SQL语句来解析JSON，非常方便。JSON数据在数据库中以VARCHAR2, CLOB或者BLOB进行存储。Oracle建议用户在插入JSON数据之前,使用is_json来验证输入JSON数据的正确性。另外，Oracle也提供了相关的函数：

• Functions：json_value, json_query,  json_table.
• Conditions：json_exists, is json, is not json, json_textcontains.

 

初识Oracle数据库使用JSON

1：创建一个带有表，里面包含存储JSON数据的字段类型比如CLOB，而且需要对输入JSON的验证限制

[sql] view plaincopyprint?

1. [oracle@oracle12c ~]$ sqlplus sde/sde@pdborcl  
2.   
3. SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 30 05:19:34 2014  
4.   
5. Copyright (c) 1982, 2014, Oracle.  All rights reserved.  
6.   
7. Last Successful login time: Tue Jul 29 2014 09:47:20 -04:00  
8.   
9. Connected to:  
10. Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production  
11. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options  
12.   
13. SQL> CREATE TABLE j_purchaseorder  
14.    (id          RAW (16) NOT NULL,  
15.     date_loaded TIMESTAMP WITH TIME ZONE,  
16.     po_document CLOB  
17.     CONSTRAINT ensure_json CHECK (po_document IS JSON));  2    3    4    5  
18.   
19. Table created.  

[oracle@oracle12c ~]$ sqlplus sde/sde@pdborcl

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 30 05:19:34 2014

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Tue Jul 29 2014 09:47:20 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> 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));  2    3    4    5

Table created.

2：插入带有JSON数据的一条记录

[sql] view plaincopyprint?

1. SQL> INSERT INTO j_purchaseorder  
2.   2    VALUES (SYS_GUID(),  
3.   3            SYSTIMESTAMP,  
4.   4  '{ "PONumber"             : 1600,  
5.   5    "Reference"            : "ABULL-20140421",  
6.   6    "Requestor"            : "Alexis Bull",  
7.   7    "User"                 : "ABULL",  
8.   8    "CostCenter"           : "A50",  
9.   9    "ShippingInstructions" : { "name"   : "Alexis Bull",  
10.  10                               "Address": { "street"  : "200 Sporting Green",  
11.  11                                            "city"    : "South San Francisco",  
12.  12                                            "state"   : "CA",  
13.  13                                            "zipCode" : 99236,  
14.  14                                            "country" : "United States of America" },  
15.  15                               "Phone" : [ { "type" : "Office", "number" : "909-555-7307" },  
16.                                          { "type" : "Mobile", "number" : "415-555-1234" } ] },  
17.  16   17    "Special Instructions" : null,  
18.  18    "AllowPartialShipment" : false,  
19.  19    "LineItems"            : [ { "ItemNumber" : 1,  
20.  20                                 "Part"       : { "Description" : "One Magic Christmas",  
21.  21                                                  "UnitPrice"   : 19.95,  
22.  22                                                  "UPCCode"     : 13131092899 },  
23.  23                                 "Quantity"   : 9.0 },  
24.  24                               { "ItemNumber" : 2,  
25.  25                                 "Part"       : { "Description" : "Lethal Weapon",  
26.  26                                                  "UnitPrice"   : 19.95,  
27.  27                                                  "UPCCode"     : 85391628927 },  
28.  28                                 "Quantity"   : 5.0 } ] }');  
29.   
30. 1 row created.  

SQL> INSERT INTO j_purchaseorder
  2    VALUES (SYS_GUID(),
  3            SYSTIMESTAMP,
  4  '{ "PONumber"             : 1600,
  5    "Reference"            : "ABULL-20140421",
  6    "Requestor"            : "Alexis Bull",
  7    "User"                 : "ABULL",
  8    "CostCenter"           : "A50",
  9    "ShippingInstructions" : { "name"   : "Alexis Bull",
 10                               "Address": { "street"  : "200 Sporting Green",
 11                                            "city"    : "South San Francisco",
 12                                            "state"   : "CA",
 13                                            "zipCode" : 99236,
 14                                            "country" : "United States of America" },
 15                               "Phone" : [ { "type" : "Office", "number" : "909-555-7307" },
                                         { "type" : "Mobile", "number" : "415-555-1234" } ] },
 16   17    "Special Instructions" : null,
 18    "AllowPartialShipment" : false,
 19    "LineItems"            : [ { "ItemNumber" : 1,
 20                                 "Part"       : { "Description" : "One Magic Christmas",
 21                                                  "UnitPrice"   : 19.95,
 22                                                  "UPCCode"     : 13131092899 },
 23                                 "Quantity"   : 9.0 },
 24                               { "ItemNumber" : 2,
 25                                 "Part"       : { "Description" : "Lethal Weapon",
 26                                                  "UnitPrice"   : 19.95,
 27                                                  "UPCCode"     : 85391628927 },
 28                                 "Quantity"   : 5.0 } ] }');

1 row created.

3：进行查询

[sql] view plaincopyprint?

1. SQL> SELECT po.po_document.PONumber FROM j_purchaseorder po;  
2.   
3. PONUMBER  
4. --------------------------------------------------------------------------------  
5. 1600  
6.   
7. SQL> SELECT po.po_document.ShippingInstructions.Phone FROM j_purchaseorder po;  
8.   
9. SHIPPINGINSTRUCTIONS  
10. --------------------------------------------------------------------------------  
11. [{"type":"Office","number":"909-555-7307"},{"type":"Mobile","number":"415-555-12  
12. 34"}]  
13.   
14.   
15. SQL> SELECT po.po_document.ShippingInstructions.Phone.type FROM j_purchaseorder po;  
16.   
17. SHIPPINGINSTRUCTIONS  
18. --------------------------------------------------------------------------------  
19. [Office,Mobile]