DBA Hub

📋Steps in this guide1/7

JSON_MERGEPATCH in Oracle Database 19c

The JSON_MERGEPATCH function is used to modify parts of a JSON document in select and update operations.

oracle 19cconfigurationintermediate
by OracleDba
17 views
1

JSON_MERGEPATCH Basic Usage

The documentation provides the following description of the function. The target expression is the JSON we want to amend. The patch expression is a JSON fragment representing the change/patch we want to merge into the target expression. To see it in action, create and populate the following test table. Before we start, check the unmodified content of the data. If the patch expression specifies an existing element, or group of elements, the function will update those elements in the output. In the following output we set the "last_name" element to the value "banana". If the patch expression specifies an element that doesn't exist, the new element is added to the document. In the example below we've added "new_element" to each document. Setting an existing element to NULL removes it from the document. If you wish to see a blank element in the document, set it to an empty string or empty array. Using a mix of existing, new and NULL elements is fine. In the example below we remove the "first_name" element, amend the "last_name" element, and add the "new_element" element. Notice we've limited the output to a single row using dot notation.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
JSON_MERGEPATCH
   ( target_expr , patch_expr [ returning_clause ] [ PRETTY ] [ ASCII ] 
     [ TRUNCATE ] [ on_error_clause ] )

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

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>

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>

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>

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

Arrays

Arrays can be processed in a similar way to what we've already seen, but we have to deal with the whole array contents at once. We can't interact with individual elements in an array. Create a new row containing an array and display the data. In the following example we attempt to edit the third array element as if it were a regular element. A match isn't made, so a new top-level element is created. Instead, we must replace the whole array.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
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

Nested JSON Objects

Similar to arrays, we can't interact directly with individual nested elements. Create a new row containing a nested JSON object and display the data. Notice the "parent1" element has a value of a JSON object, made up of two JSON elements. This is not an array. If we try to edit the "child2" element without any reference to its parent, we get a new top-level element created. Instead we need to amend the whole top-level element, or reference the parent in the call. We can also nest calls. We are still having to replace the value of the parent object, but it could make that simpler for complicated objects. Thanks to "GlenM" and "JohnB" in the comments for pointing some alternatives out.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
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>

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>


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

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 in Updates

Before we start, let's check the data is consistent. So far the examples have all been queries, but we can modify data in a table using an update statement. In the following example we display the data in the test table, update the JSON data in one row, and display the table data again.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
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;

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

Format Output

The returning clause works like that of the other SQL/JSON functions, as described here . The keyword displays the output in a human readable form, rather than minified. The keyword indicates the output should be truncated to fit the return type. In the following example the return type is , so the output is truncated to fit. The keyword indicates the output should convert any non-ASCII characters to JSON escape sequences.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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>

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

Error Handling

If there are any failures during the processing of the data the default response is to return a NULL value. The way an error is handled can be specified explicitly with the clause.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 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 Support

There is no support for in direct PL/SQL assignments. The simple workaround for this is to make the assignment using a query from dual. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
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>

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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!