目錄

• 備注:
• 一. Json數(shù)據(jù)存儲
• 二. Json數(shù)據(jù)insert
• 三. json數(shù)據(jù)update
• 四. json數(shù)據(jù)查詢
• 五. 常用的json函數(shù)
• 5.1 json_array
• 5.2 JSON_ARRAYAGG
• 5.3 JSON_DATAGUIDE
• 5.4 JSON_MERGEPATCH
• 5.5 JSON_OBJECT
• 5.6 JSON_OBJECTAGG
• 5.7 JSON_QUERY
• 5.8 json_serialize
• 5.9 JSON_TABLE
• 5.10 JSON_TRANSFORM
• 5.11 JSON_VALUE

備注:

Oracle 19C

一. Json數(shù)據(jù)存儲

看了下官網(wǎng)，Json數(shù)據(jù)一般使用varchar2(400),varchar2(32676)或者BLOB來存儲Json數(shù)據(jù)。
代碼:

create table test_json(id number,json_text varchar2(4000) CONSTRAINT ensure_json CHECK (json_text IS JSON));

二. Json數(shù)據(jù)insert

數(shù)據(jù)準(zhǔn)備:

insert into test_jsonselect rownum as rn,       json_text from  (select json_object( "deptno" value d.deptno, "dname" value d.dname, "loc" value d.loc, "emps" value json_arrayagg (     json_object("empno" value e.empno,"ename" value e.ename,"job"   value e.job,"mgr"   value e.mgr,"hiredate" value e.hiredate,"sal" value e.sal,"comm" value e.comm     )     ))  as json_text  from dept d  left join emp e  on d.deptno = e.deptno  group by d.deptno,d.dname,d.loc  ) tmp  ;

*查看json數(shù)據(jù):
deptno為40的沒有員工，也都進(jìn)入了，這個看起來有點奇怪

我們看看deptno為10的json數(shù)據(jù)

三. json數(shù)據(jù)update

上一步 deptno為40的沒有員工，也都進(jìn)入了，這個看起來有點奇怪 ，我需要emps后面的都去除掉。

代碼:

UPDATE TEST_JSON SET json_text =  json_mergepatch(json_text, "{"deptno":40,"dname":"OPERATIONS","loc":"BOSTON","emps" : null}") where id = 4   ;

測試記錄:

四. json數(shù)據(jù)查詢

上一個步驟中，我們插入的部門數(shù)據(jù)，每個部門都有0或多個員工，此時我們顯示部門名稱，以及部門下所有的員工。

代碼:

select t.id,       t.json_text.dname,       t.json_text.emps.ename from TEST_JSON t ;

測試記錄:
不得不說，Oracle的json功能真的太方便了

代碼2:

select t.id,       JSON_QUERY(t.json_text, "$.emps.ename" WITH WRAPPER) from TEST_JSON t ;

測試記錄2:

五. 常用的json函數(shù)

5.1 json_array

如果json中要存數(shù)組的話，可以使用json_array函數(shù)

SQL> select JSON_ARRAY(1,2,3) from dual;JSON_ARRAY(1,2,3)--------------------------------------------------------------------------------[1,2,3]

5.2 JSON_ARRAYAGG

將多列數(shù)據(jù)轉(zhuǎn)換為一個數(shù)組類型，例如第二步insert的時候就有使用JSON_ARRAYAGG函數(shù)。

代碼:

insert into test_jsonselect rownum as rn,       json_text from  (select json_object( "deptno" value d.deptno, "dname" value d.dname, "loc" value d.loc, "emps" value json_arrayagg (     json_object("empno" value e.empno,"ename" value e.ename,"job"   value e.job,"mgr"   value e.mgr,"hiredate" value e.hiredate,"sal" value e.sal,"comm" value e.comm     )     ))  as json_text  from dept d  left join emp e  on d.deptno = e.deptno  group by d.deptno,d.dname,d.loc  ) tmp  ;

官網(wǎng)測試demo:

CREATE TABLE id_table (id NUMBER);INSERT INTO id_table VALUES(624);INSERT INTO id_table VALUES(null);INSERT INTO id_table VALUES(925);INSERT INTO id_table VALUES(585);SELECT JSON_ARRAYAGG(id ORDER BY id RETURNING VARCHAR2(100)) ID_NUMBERS  FROM id_table;

官網(wǎng)測試demo:

SQL> CREATE TABLE id_table (id NUMBER);Table createdSQL> INSERT INTO id_table VALUES(624);1 row insertedSQL> INSERT INTO id_table VALUES(null);1 row insertedSQL> INSERT INTO id_table VALUES(925);1 row insertedSQL> INSERT INTO id_table VALUES(585);1 row insertedSQL> SQL> SELECT JSON_ARRAYAGG(id ORDER BY id RETURNING VARCHAR2(100)) ID_NUMBERS  2    FROM id_table;ID_NUMBERS--------------------------------------------------------------------------------[585,624,925]SQL> 

