oracle19c JSON常用函数

已于 2024-11-10 20:07:04 修改
阅读量2.6k 收藏 16
点赞数 17
文章标签： json oracle 数据库 dba
于 2024-01-24 19:58:05 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/u011048844/article/details/135829618
版权

首先要说明的是，本文的主要以案例为主，具体函数的说明可以查看oracle官方文档，虽然是19c，但是这里参考了oracle 21c的说明

SQL For JSON Conditions

主要为两个模块

  1. 环境准备

数据库：oracle 19c

创建一张表

​CREATE TABLE "MY_JSON_TABLE" (
  "ID" NUMBER VISIBLE NOT NULL,
  "CODE" VARCHAR2(255 BYTE) VISIBLE,
  "NAME" VARCHAR2(255 BYTE) VISIBLE,
  "JDATA" CLOB VISIBLE
) ;

初始化数据，这里插入了10W

BEGIN
   FOR i IN 1..100000 LOOP
	INSERT INTO "MY_JSON_TABLE"(ID, CODE, NAME, JDATA) VALUES (i, 'MJ-CD-'||i, '测试'||1, '{
    "name":"龙傲天",
    "age":18,
    "phone":[ "15088888888", "13966666666","0571-88888888"],
    "id":"333333199911114567",
    "eamil":"113@126.com",
    "work":[
        {
            "company":"快乐无限",
            "post":"销售经理",
            "address":"杭州一号大街"
        },
        {
            "company":"高兴上班",
            "post":"售前总监",
            "address":"浙江杭州滨江"
        },
        {
            "company":"没班上",
            "post":"务农",
            "address":"浙江杭州富阳"
        }
    ]
}');
   END LOOP;
   COMMIT;
END;
/

  1. 函数

JSON路径表达式通配符

在查询开始之前，我们先来看下通配符的说明，后续查询条件及各种操作时，都会用到JSON PATH

通配符

说明

$

表示根元素

.

表示当前元素

[*]

表示匹配数组中的所有元素

[]

表示过滤数组中符合条件的元素

@

表示当前元素的属性

    1. JSON_OBJECT

JSON_OBJECT接受一系列键-值对或一个对象类型实例作为输入，返回一个JSON类型的对象。集合类型不能传递给JSON_OBJECT。

将MY_JSON_TABLE表中的CODE，NAME字段组合成一个JSON字符串

SELECT 
  JSON_OBJECT(CODE,NAME)
FROM MY_JSON_TABLE
WHERE id = 1;

查询结果字段都转成json格式

select JSON_OBJECT(*) from "flyway_schema_history";

可以更换字段名称，key关键字可以省略

SELECT 
  JSON_OBJECT(
		key 'CODE1' VALUE CODE,
		key 'NAME1' VALUE NAME
	)
FROM MY_JSON_TABLE
WHERE id = 1;

可以和JSON_ARRAY一起使用，将多行结果转成一个json数组，JSON_ARRAY后面章节中有讲到

SELECT 
JSON_ARRAYAGG(
		JSON_OBJECT(
			key 'CODE1' VALUE CODE,
			key 'NAME1' VALUE NAME
		)
	)
FROM MY_JSON_TABLE
WHERE rownum < 5;

  NULL ON NULL和ABSENT ON NULL，可以选择保留或者忽略nul值。缺省是 NULL ON NULL

SELECT 
JSON_ARRAYAGG(
		JSON_OBJECT(
			key 'CODE1' VALUE CODE,
			key 'NAME1' VALUE null  NULL ON NULL
		)
	)
FROM MY_JSON_TABLE
WHERE rownum < 5;


SELECT 
JSON_ARRAYAGG(
		JSON_OBJECT(
			key 'CODE1' VALUE CODE,
			key 'NAME1' VALUE null  ABSENT ON NULL
		)
	)
FROM MY_JSON_TABLE
WHERE rownum < 5;

STRICT关键字，严格校验json，不符合会报错

SELECT JSON_OBJECT ('name' value 'Foo' FORMAT JSON ) FROM DUAL;



SELECT JSON_OBJECT ('name' value 'Foo' FORMAT JSON STRICT ) FROM DUAL;

还可以把两个字段组合在一起，一个作为key，一个作为value

SELECT JSON_OBJECT(
 key CODE value NAME
 )
FROM MY_JSON_TABLE
WHERE rownum < 10 
ORDER BY id;

    1. JSON_OBJECTAGG

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

SELECT JSON_OBJECTAGG(
	KEY CODE VALUE NAME
)  
FROM MY_JSON_TABLE
WHERE  rownum < 10 ;

    1. IS JSON 

判断字段内容是否是JSON格式

select JDATA from MY_JSON_TABLE where JDATA IS JSON and rownum < 10;

后面可以跟关键字 STRICT 和 LAX，缺省使用LAX，STRICT会严格校验JSON

