[原]Oracle 12.1.0.2 对JSON的支持
linghe301发表于 2015-01-30 09:11:42
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@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> 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
--------------------------------------------------------------------------------
1600
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"}]
SQL> SELECT po.po_document.ShippingInstructions.Phone.type FROM j_purchaseorder po;
SHIPPINGINSTRUCTIONS
--------------------------------------------------------------------------------
[Office,Mobile]