◉ 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, 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.
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>
We can simplify this operation using JSON_MERGEPATCH to amend the child elements, and return the amended object as the value for the parent element. This is effectively nesting the 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" in the comments for pointing this out. I hadn't thought of it.
◉ 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.
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>
Posts
0 comments:
Post a Comment