select JDATA from MY_JSON_TABLE where JDATA IS JSON STRICT and rownum < 10;

也可以判断不是JSON的，且可以两者一起使用。如：是JSON，但不是严格的JSON

{ "Grade Values" : { A : 4.0, B : 3.0, C : 2.0 } }
select JDATA from MY_JSON_TABLE where JDATA IS NOT JSON STRICT and JDATA IS JSON LAX AND rownum < 10;

还可以使用WITH UNIQUE KEYS 过滤JSON中有重复字段的数据, 缺省其实是WITHOUT UNIQUE KEYS 。当然如果后面有STRICT关键字也是一样的，重复字段也是不允许的

{a:100, a:200, b:300}
select count(1) from MY_JSON_TABLE where JDATA IS JSON WITH UNIQUE KEYS ;

    1. JSON_EXISTS

 判断符合条件的JSON

判断JSON中是否存在字段

SELECT count(1) FROM MY_JSON_TABLE WHERE JSON_EXISTS(JDATA, '$.name');

如果是JSON ARRAY，则可以加 [0] -取第一个对象,[*]-任意对象

SELECT JDATA FROM MY_JSON_TABLE WHERE JSON_EXISTS(JDATA, '$[0].first');

如果需要把不是JSON的一起查询出来，则需要加关键字  TRUE ON ERROR 

SELECT  JDATA FROM MY_JSON_TABLE WHERE JSON_EXISTS(JDATA, '$[*].first' TRUE ON ERROR );

上述只是判断字段是否存在，并不能判断具体值，下面开始说明值查询

相等

SELECT * FROM MY_JSON_TABLE  WHERE JSON_EXISTS(JDATA, '$?(@.name == "龙傲天")');
SELECT * FROM MY_JSON_TABLE  WHERE JSON_EXISTS(JDATA, '$[*]?(@.name == "龙傲天")');

全等还有另外一种写法，可以把参数提取出来，var1就是提取出来的字段名，但是如果条件多的情况下就不是很好处理

SELECT * FROM MY_JSON_TABLE  WHERE JSON_EXISTS(JDATA, '$[*]?(@.name == $var1)'  PASSING '龙傲天' as "var1");

不相等

SELECT  * FROM MY_JSON_TABLE WHERE JSON_EXISTS(JDATA, '$?(@.age <> 18)' );
SELECT  * FROM MY_JSON_TABLE WHERE JSON_EXISTS(JDATA, '$?(@.age != 18)' );

模糊查询

模糊查询我们要用到like，这与普通模糊查询是一样。

SELECT * FROM MY_JSON_TABLE  WHERE JSON_EXISTS(JDATA, '$[*]?(@.name like "龙%")');

与全等一样，参数也可以提取出来，后面就不再举例

SELECT * FROM MY_JSON_TABLE  WHERE JSON_EXISTS(JDATA, '$[*]?(@.name like $var2)'  PASSING '龙%' as "var2");

not like就有区别了，不支持直接加not，我们这里要用到exists和 !exists

SELECT JSON_QUERY(JDATA, '$') FROM MY_JSON_TABLE  WHERE JSON_EXISTS(JDATA, '$[*]?(!exists (@.name? (@ like "龙%")))');

在列表中-in与not in

通过前面几个案例，后的我们都可以举一反三了

SELECT * FROM MY_JSON_TABLE   WHERE JSON_EXISTS(JDATA, '$[*]?(@.age in (18,19))');

not in 也一样，不支持直接加not ，这里利用 !exists

SELECT JSON_QUERY(JDATA, '$') FROM MY_JSON_TABLE  WHERE JSON_EXISTS(JDATA, '$[*]?(!exists (@.age?(@ in ("10","18","20"))))');

空字符串

SELECT  * FROM MY_JSON_TABLE WHERE JSON_EXISTS(JDATA, '$[*]?(@.middle == "")' );

null与not null

这里的查询条件更像是java代码中的判断，与以往的 is null 不同

SELECT  * FROM MY_JSON_TABLE WHERE JSON_EXISTS(JDATA, '$?(@.age == null)' );

SELECT  * FROM MY_JSON_TABLE WHERE JSON_EXISTS(JDATA, '$?(@.age != null)' );

范围比较

大于

SELECT  * FROM MY_JSON_TABLE WHERE JSON_EXISTS(JDATA, '$?(@.age >18)' );

小于

SELECT  * FROM MY_JSON_TABLE WHERE JSON_EXISTS(JDATA, '$?(@.age < 18)' );

条件组合，需要用到&& 和 ||，这里与java中的符号一样，与通用sql区别。另外，BETWEEN...AND这里用不了(或者说我没找对方法)

SELECT  * FROM MY_JSON_TABLE WHERE JSON_EXISTS(JDATA, '$?(@.age < 30 && @.age > 18 )' ) ;

组合查询

