8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
JSON_MERGEPATCH in Oracle Database 19c
The JSON_MERGEPATCH
function is used to modify parts of a JSON document in select and update operations. In previous releases, you had to retrieve the JSON document, process the contents and replace the whole document with the modified document. With the JSON_MERGEPATCH
function you can simplify the process significantly for some scenarios.
- JSON_MERGEPATCH Basic Usage
- Arrays
- Nested JSON Objects
- JSON_MERGEPATCH in Updates
- Format Output
- Error Handling
- PL/SQL Support
Related articles.
- JSON_MERGEPATCH Function in 19c
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23c, All Articles
JSON_MERGEPATCH Basic Usage
The documentation provides the following description of the JSON_MERGEPATCH
function.
JSON_MERGEPATCH ( target_expr , patch_expr [ returning_clause ] [ PRETTY ] [ ASCII ] [ TRUNCATE ] [ on_error_clause ] )
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.
-- 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;
Before we start, check the unmodified content of the data.
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>
If the patch expression specifies an existing element, or group of elements, the JSON_MERGEPATCH
function will update those elements in the output. In the following output we set the "last_name" element to the value "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>
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.
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>
Setting an existing element to NULL removes it from the document.
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>
If you wish to see a blank element in the document, set it to an empty string or empty array.
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>
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.
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>
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.
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>
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.
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>
Instead, we must replace the whole array.
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>
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.
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>
If we try to edit the "child2" element without any reference to its parent, we get a new top-level element created.
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>
Instead we need to amend the whole top-level element, or reference the parent in the call.
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>
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.
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>
Thanks to "GlenM" and "JohnB" in the comments for pointing some alternatives out.
JSON_MERGEPATCH in Updates
Before we start, let's check the data is consistent.
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;
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.
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;
Format Output
The returning clause works like that of the other SQL/JSON functions, as described here.
The PRETTY
keyword displays the output in a human readable form, rather than minified.
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>
The TRUNCATE
keyword indicates the output should be truncated to fit the return type. In the following example the return type is VARCHAR2(10)
, so the output is truncated to fit.
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>
The ASCII
keyword indicates the output should convert any non-ASCII characters to JSON escape sequences.
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 ON ERROR
clause.
-- 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>
PL/SQL Support
There is no support for JSON_MERGEPATCH
in direct PL/SQL assignments.
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>
The simple workaround for this is to make the assignment using a query from 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>
For more information see:
- Updating a JSON Document with JSON Merge Patch
- JSON_MERGEPATCH
- JSON_MERGEPATCH Function in 19c
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23c, All Articles
Hope this helps. Regards Tim...