Oracle?處理json數(shù)據(jù)的方法
備注:
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_json
select 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_json
select 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 created SQL> INSERT INTO id_table VALUES(624); 1 row inserted SQL> INSERT INTO id_table VALUES(null); 1 row inserted SQL> INSERT INTO id_table VALUES(925); 1 row inserted SQL> INSERT INTO id_table VALUES(585); 1 row inserted SQL> 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)系行和列中?？梢允褂肧QL將函數(shù)返回的結(jié)果作為虛擬關(guān)系表進(jìn)行查詢。JSON_TABLE的主要目的是為JSON數(shù)組中的每個對象創(chuàng)建一行關(guān)系數(shù)據(jù)，并將該對象中的JSON值作為單獨的SQL列值輸出。
代碼:
SELECT t.* FROM test_json NESTED json_text COLUMNS(dname, deptno) t;
測試記錄:
代碼2:
SELECT t.* FROM test_json LEFT OUTER JOIN JSON_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
--------------------------------------------------------------------------------
100
SQL> 參考:
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_QUERY.html
到此這篇關(guān)于Oracle 處理json數(shù)據(jù)的文章就介紹到這了,更多相關(guān)Oracle 處理json數(shù)據(jù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
ORA-00947:Not enough values (沒有足夠的值)的深入分析
本篇文章是對ORA-00947:Not enough values (沒有足夠的值)的解決方法進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下2013-05-05
自動備份Oracle數(shù)據(jù)庫...2007-03-03
Oracle 10g 服務(wù)器端安裝預(yù)備步驟(詳細(xì)圖文教程)
這篇文章主要介紹了Oracle 10g 服務(wù)器端安裝預(yù)備步驟(詳細(xì)圖文教程),需要的朋友可以參考下2017-03-03
Oracle之關(guān)于各類連接超時相關(guān)參數(shù)學(xué)習(xí)
這篇文章主要介紹了Oracle之關(guān)于各類連接超時相關(guān)參數(shù)，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-04-04
本篇文章是對oracle中去掉回車換行空格的解決方法進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下2013-05-05
本文通過實際業(yè)務(wù)系統(tǒng)中調(diào)整的一個案例，試圖給出一個常見CPU消耗問題的一個診斷方法.2007-03-03
關(guān)于Oracle12C默認(rèn)用戶名system密碼不正確的解決方案
這篇文章主要介紹了關(guān)于Oracle12C默認(rèn)用戶名system密碼不正確的解決方案 ,本文給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2021-10-10
Oracle數(shù)據(jù)庫索引的維護(hù)...2007-03-03
Oracle中nvl()和nvl2()函數(shù)實例詳解
NVL函數(shù)的功能是實現(xiàn)空值的轉(zhuǎn)換,根據(jù)第一個表達(dá)式的值是否為空值來返回響應(yīng)的列名或表達(dá)式,下面這篇文章主要給大家介紹了關(guān)于Oracle中nvl()和nvl2()函數(shù)的相關(guān)資料,需要的朋友可以參考下2022-05-05
這篇文章主要介紹了Oracle的SYS_GUID()函數(shù)用法及說明，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-07-07