用&& 或 ||连接，&&与逻辑，||表示或逻辑。

SELECT * FROM MY_JSON_TABLE WHERE JSON_EXISTS(JDATA, '$[*]?(@.age in (10, 15,20,28)  && @.name like "%傲_")') ;

复杂查询

准备模拟数据

{   
	"name":"龙傲天",    
	"age":10,  
    "phone":[ "15088888888", "13966666666","0571-88888888"],
    "id":"333333199911114567",
    "eamil":"113@126.com",
    "work":[
        {
            "company":"快乐无限",
            "post":"销售经理",
            "address":"杭州一号大街"
        },
        {
            "company":"高兴上班",
            "post":"售前总监",
            "address":"上海"
        },
        {
            "company":"没班上",
            "post":"务农",
            "address":"浙江杭州富阳"
        }
    ]
}

sql说明：查询work数组中，第二个元素对象address包含上海的数据。[1]表示取第二个数据，下标从0开始。如果不区分下标可以是[*]

 SELECT * FROM MY_JSON_TABLE WHERE JSON_EXISTS(JDATA, '$[*]?(@.age in (10, 15,20,28)  && @.name like "%傲_" && @.work[1].address like "%上海%")') ;

如果不区分数组的元素，以下两个sql同等效果

SELECT * FROM MY_JSON_TABLE WHERE JSON_EXISTS(JDATA, '$[*]?(@.age in (10, 15,20,28)  && @.name like "%傲_" && @.work[*].address like "%上海%")') ;


SELECT * FROM MY_JSON_TABLE WHERE JSON_EXISTS(JDATA, '$[*]?(@.age in (10, 15,20,28)  && @.name like "%傲_" && @.work.address like "%上海%")') ;

我们还可以用exists关键字，注意这里exists不能大写，否则会报错

	SELECT JDATA FROM MY_JSON_TABLE where JSON_EXISTS(JDATA, '$?(@.age == 10 && exists (@.work[*]?(@.address == "上海")) )') ;

当然如果只是单纯的查询work第2个元素中address包含上海的，还可以这样

SELECT * FROM MY_JSON_TABLE WHERE JSON_EXISTS(JDATA, '$.work[1]?(@.address like "%上海%")') ;

    1. JSON_TEXTCONTAINS

使用JSON_TEXTCONTAINS需要先创建索引

CREATE INDEX ix_json_data
  ON MY_JSON_TABLE(JDATA)
  INDEXTYPE IS CTXSYS.CONTEXT
  PARAMETERS ('SECTION GROUP CTXSYS.JSON_SECTION_GROUP SYNC (ON COMMIT)');

也可以用于查询

SELECT JSON_QUERY(JDATA ,'$') FROM MY_JSON_TABLE  WHERE JSON_TEXTCONTAINS(JDATA, '$.name', '龙傲天')  AND ROWNUM < 10;

能查询数组对象里面的字段

SELECT JSON_QUERY(JDATA ,'$') FROM MY_JSON_TABLE  WHERE JSON_TEXTCONTAINS(JDATA, '$.work', '杭州一号大街')  AND ROWNUM < 10;

能查询按空格分割的数据

SELECT JSON_QUERY(JDATA ,'$') FROM MY_JSON_TABLE  WHERE JSON_TEXTCONTAINS(JDATA, '$.work', '杭州一号')  AND ROWNUM < 10;

    1. JSON_TABLE

JSON_TABLE可以将json中的字段转换成普通数据库字段，然后作为条件查询

这里只做演示，详细讲解可以参考其他章节

演示1

查询age=10的数据

SELECT JDATA FROM MY_JSON_TABLE, JSON_TABLE(JDATA, '$' COLUMNS ("MY_AGE" NUMBER PATH '$.age')) AS t WHERE t.MY_AGE = 10;

演示2

查询存在age字段的数据，

SELECT HAS_AGE
  FROM MY_JSON_TABLE,
       JSON_TABLE(JDATA, '$' 
			 COLUMNS ("HAS_AGE" NUMBER EXISTS PATH '$.age')) AS jt
  WHERE jt.HAS_AGE = 1;

演示3

查询age=10，name='龙傲天'的数据

	SELECT JDATA 
	FROM MY_JSON_TABLE, JSON_TABLE(JDATA, '$' 
			COLUMNS (
					"MY_AGE" NUMBER PATH '$.age',
					"NAME" VARCHAR2 PATH '$.name'
			 )) AS t WHERE t.MY_AGE = 10 and t.NAME = '龙傲天';

也允许单独使用，但是实际意义不大。我们可以看下

SELECT *
FROM JSON_TABLE(
         '{"id": 100, "name": "John", "age": 25, "email": "john@example.com"}',
         '$'
         COLUMNS (
            id    NUMBER    PATH '$.id',
            name  VARCHAR2(50) PATH '$.name',
            age   NUMBER    PATH '$.age',
            email VARCHAR2(100) PATH '$.email'
         )
      );

