Oracle 12.1.0.2 对JSON的支持

来源：互联网发布：sql 两个字段相等编辑：程序博客网时间：2024/06/05 09:25

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的验证限制

[oracle@oracle12c ~]$ sqlplus sde/sde@pdborclSQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 30 05:19:34 2014Copyright (c) 1982, 2014, Oracle.  All rights reserved.Last Successful login time: Tue Jul 29 2014 09:47:20 -04:00Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsSQL> 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    5Table created.

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

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> SELECT po.po_document.PONumber FROM j_purchaseorder po;PONUMBER--------------------------------------------------------------------------------1600SQL> SELECT po.po_document.ShippingInstructions.Phone FROM j_purchaseorder po;SHIPPINGINSTRUCTIONS--------------------------------------------------------------------------------[{"type":"Office","number":"909-555-7307"},{"type":"Mobile","number":"415-555-1234"}]SQL> SELECT po.po_document.ShippingInstructions.Phone.type FROM j_purchaseorder po;SHIPPINGINSTRUCTIONS--------------------------------------------------------------------------------[Office,Mobile]

0 0