Oracle 12.1.0.2的版本增加了一个新的功能，就是可以存储、查询JSON数据格式，而且也实现了使用SQL语句来解析JSON。以下是来自己文档的的一个示例：

1.创建一个带有校验JSON数据的一个clob字段的表
SQL> CREATE TABLE j_purchaseorder
2 (id RAW (16) NOT NULL,
3 date_loaded TIMESTAMP WITH TIME ZONE,
4 po_document CLOB
5 CONSTRAINT ensure_json CHECK (po_document IS JSON));

Table created.

2.[oracle@odb12c ~]$ vi t.sql
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",
"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}]}');
SQL> @t
1 row created.

3.查询起来也很方便
SQL> SELECT po.po_document.PONumber FROM j_purchaseorder po;
PONUMBER
--------------------------------------------------------------------------------
1600
SQL>
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]

ITPub博客

Oracle 12c 对Json数据的支持

最新文章