复杂结构，可以使用NESTED PATH嵌套

select ID,t.* from MY_JSON_TABLE
, JSON_TABLE(
   JDATA, '$'
	COLUMNS (
		"NAME" VARCHAR2 PATH '$.name',
		"AGE" NUMBER PATH '$.age',
		 NESTED PATH '$.work[*]'
			COLUMNS (
				"COMPANY" VARCHAR2(32) PATH '$.company',
              "ADDRESS" VARCHAR2(20) PATH '$.address'
			)
   )
) AS t WHERE t.AGE = 18 AND t.NAME = '龙傲天' AND rownum < 10;

    1. JSON_ARRAY 

以下示例从JSON对象、JSON数组、数字文字、文本文字和null构造JSON数组：

VARCHAR2 指定大小为字节数或字符数。 默认是字节。如果省略了这个子句，或者在没有指定 size 值的情况下指定了这个子句，那么 JSON_ARRAY 将返回一个 VARCHAR2(4000) 类型的字符串。

CLOB 返回一个包含单字节或多字节字符的字符大对象。

BLOB 返回一个二进制大对象的 AL32UTF8 字符集。

SELECT JSON_ARRAY (     
    JSON_OBJECT('percentage' VALUE .50),
    JSON_ARRAY(1,2,3),
    100,
    'California',
    null
    NULL ON NULL
    ) "JSON Array Example"
  FROM DUAL;

可以添加STRICT关键字

SELECT JSON_ARRAY (     
    JSON_OBJECT('percentage' VALUE .50),
    JSON_ARRAY(1,2,3),
    100,
    'California',
    null
    NULL ON NULL
   STRICT ) "JSON Array Example"
  FROM DUAL;

关键ABSENT ON NULL（忽略null） 或者 NULL ON NULL (保留null)，前面已经保留了null，我们再来看下忽略的情况

SELECT JSON_ARRAY (     
    JSON_OBJECT('percentage' VALUE .50),
    JSON_ARRAY(1,2,3),
    100,
    'California',
    null ABSENT ON NULL
    ) "JSON Array Example"
  FROM DUAL;

    1. JSON_ARRAYAGG

SELECT
	JSON_ARRAYAGG ( ID ORDER BY ID  RETURNING VARCHAR2(100)  ) ID   
FROM
	MY_JSON_TABLE 
WHERE
	ROWNUM < 10;

添加STRICT关键字，验证 JSON 生成函数的输出是正确的 JSON 。如果检查失败，将引发语法错误。

SELECT
	JSON_ARRAYAGG ( ID ORDER BY ID  RETURNING VARCHAR2(100) STRICT) ID   
FROM
	MY_JSON_TABLE 
WHERE
	ROWNUM < 10;

    1. JSON_DATAGUIDE

聚合函数JSON_DATAGUIDE接受JSON数据的表列作为输入，并将数据指南作为CLOB返回。列中的每一行都被称为一个JSON文档。对于列中的每个JSON文档，该函数返回一个CLOB值，其中包含该JSON文档的平面数据指南。

意思就是返回一个JSON文档的说明，包括json path, 类型，长度。

SELECT   
		id, JSON_DATAGUIDE(JDATA)  DATAGUIDE 
FROM  MY_JSON_TABLE where  rownum < 2 group by id

    1. JSON_QUERY

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

这个函数有一个神奇的用处，如果存储JSON字符串的的字段是CLOB或者BLOB类型，查询往往比较耗时。此时如果使用JSON_QUERY，可以减少消耗。

SELECT JSON_QUERY(JDATA, '$')  FROM MY_JSON_TABLE where ROWNUM < 100;

只返回100条，都已经不是一个量级的了

WRAPPER关键字，可以将结果包裹成数组，不管本身结果是不是数组都会包裹。

SELECT JSON_QUERY('{a:100, b:200, c:300}', '$.a' WITH WRAPPER) AS value  FROM DUAL;

结果包裹成数组

SELECT JSON_QUERY('{a:100, b:200, c:300}', '$.*' WITH WRAPPER) AS value FROM DUAL;

获取数组中的全部元素，并包裹成数组，下面两个语句是等价的

SELECT JSON_QUERY('[0,1,2,3,4]', '$' ) AS value FROM DUAL;


SELECT JSON_QUERY('[0,1,2,3,4]', '$[*]' WITH WRAPPER) AS value FROM DUAL;

可以取数组中，几个元素

SELECT JSON_QUERY('[0,1,2,3,4,5,6,7,8]', '$[0, 3 to 5, 7]' WITH WRAPPER) AS value FROM DUAL;

CONDITIONAL关键字，可以返回一个严格的JSON格式，我们可以看到字段被加上了双引号

SELECT JSON_QUERY('[{a:100},{b:200},{c:300}]', '$[*]' WITH CONDITIONAL WRAPPER) AS value FROM DUAL;

