前往小程序，Get更优阅读体验！
立即前往
文档建议反馈控制台
首页
学习
活动
专区
工具
TVP
最新优惠活动
文章/答案/技术大牛
发布
首页
学习
活动
专区
工具
TVP最新优惠活动
返回腾讯云官网
社区首页 >专栏 >Oracle 19c 中 JSON_MERGEPATCH 函数

Oracle 19c 中 JSON_MERGEPATCH 函数

作者头像
Yunjie Ge
发布于 2022-04-24 10:05:22
2.7K0
发布于 2022-04-24 10:05:22
举报
文章被收录于专栏：数据库与编程

JSON_MERGEPATCH 函数用于在查询和更新操作中修改 JSON 文档的一部分。在 Oracle 19c 以前的版本中，必须查询 JSON 文档，处理内容并将整个文档替换为修改后的文档。使用 JSON_MERGEPATCH 函数，可以在某些情况下显着简化该过程。

1、JSON_MERGEPATCH 基本用法

代码语言：javascript
复制
JSON_MERGEPATCH
   ( target_expr , patch_expr [ returning_clause ] [ PRETTY ] [ ASCII ] 
     [ TRUNCATE ] [ on_error_clause ] )

目标表达式（ target_expr ）是我们要修改的 JSON。补丁表达式（ patch_expr ）是一个 JSON 片段，表示我们要合并到目标表达式中的更改/补丁。

要查看它的实际效果，请创建并填充以下测试表。

代码语言：javascript
复制
-- DROP TABLE json_documents PURGE;

CREATE TABLE json_documents (
  id    NUMBER,
  data  VARCHAR2(4000),
  CONSTRAINT json_documents_is_json CHECK (data IS JSON)
);

INSERT INTO json_documents (id, data) VALUES (1, '{"id":1,"first_name":"Iron","last_name":"Man"}');
INSERT INTO json_documents (id, data) VALUES (2, '{"id":2,"first_name":"Wonder","last_name":"Woman"}');
INSERT INTO json_documents (id, data) VALUES (3, '{"id":3,"first_name":"The","last_name":"Hulk"}');
COMMIT;

在开始之前，请检查数据的未修改内容。

代码语言：javascript
复制
SELECT data FROM json_documents;

DATA
---------------------------------------------------------------------------------
{"id":1,"first_name":"Iron","last_name":"Man"}
{"id":2,"first_name":"Wonder","last_name":"Woman"}
{"id":3,"first_name":"The","last_name":"Hulk"}

SQL>

如果补丁表达式指定了一个现有元素或元素组，则 JSON_MERGEPATCH 函数将更新输出中的那些元素。在以下输出中，我们将“ last_name”元素设置为值“ banana”。

代码语言：javascript
复制
SELECT JSON_MERGEPATCH(data, '{"last_name":"banana"}') AS data
FROM   json_documents;

DATA
---------------------------------------------------------------------------------
{"id":1,"first_name":"Iron","last_name":"banana"}
{"id":2,"first_name":"Wonder","last_name":"banana"}
{"id":3,"first_name":"The","last_name":"banana"}

SQL>

如果补丁表达式指定了不存在的元素，则新元素将添加到文档中。在下面的示例中，我们向每个文档添加了“ new_element”。

代码语言：javascript
复制
SELECT JSON_MERGEPATCH(data, '{"new_element":"surprise"}') AS data
FROM   json_documents;

DATA
--------------------------------------------------------------------------------
{"id":1,"first_name":"Iron","last_name":"Man","new_element":"surprise"}
{"id":2,"first_name":"Wonder","last_name":"Woman","new_element":"surprise"}
{"id":3,"first_name":"The","last_name":"Hulk","new_element":"surprise"}

SQL>

将现有元素设置为 NULL 会将其从文档中删除。

代码语言：javascript
复制
SELECT JSON_MERGEPATCH(data, '{"last_name":NULL}') AS data
FROM   json_documents;

DATA
---------------------------------------------------------------------------------
{"id":1,"first_name":"Iron"}
{"id":2,"first_name":"Wonder"}
{"id":3,"first_name":"The"}

SQL>

如果希望在文档中看到空白元素，请将其设置为空字符串或空数组。

代码语言：javascript
复制
SELECT JSON_MERGEPATCH(data, '{"last_name":""}') AS data
FROM   json_documents;

DATA
---------------------------------------------------------------------------------
{"id":1,"first_name":"Iron","last_name":""}
{"id":2,"first_name":"Wonder","last_name":""}
{"id":3,"first_name":"The","last_name":""}

SQL>

可以混合使用现有的、新的和 NULL 。在下面的示例中，我们删除“ first_name”元素，修改“ last_name”元素，然后添加“ new_element”元素。

代码语言：javascript
复制
SELECT JSON_MERGEPATCH(a.data, '{"first_name":NULL, "last_name":"banana","new_element":"surprise"}') AS data
FROM   json_documents a
WHERE  a.data.first_name = 'Iron';

