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_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

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

(0)

相关推荐

  • Oracle解析复杂json的方法实例详解

    问题背景: 当前在Oracle数据库(11G之前的版本)解析json没有可以直接使用的系统方法,网上流传的PLSQL脚本大多也只可以解析结构较单一的json串,对于结构复杂的json串还无法解析.如此一来导致即便可以在PL/SQL中调用远程接口,但返回结果仍需传给前台js或java等其它代码进行处理,不太方便. 分析思路: 1.在PL/SQL中写json串,无需声明json对象,只需直接拼接成格式正确的json字符串即可,因此数据库对象间json串的传递完全可以用varchar2或clob来代替

  • Oracle截取JSON字符串内容的方法

    Oracle截取JSON字符串内容 ,具体代码如下所示: CREATE OR REPLACE FUNCTION PLATFROM.parsejsonstr(p_jsonstr varchar2,startkey varchar2,endkey varchar2) RETURN VARCHAR2 IS rtnVal VARCHAR2(1000); FindIdxS NUMBER(2); FindIdxE NUMBER(2); BEGIN if endkey='}' then rtnVal:=sub

  • 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_TRANSFOR

  • java根据数据库表内容生产树结构json数据的方法

    1.利用场景 组织机构树,通常会有组织机构表,其中有code(代码),pcode(上级代码),name(组织名称)等字段 2.构造数据(以下数据并不是组织机构数据,而纯属本人胡编乱造的数据) List<Tree<Test>> trees = new ArrayList<Tree<Test>>(); tests.add(new Test("0", "", "关于本人")); tests.add(new

  • ThinkPHP中使用ajax接收json数据的方法

    本文实例讲述了ThinkPHP中使用ajax接收json数据的方法.分享给大家供大家参考.具体分析如下: 这里通过ThinkPHP+jquery实现ajax,扩展了下,写了个查询,前台代码如下: 首先需要引入jquery.js,主要代码如下: 复制代码 代码如下: function ajax(id,pic){     //由于ThinkPHP不解析JavaScript里的ThinkPHP常量,所以需要先在这里定义. var URL='__URL__';         $.ajax({     

  • AngularJS获取json数据的方法详解

    本文实例讲述了AngularJS获取json数据的方法.分享给大家供大家参考,具体如下: 学习了这么多天的AngularJS,今天想从实战的角度和大家分享一个简单的Demo--用户查询系统,以巩固之前所学知识.功能需求需要满足两点 1.查询所有用户信息,并在前端展示 2.根据id查询用户信息,展示在前端.Ok,需求很简单,那么我们就开始实现所提的功能需求. 代码框架 前端的代码通常包含三部分:html, css, 和JavaScript,我们使用html编写视图文件,css来进行视图样式控制,J

  • 使用MSScriptControl 在 C# 中读取json数据的方法

    C#中已经有JavaScriptSerializer类可以将json数据给反序列化为对象 /// <summary> /// JSON文本转对象,泛型方法 /// </summary> /// <typeparam name="T">类型</typeparam> /// <param name="jsonText">JSON文本</param> /// <returns>指定类型的对

  • android针对json数据解析方法实例分析

    本文实例讲述了android针对json数据解析方法.分享给大家供大家参考.具体如下: JSON的定义: 一种轻量级的数据交换格式,具有良好的可读和便于快速编写的特性.业内主流技术为其提供了完整的解决方案(有点类似于正则表达式 ,获得了当今大部分语言的支持),从而可以在不同平台间进行数据交换.JSON采用兼容性很高的文本格式,同时也具备类似于C语言体系的行为. – Json.org JSON Vs XML 1.JSON和XML的数据可读性基本相同 2.JSON和XML同样拥有丰富的解析手段 3.

  • ajax调用返回php接口返回json数据的方法(必看篇)

    php代码如下: <?php header('Content-Type: application/json'); header('Content-Type: text/html;charset=utf-8'); $email = $_GET['email']; $user = []; $conn = @mysql_connect("localhost","Test","123456") or die("Failed in conn

  • springboot实现FastJson解析json数据的方法

    最近在研究springboot实现FastJson解析json数据的方法,那么今天也算个学习笔记吧! 添加jar包: <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.15</version> </dependency> 两种方式启动加载类: 第一种继承WebMvcConfigur

  • SpringMVC中controller接收json数据的方法

    本文实例为大家分享了SpringMVC中controller接收json数据的方法,供大家参考,具体内容如下 1.jsp页面发送ajax的post请求: function postJson(){ var json = {"username" : "imp", "password" : "123456"}; $.ajax({ type : "post", url : "<%=basePath

  • Jquery遍历Json数据的方法

    本文实例讲述了Jquery遍历Json数据的方法.分享给大家供大家参考.具体如下: <html> <head> <title>jQuery遍历json</title> <script src="js/jquery-1.5.2.min.js" type="text/javascript"></script> </head> <script> var a = { "

随机推荐