Working with JSON_MERGEPATCH

JSON_MERGEPATCH function is very useful to update JSON nodes in JSON documents. We can simply pass the JSON required JSON patch to merge with the existing JSON document using the JSON_MERGEPATCH function, it can add or update the JSON node as per the availability of the node in the JSON document.

Oracle Document link

Create a table with JSON

CREATE TABLE ontoor_json (
    username      NUMBER,
    json_document  VARCHAR2(4000),
    CONSTRAINT json_document_ck CHECK ( json_document IS JSON )
);

INSERT INTO ontoor_json (
    username,
    json_document
) VALUES (
    1234,
    '{"SALES_DATA" : {"MONTH": {"JAN": { "AMOUNT": 4000}} }}'
);

COMMIT;

SELECT
    oj.json_document
FROM
    ontoor_json oj
WHERE
    username = 1234;

USERNAMEJSON_DOCUMENT
1234{“SALES_DATA” : {“MONTH”: {“JAN”: { “AMOUNT”: 4000}} }}

Merging

Merge Expanse data with sales data.

SELECT oj.username,
    json_mergepatch(oj.json_document, '{"EXPANSE_DATA": {"MONTH" : { "JAN" : { "AMOUNT" : 2000}}}}')
FROM
    ontoor_json oj
WHERE
    username = 1234;

USERNAMEJSON_DOCUMENT
1234{“SALES_DATA”:{“MONTH”:{“JAN”:{“AMOUNT”:4000}}},“EXPANSE_DATA”:{“MONTH”:{“JAN”:{“AMOUNT”:2000}}}}
SELECT oj.username,
    json_mergepatch(oj.json_document, '{"SALES_DATA": {"MONTH" : { "FEB" : { "AMOUNT" : 2000}}}}')
FROM
    ontoor_json oj
WHERE
    username = 1234;


Output

{
  "SALES_DATA" :
  {
    "MONTH" :
    {
      "JAN" :
      {
        "AMOUNT" : 4000
      },
      "FEB" :
      {
        "AMOUNT" : 2000
      }
    }
  }
}

Data for Feb is added as the feb node was not available in the document.

Let’s try directly with node update.

Assignment

SELECT oj.username,
    json_mergepatch(oj.json_document.SALES_DATA.MONTH.JAN.AMOUNT, '7000')
FROM
    ontoor_json oj
WHERE
    username = 1234;


Output

ORA-40629: Patch specification is not valid JSON
40629. 00000 -  "Patch specification is not valid JSON"
*Cause:    Patch specification was not expressed in valid JSON.
*Action:   Ensure that patch specification is expressed in valid JSON.


Patching

1. Example 1

SELECT oj.username,
    json_mergepatch(oj.json_document.SALES_DATA.MONTH.JAN, '{"AMOUNT": 7000}')
FROM
    ontoor_json oj
WHERE
    username = 1234;


Output

USERNAMEJSON_DOCUMENT
1234{“AMOUNT”:4000,“JAN”:7000}

2. Example 2

SELECT oj.username,
    json_mergepatch(oj.json_document, 
					'{"EXPANSE_DATA": {"MONTH" : { "JAN" : { "AMOUNT" : 2000}}}}' )
FROM
    ontoor_json oj
WHERE
    username = 1234;


Output

{
  "SALES_DATA" :
  {
    "MONTH" :
    {
      "JAN" :
      {
        "AMOUNT" : 4000
      }
    }
  },
  "EXPANSE_DATA" :
  {
    "MONTH" :
    {
      "JAN" :
      {
        "AMOUNT" : 2000
      }
    }
  }
}


3. Example 3

In the above query, Expanse data is added/ merged with SALES data since it was not available in the document. Let’s try to update the existing node.

SELECT oj.username,
    json_mergepatch(oj.json_document, 
					'{"SALES_DATA" : {"MONTH": {"JAN": { "AMOUNT": 5000}} }}' )
FROM
    ontoor_json oj
WHERE
    username = 1234;


Output

{
  "SALES_DATA" :
  {
    "MONTH" :
    {
      "JAN" :
      {
        "AMOUNT" : 5000
      }
    }
  }
}


The expanse amount is updated from 4000 to 5000.

Formatting

To format JSON we can use other optional function parameters | PRETTY

SELECT oj.username,
    json_mergepatch(oj.json_document, 
					'{"EXPANSE_DATA": {"MONTH" : { "JAN" : { "AMOUNT" : 2000}}}}' PRETTY )
FROM
    ontoor_json oj
WHERE
    username = 1234;


Output

{
  "SALES_DATA" :
  {
    "MONTH" :
    {
      "JAN" :
      {
        "AMOUNT" : 4000
      }
    }
  },
  "EXPANSE_DATA" :
  {
    "MONTH" :
    {
      "JAN" :
      {
        "AMOUNT" : 2000
      }
    }
  }
}


Update JSON Document in Table

UPDATE ontoor_json oj
SET
    oj.json_document = json_mergepatch(oj.json_document,
					  '{"SALES_DATA" : {"MONTH": {"JAN": { "AMOUNT": 4000}} }}')
WHERE
    username = 1234;
/

SELECT
    oj.username,
    oj.json_document
FROM
    ontoor_json oj
WHERE
    username = 1234;


Output

USERNAMEJSON_DOCUMENT
1234{“SALES_DATA”:{“MONTH”:{“JAN”:{“AMOUNT”:7000}}}}

ERROR ON ERROR

By default JSON_MERGEPATCH function return NULL if any error occurred, however, we can force it to return an error.

SELECT
    oj.username,
    json_mergepatch(oj.json_document,
    '{"SALES_DATA" : {"MONTH": {"JAN": { "AMOUNT": 4000}} }}' RETURNING VARCHAR2(5) ERROR ON ERROR)
FROM
    ontoor_json oj
WHERE
    username = 1234;


Output

ORA-40478: output value too large (maximum: 5)
40478. 00000 -  "output value too large (maximum: %s)"
*Cause:    The provided JavaScript Object Notation (JSON) operator generated a
           result which exceeds the maximum length specified in the RETURN
           clause.
*Action:   Increase the maximum size of the data type in the RETURNING clause
           or use a CLOB or BLOB in the RETURNING clause.





We have defined the return datatype as VARCHAR2(5) and RETURNED value in JSON_MERGEPATCH is greater than 5 characters. If we don’t use ERROR on ERROR then it will return NULL as by default behavior.

RETURNING

By default, JSON_MERGEPATCH function returns the VARCHAR2. We can force return in the below four data types.

  • VARCHAR2
  • CLOB
  • BLOB
  • JSON

TRUNCATE





SELECT
    oj.username,
    json_mergepatch(oj.json_document,
    '{"SALES_DATA" : {"MONTH": {"JAN": { "AMOUNT": 4000}} }}' 
    RETURNING VARCHAR2(5) TRUNCATE ERROR ON ERROR) JSON_DOCUMENT
FROM
    ontoor_json oj
WHERE
    username = 1234;


Output

USERNAMEJSON_DOCUMENT
1234{“SAL

You might also Like: