Oracle 处理json数据的方法
作者:只是甲 发布时间:2024-01-16 15:11:15
备注:
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_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的没有员工,也都进入了,这个看起来有点奇怪
我们看看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_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>
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数据中选择并返回一个或多个值,然后返回这些值。可以使用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函数接受任何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数据计算的结果映射到关系行和列中。可以使用SQL将函数返回的结果作为虚拟关系表进行查询。JSON_TABLE的主要目的是为JSON数组中的每个对象创建一行关系数据,并将该对象中的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文档输入到函数中。通过指定一个或多个对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
--------------------------------------------------------------------------------
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
来源:https://blog.csdn.net/u010520724/article/details/127728168
猜你喜欢
- 本文实例为大家分享了python绘制分组对比柱状图的具体代码,供大家参考,具体内容如下首先放效果图: # -*- co
- 下载了一个小型的记帐软件,发现这个软件数据库用的是access,很想看看它的数据库结构怎样,结果人家加密了。access的解密小case了,
- 前言:什么是cookie?Cookie,指某些网站为了辨别用户身份、进行session跟踪而储存在用户本地终端上的数据(通常经过加密)。比如
- 自然语言处理是计算机科学领域与人工智能领域中的一个重要方向。自然语言工具箱(NLTK,NaturalLanguageToolkit)是一个基
- 导航设计是结构层面设计中的主要工作之一,在软件中,导航设计的好坏,直接关系到用户使用是否能够流畅。面对较复杂的导航,我们第一反应是将其简化。
- 一、Python 的 IDE —— PyCharm1.1 集成开发环境(IDE)集成开发环境(IDE,Integrated Developm
- django model的json字段的编码器不能有效编码诸如uuid,datetime等数据类型,当直接存储此类型的对象到json字段中为
- 本文实例讲述了Python简单生成随机数的方法。分享给大家供大家参考,具体如下:主要知识点:随机整数:random.randint(a,b)
- 模版结构优化引入模版有时候一些代码是在许多模版中都用到的。如果我们每次都重复的去拷贝代码那肯定不符合项目的规范。一般我们可以把这些重复性的代
- 直接在线安装1、File->Settings->Plugins->Install JetBrains Plugins2、点
- 我将示范微优化(micro optimization)如何提升python代码5%的执行速度。5%!同时也会触怒任何维护你代码的人。但实际上
- 从文本文件中读入浮点数据,是最常见的任务之一,python没有scanf这样的输入函数,但我们可以利用正规表达式从读入的字符串中提取出浮点数
- 1.列表:list# 1.list:Python内置的一种数据类型,列表;# 2.list是一种有序的集合,可以随时添加和删除其中的元素;#
- 当我们使用一个数据库时,总希望数据库的内容是可靠的、正确的,但由于计算机系统的故障(包括机器故障、介质故障、误操作等),数据库有时也可能遭到
- 对于Dreamweaver这个广大用户早有听闻的网页编辑工具,除功能强大外,Dreamweaver一向被推崇为同类网页编辑软件中产生垃圾代码
- 用Python实现点对点的聊天,2个程序,一个是client.py,一个是server.py,通过本机地址127.0.0.1连接进行通信,利
- 摘要: 本文由简到繁地介绍了以jQuery作为蓝本的js框架开发步聚, 希望借助本文大家对jQuery这样的框架内部有一个大致的认识。推荐:
- 下面是我已经证实可用的自动备份的方法. 1、打开企业管理器->管理->sql server代理 2、新建一个作业,作业名称随便取
- 动态变量名赋值在使用 tkinter 时需要动态生成变量,如动态生成 var1...var10 变量。使用 exec 动态赋值exec 在
- 在学习python的时候,三大“名器”对没有其他语言编程经验的人来说,应该算是一个小难点,本次博客就博主自己对装饰器、迭代器和生成器理解进行