5.3 JSON_DATAGUIDE

聚合函數(shù)JSON_DATAGUIDE接受JSON數(shù)據(jù)的表列作為輸入，并將數(shù)據(jù)指南作為CLOB返回。列中的每一行都被稱為一個JSON文檔。對于列中的每個JSON文檔，該函數(shù)返回一個CLOB值，其中包含該JSON文檔的平面數(shù)據(jù)指南。

代碼:

select t.id,       --t.json_text,       JSON_DATAGUIDE(t.json_text) from TEST_JSON t group by t.id order by t.id ;

測試記錄:

5.4 JSON_MERGEPATCH

用于update json文檔數(shù)據(jù)
代碼:

UPDATE TEST_JSON SET json_text =  json_mergepatch(json_text, "{"deptno":40,"dname":"OPERATIONS","loc":"BOSTON","emps" : null}") where id = 4   ;

測試記錄:

5.5 JSON_OBJECT

SQL/JSON函數(shù)JSON_OBJECT接受一系列鍵-值對或一個對象類型實例作為輸入。集合類型不能傳遞給JSON_OBJECT。

代碼:

select empno,       JSON_OBJECT(key "empno" value empno,   key "ename" value ename,   key "job" value job) as emp_json  from emp;

測試記錄:

5.6 JSON_OBJECTAGG

SQL/JSON函數(shù)JSON_OBJECTAGG是一個聚合函數(shù)。它將屬性鍵-值對作為其輸入。通常，屬性鍵、屬性值或兩者都是SQL表達(dá)式的列。該函數(shù)為每個鍵-值對構(gòu)造一個對象成員，并返回一個包含這些對象成員的JSON對象。

代碼:

select JSON_OBJECTAGG(key dname value deptno) as depts  from dept

測試記錄:

5.7 JSON_QUERY

JSON_QUERY從JSON數(shù)據(jù)中選擇并返回一個或多個值，然后返回這些值。可以使用JSON_QUERY檢索JSON文檔的片段。

代碼:

select t.id,       JSON_QUERY(t.json_text, "$.emps.ename" WITH WRAPPER) from TEST_JSON t ;

測試記錄:

5.8 json_serialize

json_serialize函數(shù)接受任何SQL數(shù)據(jù)類型(VARCHAR2、CLOB、BLOB)的JSON數(shù)據(jù)作為輸入，并返回其文本表示。通常使用它來轉(zhuǎn)換查詢的結(jié)果。

可以使用json_serialize將二進(jìn)制JSON數(shù)據(jù)轉(zhuǎn)換為文本形式(VARCHAR2或CLOB)，或者通過對文本JSON數(shù)據(jù)進(jìn)行精細(xì)打印或?qū)ζ渲械姆莂scii Unicode字符進(jìn)行轉(zhuǎn)義來轉(zhuǎn)換文本JSON數(shù)據(jù)。

測試記錄:

SQL> SELECT JSON_SERIALIZE ("{a:[1,2,3,4]}" RETURNING VARCHAR2(10) TRUNCATE ERROR ON ERROR) from dual;JSON_SERIALIZE("{A:[1,2,3,4]}"------------------------------{"a":[1,2,

5.9 JSON_TABLE

SQL/JSON函數(shù)JSON_TABLE創(chuàng)建JSON數(shù)據(jù)的關(guān)系視圖。它將JSON數(shù)據(jù)計算的結(jié)果映射到關(guān)系行和列中。可以使用SQL將函數(shù)返回的結(jié)果作為虛擬關(guān)系表進(jìn)行查詢。JSON_TABLE的主要目的是為JSON數(shù)組中的每個對象創(chuàng)建一行關(guān)系數(shù)據(jù)，并將該對象中的JSON值作為單獨的SQL列值輸出。

代碼:

SELECT t.*FROM test_jsonNESTED json_text COLUMNS(dname, deptno) t;

測試記錄:

代碼2:

SELECT t.*FROM test_json LEFT OUTER JOINJSON_TABLE(json_text COLUMNS(dname, deptno)) t ON 1=1;

測試記錄2:

5.10 JSON_TRANSFORM

使用JSON_TRANSFORM修改JSON文檔輸入到函數(shù)中。通過指定一個或多個對JSON數(shù)據(jù)執(zhí)行更改的修改操作，可以更改JSON文檔(或JSON文檔的部分)。修改后的JSON文檔作為輸出返回。

5.11 JSON_VALUE

SQL/JSON函數(shù)JSON_VALUE在JSON數(shù)據(jù)中查找指定的標(biāo)量JSON值，并將其作為SQL值返回。

測試記錄:

SQL> SELECT JSON_VALUE("{a:100}", "$.a") AS value  2    FROM DUAL;VALUE--------------------------------------------------------------------------------100SQL> 

參考:

到此這篇關(guān)于Oracle 處理json數(shù)據(jù)的文章就介紹到這了,更多相關(guān)Oracle 處理json數(shù)據(jù)內(nèi)容請搜索以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持！