Working with JSON_MERGEPATCH

December 15, 2022Posted inAPEX, ORACLE

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

Copy Code
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.

Copy Code
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}}}}
Copy Code
SELECT oj.username,
    json_mergepatch(oj.json_document, '{"SALES_DATA": {"MONTH" : { "FEB" : { "AMOUNT" : 2000}}}}')
FROM
    ontoor_json oj
WHERE
    username = 1234;


Output

Copy Code
{
  "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

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


Output

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

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

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


Output

Copy Code
{
  "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.

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


Output

Copy Code
{
  "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

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


Output

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


Update JSON Document in Table

Copy Code
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.

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

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










Copy Code
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:














	Tags: