JSON_MERGEPATCH Examples (AskTom Office hours, March 12th, 2019)

View All Scripts Login to Run Script

Script Name JSON_MERGEPATCH Examples (AskTom Office hours, March 12th, 2019)
Description Examples showing how JSON_MERGEPATCH can be used to modify JSON values. From AskTom Office hours March 12th 2019: Updates with PLSQL.
Area SQL General / JSON
Contributor Josh Spiegel (Oracle)
Created Monday March 11, 2019

Statement 1
JSON_MERGEPATCH takes two JSON objects as input and returns a new JSON object that contains entries from both objects. The first input to the function is referred to as the "target" and the second argument is referred to as the "patch".
```
SELECT JSON_MERGEPATCH('{"a":1}', '{"b":2}')
FROM dual
```
Statement 2
In this example, both the target and patch document contain an entry for key "a". The result object will keep the value from the patch ("a"=3).
```
SELECT JSON_MERGEPATCH('{"a":1}', '{"b":2, "a":3}')
FROM dual
```

Statement 3

CREATE TABLE zoo (id NUMBER PRIMARY KEY, jcol VARCHAR2(4000) CHECK (jcol IS JSON))

Statement 4

INSERT INTO zoo VALUES (1, '{"name":"Flipper", "species":"dolphin", "food":"fish"}')

Statement 5

INSERT INTO zoo VALUES (2, '{"name":"Lassie", "species":"dog", "food":"Friskies"}')

Statement 6

INSERT INTO zoo VALUES (3, '{"name":"Findus", "species":"cat", "food":["Meow","Mix"]}')

Statement 7

INSERT INTO zoo VALUES (4, '{"name":"Melman", "species":"giraffe", "height": 4}')

Statement 8

INSERT INTO zoo VALUES (5, '{"name":"Leo", "species":"Lion", "food":["Giraffes", "Meow Mix"]}')

Statement 9
The following examples use this zoo dataset to show how JSON_MERGEPATCH can be used to update JSON stored in a table.
```
SELECT * 
FROM zoo
```
Statement 10
This statement adds an entry "admission"=2.75 to all JSON values stored in the table.
```
UPDATE zoo
SET jcol = JSON_MERGEPATCH(jcol, '{"admission":2.75}')
```
Statement 11
```
SELECT * 
FROM zoo
```
Statement 12
This statement updates the JSON document for a specific row (the giraffe). It updates the height of the giraffe from 4 to 5 and also adds an entry for key "notes".
```
UPDATE zoo
SET jcol = JSON_MERGEPATCH(jcol, '{"height":5, "notes" : "He bites."}')
WHERE id = 4
```
Statement 13
```
SELECT * 
FROM zoo 
WHERE id = 4;
```
Statement 14
This example shows how JSON_MERGEPATCH can be used to perform a relative update. In this case, it is converting the value of the "species" attribute to uppercase.
```
UPDATE zoo z
SET jcol = JSON_MERGEPATCH(jcol,
              JSON_OBJECT('species' VALUE UPPER(z.jcol.species))
           )
```
Statement 15
```
SELECT * 
FROM zoo
```

Statement 16

This example raises the value of the "admission" entry by 40% for the dolphin object.

UPDATE zoo z
SET jcol = JSON_MERGEPATCH(jcol,
              JSON_OBJECT('admission' VALUE z.jcol.admission * 1.4)
           )
WHERE z.jcol.name = 'Flipper'

Statement 17
```
SELECT * 
FROM zoo
```
Statement 18
Using null in a patch document will cause the corresponding entry in the target document to be removed (it will not set it to null). This example removes the "admission" entry from all objects in the table. It is not possible to set an entry to null using JSON_MERGEPATCH. Therefore, JSON_MERGEPATCH is not suitable for applications that need to distinguish cases where an attribute is null and when it is not present.
```
UPDATE zoo
SET jcol = JSON_MERGEPATCH(jcol, '{"admission" : null}')
```
Statement 19
```
SELECT * 
FROM zoo
```

Statement 20

This example sets the food attribute in the cat object to a nested object.

UPDATE zoo
SET jcol = JSON_MERGEPATCH(jcol, '{
    "food" : { 
         "breakfast" : "milk", 
         "dinner" : "fish"
     }
}')
WHERE id = 3

Statement 21
```
SELECT * 
FROM zoo 
WHERE id = 3
```
Statement 22
JSON_MERGEPATCH can be used to make partial updates to nested JSON objects. This example updates the nested object for the cat - it leaves breakfast set to "milk", adds an entry for "lunch", and changes "dinner" from "fish" to "chicken".
```
UPDATE zoo
SET jcol = JSON_MERGEPATCH(jcol, '{
    "food" : { 
         "lunch" : "Meow Mix",
         "dinner" : "chicken"
     }
}')
WHERE id = 3
```
Statement 23
```
SELECT * 
FROM zoo 
WHERE id = 3
```
Statement 24
```
SELECT * 
FROM zoo 
WHERE id = 5
```
Statement 25
JSON_MERGEPATCH can not be used to make partial modifications to arrays. Therefore, JSON_MERGEPATCH may not be suitable for applications that use arrays of large nested objects. This example fully replaces the array in the lion object.
```
UPDATE zoo
SET jcol = JSON_MERGEPATCH(jcol, '{"food" : ["Giraffes", "Meow Mix", "Fish"]}')
WHERE id = 5
```
Statement 26
```
SELECT * 
FROM zoo 
WHERE id = 5
```

Statement 27

Bind variables may be used with JSON_MERGEPATCH. This example updates the dog's name to Fido.

DECLARE
  newname VARCHAR2(16) := 'Fido';
  id      NUMBER := 2;
BEGIN
  EXECUTE IMMEDIATE  
     'UPDATE zoo SET jcol = JSON_MERGEPATCH(jcol, JSON_OBJECT(''name'' VALUE :1)) WHERE id = :2'  
      USING newname, id;
END;

Statement 28
```
SELECT * FROM zoo WHERE id = 2;
```
Statement 29
Edge case: If the patch document is not an object, the patch value itself is returned.
```
SELECT JSON_MERGEPATCH('{"a" : 1}', '["hello"]')
FROM dual;
```
Statement 30
Edge case: If the target document is not an object, the patch document is applied to an empty JSON object ({}) and the resulting value is returned.
```
SELECT JSON_MERGEPATCH('["hello"]', '{"a":1, "b":null}')
FROM dual;
```
Statement 31
By default, the result of JSON_MERGEPATCH will not contain insignificant whitespace. The PRETTY option can be used to format the result object in a way that is easier to read. Note, this example may not work well in Live SQL since formatting of the query result sometimes strips or modifies whitespace.
```
SELECT 'Example = ' || JSON_MERGEPATCH('{"a":1}', '{"b":{"c":2}}' )
FROM dual;
```

Statement 32

SELECT 'Example = ' || JSON_MERGEPATCH('{"a":1}', '{"b":{"c":2}}' PRETTY)
FROM dual;

Statement 33
The default return type of JSON_MERGEPATCH is VARCHAR2. The RETURNING clause can be used to return LOB types instead.
```
SELECT JSON_MERGEPATCH('{"a":1}', '{"b":2}' RETURNING BLOB)
FROM dual;
```

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database