Overview
This document is about how to merge JSON nodes with existing JSON data in oracle.
Technologies and Tools Used
The following technologies have been used to merge JSON nodes with existing JSON data in oracle.
- Oracle SQL/ PLSQL
Use Case
Let us have the requirement on how to merge JSON nodes with existing JSON data in oracle.
Steps with Screenshot
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
Code :
CREATE TABLE Table_json (
username NUMBER,
json_document VARCHAR2(4000),
CONSTRAINT json_document_ck CHECK ( json_document IS JSON )
);
INSERT INTO Table_json (
username,
json_document
) VALUES (
1234,
‘{“SALES_DATA” : {“MONTH”: {“JAN”: { “AMOUNT”: 4000}} }}’
);
COMMIT;
SELECT
tj.username,tj.json_document
FROM
Table_json tj
WHERE
username = 1234;
/
Output:
| USERNAME | JSON_DOCUMENT |
| 1234 | {“SALES_DATA” : {“MONTH”: {“JAN”: { “AMOUNT”: 4000}} }} |
Merging Data
Merge Expanse data with sales data.
Code:
SELECT
tj.username, json_mergepatch(tj.json_document, ‘{“EXPANSE_DATA”: {“MONTH” : { “JAN” : { “AMOUNT” : 2000}}}}’)
FROM
table_json tj
WHERE
username = 1234;
/
Output:
| USERNAME | JSON_DOCUMENT |
| 1234 | {“SALES_DATA”:{“MONTH”:{“JAN”:{“AMOUNT”:4000}}},“EXPANSE_DATA”:{“MONTH”:{“JAN”:{“AMOUNT”:2000}}}} |
Code:
SELECT
json_mergepatch(tj.json_document, ‘{“SALES_DATA”: {“MONTH” : { “FEB” : { “AMOUNT” : 2000}}}}’)
FROM
Table_json tj
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:
Code:
SELECT tj.username,
json_mergepatch(tj.json_document.SALES_DATA.MONTH.JAN.AMOUNT, ‘7000’)
FROM
table_json tj
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 tj.username,
json_mergepatch(tj.json_document.SALES_DATA.MONTH.JAN, ‘{“AMOUNT”: 7000}’) json_document
FROM
Table_json tj
WHERE
username = 1234;
/
Output:
| USERNAME | JSON_DOCUMENT |
| 1234 | {“AMOUNT”:7000} |
2. Example 2
SELECT tj.username,
json_mergepatch(tj.json_document, ‘{“EXPANSE_DATA”: {“MONTH” : { “JAN” : { “AMOUNT” : 2000}}}}’ )
FROM
Table_json tj
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.
Code :
SELECT tj.username,
json_mergepatch(tj.json_document, ‘{“SALES_DATA” : {“MONTH”: {“JAN”: { “AMOUNT”: 5000}} }}’ )
FROM
Table_json tj
WHERE
username = 1234;
/
Output :
{
"SALES_DATA" :
{
"MONTH" :
{
"JAN" :
{
"AMOUNT" : 5000
}
}
}
}
The expanse amount is updated from 4000 to 5000.
Update JSON Document in Table:
Code:
UPDATE table_json tj
SET tj.json_document = json_mergepatch(tj.json_document, ‘{“SALES_DATA” : {“MONTH”: {“JAN”: { “AMOUNT”: 7000}} }}’)
WHERE
username = 1234;
/
SELECT
tj.username,
tj.json_document
FROM
Table_json tj
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.
Code:
SELECT
tj.username, json_mergepatch(tj.json_document,
‘{“SALES_DATA” : {“MONTH”: {“JAN”: { “AMOUNT”: 4000}} }}’ RETURNING VARCHAR2(5) ERROR ON ERROR)
FROM
Table_json tj
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 ERROR ON ERROR
Code:
SELECT
tj.username,
json_mergepatch(tj.json_document,
‘{“SALES_DATA” : {“MONTH”: {“JAN”: { “AMOUNT”: 4000}} }}’
RETURNING VARCHAR2(5) TRUNCATE ERROR ON ERROR) JSON_DOCUMENT
FROM
table_json tj
WHERE
username = 1234;
Output :
| USERNAME | JSON_DOCUMENT |
| 1234 | {“SAL |
