Oracle?处理json数据的方法(oracle json_table)深度揭秘

随心笔谈11个月前发布 admin
105 0



目录备注:一. Json数据存储二. Json数据insert三. json数据update四. json数据查询五. 常用的json函数5.1 json_array5.2 JSON_ARRAYAGG5.3 JSON_DATAGUIDE5.4 JSON_MERGEPATCH5.5 JSON_OBJECT5.6 JSON_OBJECTAGG5.7 JSON_QUERY5.8 json_serialize5.9 JSON_TABLE5.10 JSON_TRANSFORM5.11 JSON_VALUE

Oracle 19C

看了下官网,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));

数据准备:

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数据:deptno为40的没有员工,也都进入了,这个看起来有点奇怪

image.png

我们看看deptno为10的json数据

image.png

上一步 deptno为40的没有员工,也都进入了,这个看起来有点奇怪 ,我需要emps后面的都去除掉。

代码:

UPDATE TEST_JSON SET json_text=json_mergepatch(json_text, ”)
where id=4
;

测试记录:

image.png

上一个步骤中,我们插入的部门数据,每个部门都有0或多个员工,此时我们显示部门名称,以及部门下所有的员工。

代码:

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

测试记录:不得不说,Oracle的json功能真的太方便了

image.png

代码2:

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

测试记录2:

image.png

如果json中要存数组的话,可以使用json_array函数

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

将多列数据转换为一个数组类型,例如第二步insert的时候就有使用JSON_ARRAYAGG函数。

代码:

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
;

官网测试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 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>

聚合函数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
;

测试记录:

image.png

image.png

用于update json文档数据

代码:

UPDATE TEST_JSON SET json_text=json_mergepatch(json_text, ”)
where id=4
;

测试记录:

image.png

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;

测试记录:

image.png

SQL/JSON函数JSON_OBJECTAGG是一个聚合函数。它将属性键-值对作为其输入。通常,属性键、属性值或两者都是SQL表达式的列。该函数为每个键-值对构造一个对象成员,并返回一个包含这些对象成员的JSON对象。

image.png

代码:

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

测试记录:

image.png

JSON_QUERY从JSON数据中选择并返回一个或多个值,然后返回这些值。可以使用JSON_QUERY检索JSON文档的片段。

image.png

代码:

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

测试记录:

image.png

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,

SQL/JSON函数JSON_TABLE创建JSON数据的关系视图。它将JSON数据计算的结果映射到关系行和列中。可以使用SQL将函数返回的结果作为虚拟关系表进行查询。JSON_TABLE的主要目的是为JSON数组中的每个对象创建一行关系数据,并将该对象中的JSON值作为单独的SQL列值输出。

image.png

代码:

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

测试记录:

image.png

代码2:

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

测试记录2:

image.png

使用JSON_TRANSFORM修改JSON文档输入到函数中。通过指定一个或多个对JSON数据执行更改的修改操作,可以更改JSON文档(或JSON文档的部分)。修改后的JSON文档作为输出返回。

image.png

SQL/JSON函数JSON_VALUE在JSON数据中查找指定的标量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/adjsn/json-in-oracle-database.html#GUID-A8A58B49-13A5-4F42-8EA0-508951DAE0BB

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_QUERY.html

到此这篇关于Oracle 处理json数据的文章就介绍到这了,更多相关Oracle 处理json数据内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家!

您可能感兴趣的文章:oracle中对JSON数据处理的详细指南

© 版权声明

相关文章