什么是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
