Oracle 19c 中 JSON_MERGEPATCH 函数

山东Oracle用户组 2021-02-06

3202

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

1、JSON_MERGEPATCH 基本用法

JSON_MERGEPATCH
   ( target_expr , patch_expr [ returning_clause ] [ PRETTY ] [ ASCII ] 
     [ TRUNCATE ] [ on_error_clause ] )

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

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

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

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

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”。

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”。

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 会将其从文档中删除。

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>

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

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”元素。

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、数组

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

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

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>

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

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>

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

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 元素组成。这不是数组。

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” 元素，则会创建一个新的顶级元素。

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>

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

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

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 更新

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

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 数据更新为一行，然后再次显示表数据。

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 关键字以人类可读的形式而不是最小的形式显示输出。

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)，因此输出被截断以适合。

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 子句明确指定处理错误的方式。

-- 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 函数。

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 虚表查询来进行分配。

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>

oracle

文章转载自山东Oracle用户组，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle 19c 中 JSON_MERGEPATCH 函数

评论