RETURNING   VARCHAR2(100)，可以限制返回的JSON串长度，如果超过了长度会返回null

SELECT JSON_QUERY('[{"a":100},{"b":200},{"c":300}]', '$[*]' RETURNING VARCHAR2(100) WITH CONDITIONAL WRAPPER) AS value FROM DUAL;

其他关键字

ON ERROR

  • NULL ON ERROR - 默认缺省.如果异常则返回null
  • ERROR ON ERROR - 如果异常就提示异常
  • EMPTY ON ERROR - 如果异常就返回一个空数组，与 EMPTY ARRAY ON ERROR相同.
  • EMPTY ARRAY ON ERROR - 如果异常就返回一个空数组
  • EMPTY OBJECT ON ERROR - 如果异常就返回一个空对象

ON EMPTY

  • NULL ON EMPTY - 匹配不到就返回null
  • ERROR ON EMPTY - 匹配不到就提示异常
  • EMPTY ON EMPTY - 匹配不到就返回空数组，参考 EMPTY ARRAY ON EMPTY.
  • EMPTY ARRAY ON EMPTY - 匹配不到就返回空数组
  • EMPTY OBJECT ON EMPTY - 匹配不到就返回一个空对象

可以组合使用

SELECT JSON_QUERY('[{"a":100},{"b":200},{"c":300}]', '$[3]' EMPTY ON ERROR NULL ON EMPTY) AS value FROM DUAL;

    1. JSON_MERGEPATCH

JSON_MERGEPATCH可以用于更新json字符串，也可以增加字段，删除字段，这样引申出可以修改字段名称。

如果A字段存在则修改值为对应值，如果不存在则添加A字段，可以多个字段一起修改，如果A字段设置的值为null，则会把A字段删除

修改数据

UPDATE MY_JSON_TABLE set JDATA = JSON_MERGEPATCH(JDATA, '{"name":"聂风"}') where ID = 14;
select JSON_QUERY(JDATA, '$') from MY_JSON_TABLE where  ID = 14;

可以多个字段一起修改

UPDATE MY_JSON_TABLE set JDATA = JSON_MERGEPATCH(JDATA, '{"name":"聂风","age":55}') where ID = 14;
select JSON_QUERY(JDATA, '$') from MY_JSON_TABLE where  ID = 14;

新增字段

只要字段不存在就自动加上了，如ID

UPDATE MY_JSON_TABLE set JDATA = JSON_MERGEPATCH(JDATA, '{"id":"1","age":55}') where ID = 14;
select JSON_QUERY(JDATA, '$') from MY_JSON_TABLE where  ID = 14;

删除字段

UPDATE MY_JSON_TABLE set JDATA = JSON_MERGEPATCH(JDATA, '{"id":null}') where ID = 14;
select JSON_QUERY(JDATA, '$') from MY_JSON_TABLE where  ID = 14;

这里需要讲到一个妙用，如果返回的结果中不需要某些字段，则可以使用此方法

SELECT id, JSON_MERGEPATCH(JDATA, '{"name":null}' ) A from MY_JSON_TABLE where ROWNUM < 100;

修改字段

需要注意的是，这里只是利用了删除和新增的方式。并不适合大量修改，因为值需要额外输入。后面会讲JSON_TRANSFORM

UPDATE MY_JSON_TABLE set JDATA = JSON_MERGEPATCH(JDATA, '{"id":null,"newId":"124"}') where ID = 14;
select JSON_QUERY(JDATA, '$') from MY_JSON_TABLE where  ID = 14;

    1. JSON_SERIALIZE

可以序列化json串，如果是用blob类型存储的，还能将二进制数据转换成字符显示出来。

先看下blob类型的数据

插入模拟数据

INSERT INTO "MY_JSON" ("id", "JDATA") VALUES ('1', UTL_RAW.CAST_TO_RAW('{"code":"11","name":"测试"}'));


select JSON_SERIALIZE(JDATA) from MY_JSON;

可以更很多关键字，

RETURNING VARCHAR2(100)限制返回的字符串长度

PRETTY 可以格式化sql

TRUNCATE 可以截取，默认缺省，配合RETURNING使用

ERROR ON ERROR json非法时提示错误，缺省默认，且不支持EMPTY ON ERROR

select JSON_SERIALIZE(JDATA RETURNING VARCHAR2(100) PRETTY TRUNCATE ERROR ON ERROR) from MY_JSON;

    1. JSON_TRANSFORM

操作

说明

REMOVE

删除字段及值

INSERT

添加字段及值

REPLACE

替换字段及值

APPEND

数组中追加数据

SET

设置字段值

RENAME

重命名字段名

KEEP

保留字段，没有被保留的字段及值会被清掉。（21C好像可以跟其他关键字)

REMOVE

