Working with JSON_MERGEPATCH

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

JSON_MERGEPATCH.jpg

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;
USERNAME JSON_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;
USERNAME JSON_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

USERNAME JSON_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
      }
    }
  }
}

Expanse amount is updated 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

USERNAME JSON_DOCUMENT
1234 {“SALES_DATA”:{“MONTH”:{“JAN”:{“AMOUNT”:7000}}}}

ERROR ON ERROR

json_error.PNG

By default JSON_MERGEPATCH function return NULL if any error occured, however we can force it to return 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 of by default behaviour.

RETURNING

json_return.PNG

By default JSON_MERGEPATCH function returns the VARCHAR2. We can force return in below four dataTypes.

  • 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

USERNAME JSON_DOCUMENT
1234 {“SAL

You might also Like:


comments powered by Disqus