DATA
---------------------------------------------------------------------------------
{"id":1,"last_name":"banana","new_element":"surprise"}

SQL>

2、数组

可以按照与我们已经看到的类似的方式来处理数组，但是我们必须立即处理整个数组的内容。我们无法与数组中的各个元素进行交互。

创建一个包含数组的新行并显示数据。

代码语言：javascript
复制
INSERT INTO json_documents VALUES (4, '{"id":4,"my_array":[{"attr1":"val1"},{"attr2":"val2"},{"attr3":"val3"}]}');

SELECT data
FROM   json_documents a
WHERE  a.id = 4;

DATA
---------------------------------------------------------------------------------
{"id":4,"my_array":[{"attr1":"val1"},{"attr2":"val2"},{"attr3":"val3"}]}

SQL>

在下面的示例中，我们尝试编辑第三个数组元素，就好像它是常规元素一样。不进行匹配，因此创建了一个新的顶级元素。

代码语言：javascript
复制
SELECT JSON_MERGEPATCH(a.data, '{"attr3":"fail"}') AS data
FROM   json_documents a
WHERE  a.id = 4;

DATA
---------------------------------------------------------------------------------------
{"id":4,"my_array":[{"attr1":"val1"},{"attr2":"val2"},{"attr3":"val3"}],"attr3":"fail"}

SQL>

相反，我们必须替换整个数组。

代码语言：javascript
复制
SELECT JSON_MERGEPATCH(a.data, '{"my_array":[{"attr1":"val1"},{"attr2":"val2"},{"attr3":"success"}]}') AS data
FROM   json_documents a
WHERE  a.id = 4;

DATA
---------------------------------------------------------------------------------
{"id":4,"my_array":[{"attr1":"val1"},{"attr2":"val2"},{"attr3":"success"}]}

SQL>

3、嵌套的 JSON 对象

与数组类似，我们不能直接与单个嵌套元素进行交互。

创建一个包含嵌套 JSON 对象的新行并显示数据。请注意，“parent1”元素具有一个 JSON 对象的值，该对象由两个 JSON 元素组成。这不是数组。

代码语言：javascript
复制
INSERT INTO json_documents VALUES (5, '{"id":5,"parent1":{"child1":1, "child2":2}}');

SELECT data
FROM   json_documents a
WHERE  a.id = 5;

DATA
---------------------------------------------------------------------------------
{"id":5,"parent1":{"child1":1,"child2":2}}

SQL>

如果尝试编辑 “child2” 元素，则会创建一个新的顶级元素。

代码语言：javascript
复制
SELECT JSON_MERGEPATCH(a.data, '{"child2":99}') AS data
FROM   json_documents a
WHERE  a.id = 5;

DATA
---------------------------------------------------------------------------------
{"id":5,"parent1":{"child1":1,"child2":2},"child2":99}

SQL>

相反，我们需要修改整个顶级元素。

代码语言：javascript
复制
SELECT JSON_MERGEPATCH(a.data, '{"parent1":{"child1":1,"child2":99}}') AS data
FROM   json_documents a
WHERE  a.id = 5;

DATA
---------------------------------------------------------------------------------
{"id":5,"parent1":{"child1":1,"child2":99}}

SQL>

我们可以使用 JSON_MERGEPATCH 修改子元素，并返回修改后的对象作为父元素的值，从而简化此操作。这实际上是在嵌套调用。我们仍然必须替换父对象的值，但是对于复杂的对象，它可以使之更简单。

代码语言：javascript
复制
SELECT JSON_MERGEPATCH(a.data,'{"parent1":'|| JSON_MERGEPATCH(a.data.parent1, '{"child2":99}') ||'}') AS data
FROM   json_documents a
WHERE  a.id = 5;

DATA
--------------------------------------------------------------------------------
{"id":5,"parent1":{"child1":1,"child2":99}}

SQL>

4、使用 JSON_MERGEPATCH 更新

在开始之前，重新初始化数据。

代码语言：javascript
复制
TRUNCATE TABLE json_documents;

INSERT INTO json_documents (id, data) VALUES (1, '{"id":1,"first_name":"Iron","last_name":"Man"}');
INSERT INTO json_documents (id, data) VALUES (2, '{"id":2,"first_name":"Wonder","last_name":"Woman"}');
INSERT INTO json_documents (id, data) VALUES (3, '{"id":3,"first_name":"The","last_name":"Hulk"}');
COMMIT;

到目前为止，所有示例都是查询，但是我们可以使用 update 语句修改表中的数据。在下面的示例中，我们在测试表中显示数据，将 JSON 数据更新为一行，然后再次显示表数据。

代码语言：javascript
复制
SELECT data FROM json_documents;