UPDATE MY_JSON_TABLE SET JDATA = JSON_TRANSFORM(JDATA, REMOVE '$.name' ) where ID = 88;

IGNORE ON MISSING (default), ERROR ON MISSING

默认字段不存在不会报错，如果需要提示错误则需要加ERROR ON MISSING

UPDATE MY_JSON_TABLE SET JDATA = JSON_TRANSFORM(JDATA, REMOVE '$.name' ERROR ON MISSING ) where ID = 88;

remove还可以移除数组中的数据，配合条件使用

UPDATE MY_JSON_TABLE SET JDATA = JSON_TRANSFORM(JDATA, REMOVE '$.work[0]' ) where ID = 88;
UPDATE MY_JSON_TABLE SET JDATA = JSON_TRANSFORM(JDATA, REMOVE '$.work[*]?(@.address like "%浙江%")' ) where ID = 88;

INSERT

UPDATE MY_JSON_TABLE SET JDATA = JSON_TRANSFORM(JDATA, INSERT '$.age' =  30 ) where ID = 88;

如果字段存在了，就会提示错误，可以加上IGNORE ON EXISTING

UPDATE MY_JSON_TABLE SET JDATA = JSON_TRANSFORM(JDATA, INSERT '$.age' =  30 IGNORE ON EXISTING) where ID = 88;

支持以下操作

ERROR ON NULL会提示错误

IGNORE ON NULL 会忽略本次插入，不会动原数据

REMOVE ON NULL会把字段删掉

UPDATE MY_JSON_TABLE SET JDATA = JSON_TRANSFORM(JDATA, INSERT '$.age' =  null REPLACE ON EXISTING REMOVE ON NULL) where ID = 88;

往数组中添加一个数据，与APPEND功能相同

UPDATE MY_JSON_TABLE SET JDATA = JSON_TRANSFORM(JDATA,  INSERT '$.work[last+1]' = '{"company":"快乐无限","post":"销售经理","address":"浙江杭州一号大街"}' FORMAT JSON  ) where ID = 88;

那么其实也可以往第一个元素插入

UPDATE MY_JSON_TABLE SET JDATA = JSON_TRANSFORM(JDATA,  INSERT '$.work[0]' = '{"company":"快乐无限","post":"销售经理","address":"测试地址"}' FORMAT JSON  ) where ID = 88;

REPLACE

目前测试的基本和SET一致，详细的可以看SET

下述中FORMAT JSON关键字把前面的json串当JSON对象处理，否则会当成字符串

UPDATE MY_JSON_TABLE SET JDATA = JSON_TRANSFORM(JDATA,  REPLACE '$.address' = '{"street":"8 Timbly Rd.", "city":"Penobsky", "state":"UT"}' FORMAT JSON ) where ID = 88;

插入结果为

{
  "phone" :
  [
    "15088888888",
    "13966666666",
    "0571-88888888"
  ],
  "id" : "333333199911114567",
  "eamil" : "113@126.com",
  "work" :
  [
  ],
  "name" : "风云",
  "address" :
  {
    "street" : "8 Timbly Rd.",
    "city" : "Penobsky",
    "state" : "UT"
  }
}

APPEND

UPDATE MY_JSON_TABLE SET JDATA = JSON_TRANSFORM(JDATA,  APPEND '$.work' = '{"company":"新公司","post":"新职位","address":"杭州"}'  FORMAT JSON) where ID = 100;


{
  "name" : "龙傲天",
  "age" : 18,
  "phone" :
  [
    "15088888888",
    "13966666666",
    "0571-88888888"
  ],
  "id" : "333333199911114567",
  "eamil" : "113@126.com",
  "work" :
  [
    {
      "company" : "快乐无限",
      "post" : "销售经理",
      "address" : "杭州一号大街"
    },
    {
      "company" : "高兴上班",
      "post" : "售前总监",
      "address" : "浙江杭州滨江"
    },
    {
      "company" : "没班上",
      "post" : "务农",
      "address" : "浙江杭州富阳"
    },
    {
      "company" : "新公司",
      "post" : "新职位",
      "address" : "杭州"
    }
  ]
}

SET

修改字段值

UPDATE MY_JSON_TABLE SET JDATA = JSON_TRANSFORM(JDATA, SET '$.age' =  6 REPLACE ON EXISTING REMOVE ON NULL) where ID = 88;

如果值是一个JSON对象，则需要添加FORMAT JSON

UPDATE MY_JSON_TABLE SET JDATA = JSON_TRANSFORM(JDATA,  SET '$.address' = '{"street":"8 Timbly Rd.", "city":"Penobsky", "state":"UT"}'  FORMAT JSON) where ID = 88;

SET与REPLACE的区别在于特殊情况的处理

来点高级的，可以用筛选，主要还是针对数组

UPDATE MY_JSON_TABLE SET JDATA = JSON_TRANSFORM(JDATA,  SET '$.work[*]?(@.address == "杭州一号大街")' = '{"company":"快乐无限","post":"销售经理","address":"浙江杭州一号大街"}' FORMAT JSON  ) where ID = 100;

原条件如果是数组，则只需要@表示

UPDATE MY_JSON_TABLE SET JDATA = JSON_TRANSFORM(JDATA,  SET '$.work[*]?(@ == "浙江杭州一号大街")' = '{"company":"快乐无限","post":"销售经理","address":"浙江杭州一号大街"}' FORMAT JSON  ) where ID = 88;

RENAME

UPDATE MY_JSON_TABLE set JDATA = JSON_TRANSFORM(JDATA,  RENAME '$.name' ='NAME') where id = 100;

修改字段名且，保留原来的数据。如果被修改的字段名不存在，缺省情况下不会报错,可以加关键字。

再来点复杂的,把符合条件的company字段改成COMPANY字段

UPDATE MY_JSON_TABLE set JDATA = JSON_TRANSFORM(JDATA,  RENAME '$.work[*]?(@.address == "测试地址").company' ='COMPANY') where id = 88;

KEEP

只保留符合条件的字段，其他字段都清除

UPDATE MY_JSON_TABLE set JDATA = JSON_TRANSFORM(JDATA,  KEEP '$.work[*].company' ) where id = 188;



UPDATE MY_JSON_TABLE set JDATA = JSON_TRANSFORM(JDATA,  KEEP '$.work[*]?(@.company == "快乐无限")' ) where id = 188;

    1. JSON_VALUE

select JSON_SERIALIZE(JSON_QUERY(JDATA, '$') PRETTY) from MY_JSON_TABLE where id <100 and  JSON_VALUE(JDATA, '$.name') like '%龙%';

对查询结果处理

select JSON_VALUE(JDATA, '$.name') as NAME, JSON_VALUE(JDATA, '$.age') as AGE from MY_JSON_TABLE where id <100 and  JSON_VALUE(JDATA, '$.name') like '%龙%';

取数组第一个元素的字段

select JSON_VALUE(JDATA, '$.name') as NAME, JSON_VALUE(JDATA, '$.age') as AGE, JSON_VALUE(JDATA, '$.work[0].company') as COMPANY from MY_JSON_TABLE where id <100 and  JSON_VALUE(JDATA, '$.work[0].company') like '%无限%';

再来几个官方的例子

SELECT JSON_VALUE('{a:100}', '$.a' RETURNING NUMBER) AS value  FROM DUAL;
SELECT JSON_VALUE('{a:{b:100}}', '$.a.b') AS value FROM DUAL;
SELECT JSON_VALUE('{a:{b:100}, c:{d:200}, e:{f:300}}', '$.*.d') AS value FROM DUAL;
SELECT JSON_VALUE('[0, 1, 2, 3]', '$[0]') AS value FROM DUAL;
SELECT JSON_VALUE('{a:[5, 10, 15, 20]}', '$.a[2]') AS value FROM DUAL;
SELECT JSON_VALUE('[{a:100}, {a:200}, {a:300}]', '$[1].a') AS value FROM DUAL;
SELECT JSON_VALUE('{firstname:"John"}', '$.lastname' DEFAULT 'No last name found' ON ERROR) AS "Last Name" FROM DUAL;

SELECT JSON_VALUE('{firstname:"John","lastname":""}', '$.lastname' 
					DEFAULT ' Last name is empty' ON EMPTY
					DEFAULT 'No last name found' ON ERROR) AS "Last Name"
FROM DUAL;

Oracle Database 19c 中的 JSON_OBJECT 函数的增强功能
congbaifu8214的博客
08-20 4590
本文讲述Oracle Database 19c 中的 JSON_OBJECT 函数的增强功能。 1、初始化 本文中的示例使用 SCOTT模式中的 DEPT表，如下： --DROPTABLEDEPT...
Oracle Database Release 19c新功能
10-31
4. **JSON增强**：Oracle 19c增强了对JSON的支持，包括JSON索引、JSON路径语言（JSONPath）和JSON数据类型的操作。这对于构建RESTful服务和处理JSON格式数据的Java应用来说，提供了更强大的数据库后端支持。 5. **...
Oracle中引入的JSON函数.pdf
09-27
Oracle Database 12c 第 2 版（12.2）中引⼊的 SQL / JSON 函数的基本⽰例。
强烈推荐 | 使用Oracle 19C 存储JSON格式数据
db_murphy的博客
08-12 9091
JSON就是一种数据格式；比如有一个txt文本文件，用来存放一个班级的成绩；规定此文本文件里的学生成绩的格式，是第一行，就是一行列头（姓名 班级 年级 科目 成绩），接下来，每一行就是一个学生的成绩。 这个文本文件内的信息存放格式，其实就是一种数据格式。 学生 班级 年级 科目 成绩 张三 一班 大一 高数 90 李四 二班 大一 高数 80 对应到JSON，它其实也是代表了一种数据格式，所谓数据格式，就是数据组织的形式。 比如说，刚才所说的学生成绩，用JSON格式来表示的话，如下:[{“学生”:
oracle json笔记
最新发布
enthan809882的博客
04-08 721
oracle json笔记
c oracle json,Oracle Database 19c 中的 JSON_OBJECT 函数的增强功能
weixin_42388680的博客
04-04 645
本文讲述Oracle Database 19c 中的 JSON_OBJECT 函数的增强功能。1、初始化本文中的示例使用SCOTT模式中的DEPT表，如下：--DROPTABLEDEPTPURGE;CREATETABLEDEPT(DEPTNONUMBER(2)CONSTRAINTPK_DEPTPRIMARYKEY,DNAMEVARCHAR2(14),LOCVARCHAR...
Oracle对JSON处理函数Jar包
10-10
http://blog.youkuaiyun.com/loveyou388i/article/details/78176431
Oracle 处理json数据
只是甲的博客
11-07 7008
Oracle 19C json 数据处理
oracle格式化查看json数据
05-25
在Oracle 19c中，引入了JSON_FORMAT_MASK，这是一个用于格式化JSON数据的选项。它可以用于控制日期、时间、数字和其他类型的显示格式，使得在查询结果中查看JSON数据更加整洁和易读。 5. PL/SQL包DBMS_JSON： ...
Oracle 19c JSON开发者指南：深入探索数据库新特性
Oracle Database 19c JSON Developer's Guide 是一份详细的文档，专为在 Oracle 数据库 19c 版本中进行 JSON 开发的开发者提供指导。该文档由 Drew Adams 主导编写，同时得到了 Oracle JSON 开发、产品管理和质量...
ORACLE19c中JSON数据的使用.pdf
10-09
在 Oracle 19c 中，可以使用 JSON 列在表中，并提供了丰富的函数来操作 JSON 数据。 使用 JSON 列 在 Oracle 19c 中，可以使用 VARCHAR2、CLOB、BLOB 类型来存放 JSON 数据，并设置 CHECK 约束（IS JSON），来确保...
Oracle JSON 字符串处理实例
03-03
Oracle JSON 字符串处理实例
Oracle解析复杂json的方法实例详解
09-09
主要介绍了Oracle解析复杂json的方法,本文给大家带来两种方法解决此问题，需要的朋友可以参考下
Oracle JSON 函数详解与实战
编码行者的博客
05-22 5272
Oracle 数据库提供了丰富的 JSON 函数集，使得开发者可以高效地处理 JSON 数据。本文将详细介绍这些函数，包括它们的语法、使用场景、具体示例，以及在实际项目中的应用。
Oracle Json Functions
weixin_45500438的博客
06-20 201
【代码】Oracle Json Functions。
oracle中对JSON数据处理
热门推荐
InterestAndFun的博客
03-13 1万+
在公司接手的项目碰到这么一个表，里面有一列数据保存的是JSON字符串。如果说这一列是一些配置数据，不需要应用到筛选和过滤，只是跟着主键去获得的数据，那你这么做并没有什么不妥。但事实是，这一列需要检索和过滤数据，这**居然是遍历全表，再把json数据转换成对象去遍历匹配。我服了，你咋这么能呢？喜欢这样玩为什么当初不用MongoDB？
Oracle ----JSON_VALUE函数
weixin_42594449的博客
11-05 2892
JSON_VALUE 函数语法 JSON_VALUE ( json_expression , path [ , return_type ] )假设你有一个存储 JSON 数据的表格 T_E2E_BU_WF_INFO，字段 BUS_CODE 存储了如下 JSON 字符串： {“lstmId”:“5f67c84580ca4071a70008fd99f371ef”, “orderId”:“12345”}说明：如果 JSON 数据中包含一个数组，可以使用 JSON_VALUE 来提取数组中的某个值。例如，假设 JS
oracle function json,oracle json 解析函数
weixin_35843096的博客
04-04 298
CREATE OR REPLACE TYPE ty_tbl_str_split IS TABLE OF ty_row_str_split; CREATE OR REPLACE TYPE ty_row_str_split as object (strValue VARCHAR2(4000)); CREATE OR REPLACE FUNCTION fn_split(p_str IN VARCH...
oracle function json,Oracle中引入的JSON函数.pdf
weixin_35854529的博客
04-04 262
Oracle中引入的JSON函数Oracle 中引⼊的 SQL / JSON 函数本⽂提供了 Oracle Database 12c 第 2 版(12.2)中引⼊的 SQL / JSON 函数的基本⽰例。建⽴测试表及数据本⽂中的⽰例使⽤以下表：CREATE TABLEDEPT (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARYKEY,DNAME VARCHAR...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值