Oracle 處理json數據的方法
目錄
- 備注:
- 一. Json數據存儲
- 二. Json數據insert
- 三. json數據update
- 四. json數據查詢
- 五. 常用的json函數
- 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數據存儲
看了下官網，Json數據一般使用varchar2(400),varchar2(32676)或者BLOB來存儲Json數據。
代碼:
create table test_json(id number,json_text varchar2(4000) CONSTRAINT ensure_json CHECK (json_text IS JSON));
二. Json數據insert
數據準備:
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數據:
deptno為40的沒有員工，也都進入了，這個看起來有點奇怪
我們看看deptno為10的json數據
三. json數據update
上一步 deptno為40的沒有員工，也都進入了，這個看起來有點奇怪 ，我需要emps后面的都去除掉。
代碼:
UPDATE TEST_JSON SET json_text = json_mergepatch(json_text, "{"deptno":40,"dname":"OPERATIONS","loc":"BOSTON","emps" : null}") where id = 4 ;測試記錄:
四. json數據查詢
上一個步驟中，我們插入的部門數據，每個部門都有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函數
5.1 json_array
如果json中要存數組的話，可以使用json_array函數
SQL> select JSON_ARRAY(1,2,3) from dual;JSON_ARRAY(1,2,3)--------------------------------------------------------------------------------[1,2,3]
5.2 JSON_ARRAYAGG
將多列數據轉換為一個數組類型，例如第二步insert的時候就有使用JSON_ARRAYAGG函數。
代碼:
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 ;官網測試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;
官網測試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
聚合函數JSON_DATAGUIDE接受JSON數據的表列作為輸入，并將數據指南作為CLOB返回。列中的每一行都被稱為一個JSON文檔。對于列中的每個JSON文檔，該函數返回一個CLOB值，其中包含該JSON文檔的平面數據指南。
代碼:
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文檔數據
代碼:
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函數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函數JSON_OBJECTAGG是一個聚合函數。它將屬性鍵-值對作為其輸入。通常，屬性鍵、屬性值或兩者都是SQL表達式的列。該函數為每個鍵-值對構造一個對象成員，并返回一個包含這些對象成員的JSON對象。
代碼:
select JSON_OBJECTAGG(key dname value deptno) as depts from dept
測試記錄:
5.7 JSON_QUERY
JSON_QUERY從JSON數據中選擇并返回一個或多個值，然后返回這些值?？梢允褂肑SON_QUERY檢索JSON文檔的片段。
代碼:
select t.id, JSON_QUERY(t.json_text, "$.emps.ename" WITH WRAPPER) from TEST_JSON t ;
測試記錄:
5.8 json_serialize
json_serialize函數接受任何SQL數據類型(VARCHAR2、CLOB、BLOB)的JSON數據作為輸入，并返回其文本表示。通常使用它來轉換查詢的結果。
可以使用json_serialize將二進制JSON數據轉換為文本形式(VARCHAR2或CLOB)，或者通過對文本JSON數據進行精細打印或對其中的非ascii Unicode字符進行轉義來轉換文本JSON數據。
測試記錄:
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函數JSON_TABLE創建JSON數據的關系視圖。它將JSON數據計算的結果映射到關系行和列中?？梢允褂肧QL將函數返回的結果作為虛擬關系表進行查詢。JSON_TABLE的主要目的是為JSON數組中的每個對象創建一行關系數據，并將該對象中的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文檔輸入到函數中。通過指定一個或多個對JSON數據執行更改的修改操作，可以更改JSON文檔(或JSON文檔的部分)。修改后的JSON文檔作為輸出返回。
5.11 JSON_VALUE
SQL/JSON函數JSON_VALUE在JSON數據中查找指定的標量JSON值，并將其作為SQL值返回。
測試記錄:
SQL> SELECT JSON_VALUE("{a:100}", "$.a") AS value 2 FROM DUAL;VALUE--------------------------------------------------------------------------------100SQL> 參考:
到此這篇關于Oracle 處理json數據的文章就介紹到這了,更多相關Oracle 處理json數據內容請搜索以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持！
網公網安備