Oracle 19c 中 JSON_MERGEPATCH 函数

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>

json 编程算法 sql ascii

0 人点赞