DATA
---------------------------------------------------------------------------------
{"id":1,"first_name":"Iron","last_name":"Man"}
{"id":2,"first_name":"Wonder","last_name":"Woman"}
{"id":3,"first_name":"The","last_name":"Hulk"}

SQL>


UPDATE json_documents a
SET    a.data = JSON_MERGEPATCH(a.data, '{"last_name":"banana","new_element":"surprise"}')
WHERE  a.data.first_name = 'Iron';


SELECT data FROM json_documents;

DATA
---------------------------------------------------------------------------------
{"id":1,"first_name":"Iron","last_name":"banana","new_element":"surprise"}
{"id":2,"first_name":"Wonder","last_name":"Woman"}
{"id":3,"first_name":"The","last_name":"Hulk"}

SQL>

ROLLBACK;

5、格式化输出

返回子句的工作方式与其他 SQL/JSON 函数类似。

PRETTY 关键字以人类可读的形式而不是最小的形式显示输出。

代码语言：javascript
复制
SELECT JSON_MERGEPATCH(a.data, '{"last_name":"banana"}' PRETTY) AS data
FROM   json_documents a
WHERE  a.data.first_name = 'Iron';

DATA
---------------------------------------------------------------------------------
{
  "id" : 1,
  "first_name" : "Iron",
  "last_name" : "banana"
}

SQL>

TRUNCATE 关键字指示输出应被截断以适合返回类型。在下面的示例中，返回类型为 VARCHAR2(10)，因此输出被截断以适合。

代码语言：javascript
复制
SELECT JSON_MERGEPATCH(a.data, '{"last_name":"banana"}' RETURNING VARCHAR2(10) TRUNCATE) AS data
FROM   json_documents a
WHERE  a.data.first_name = 'Iron';

DATA
---------------------------------------------------------------------------------
{"id":1,"f

SQL>

ASCII 关键字指示输出应将任何非 ASCII 字符转换为 JSON 转义序列。

6、错误处理

如果在处理数据期间发生任何故障，则默认响应是返回 NULL 值。可以使用ON ERROR 子句明确指定处理错误的方式。

代码语言：javascript
复制
-- Default behaviour.
SELECT JSON_MERGEPATCH(a.data, '{}' RETURNING VARCHAR2(10) NULL ON ERROR) AS data
FROM   json_documents a
WHERE  a.data.first_name = 'Iron';

DATA
---------------------------------------------------------------------------------


SQL>


SELECT JSON_MERGEPATCH(a.data, '{}' RETURNING VARCHAR2(10) ERROR ON ERROR) AS data
FROM   json_documents a
WHERE  a.data.first_name = 'Iron';
       *
ERROR at line 2:
ORA-40478: output value too large (maximum: 10)

SQL>

7、PL/SQL 支持

不支持在 PL/SQL 中直接分配使用 JSON_MERGEPATCH 函数。

代码语言：javascript
复制
SET SERVEROUTPUT ON
DECLARE
  l_json_doc VARCHAR2(32767);
BEGIN
  l_json_doc := '{"id":1,"first_name":"Iron","last_name":"Man"}';
  DBMS_OUTPUT.put_line('Before: ' || l_json_doc);

  l_json_doc := JSON_MERGEPATCH(l_json_doc, '{"last_name":"banana"}');

  DBMS_OUTPUT.put_line('After : ' || l_json_doc);
END;
/
  l_json_doc := JSON_MERGEPATCH(l_json_doc, '{"last_name":"banana"}');
                *
ERROR at line 7:
ORA-06550: line 7, column 17:
PLS-00201: identifier 'JSON_MERGEPATCH' must be declared
ORA-06550: line 7, column 3:
PL/SQL: Statement ignored

SQL>

一个简单的解决方法是通过使用 dual 虚表查询来进行分配。

代码语言：javascript
复制
DECLARE
  l_json_doc VARCHAR2(32767);
BEGIN
  l_json_doc := '{"id":1,"first_name":"Iron","last_name":"Man"}';
  DBMS_OUTPUT.put_line('Before: ' || l_json_doc);

  SELECT JSON_MERGEPATCH(l_json_doc, '{"last_name":"banana"}')
  INTO   l_json_doc
  FROM   dual;

  DBMS_OUTPUT.put_line('After : ' || l_json_doc);
END;
/
Before: {"id":1,"first_name":"Iron","last_name":"Man"}
After : {"id":1,"first_name":"Iron","last_name":"banana"}

SQL>
本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2020-10-26，如有侵权请联系 cloudcommunity@tencent.com 删除
json
编程算法
sql
ascii

本文分享自 山东Oracle用户组 微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ，欢迎热爱写作的你一起参与！

json
编程算法
sql
ascii
评论
登录后参与评论
0 条评论
热度
最新
登录 后参与评论
推荐阅读
LV.
文章
0
获赞
0
领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2024 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档

Copyright © 2013 - 2024 Tencent Cloud.

All Rights Reserved. 腾讯云 版权所有

登录 后参与评论