8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 19c » Here

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.

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

Hope this helps. Regards Tim...

Back to the Top.