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.
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
}
}
}
}
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
| USERNAME | JSON_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
| USERNAME | JSON_DOCUMENT |
|---|---|
| 1234 | {“SAL |
