SELECT JSON_MERGEPATCH('{"a":1}', '{"b":2}')
FROM dual
SELECT JSON_MERGEPATCH('{"a":1}', '{"b":2, "a":3}')
FROM dual
CREATE TABLE zoo (id NUMBER PRIMARY KEY, jcol VARCHAR2(4000) CHECK (jcol IS JSON))
INSERT INTO zoo VALUES (1, '{"name":"Flipper", "species":"dolphin", "food":"fish"}')
INSERT INTO zoo VALUES (2, '{"name":"Lassie", "species":"dog", "food":"Friskies"}')
INSERT INTO zoo VALUES (3, '{"name":"Findus", "species":"cat", "food":["Meow","Mix"]}')
INSERT INTO zoo VALUES (4, '{"name":"Melman", "species":"giraffe", "height": 4}')
INSERT INTO zoo VALUES (5, '{"name":"Leo", "species":"Lion", "food":["Giraffes", "Meow Mix"]}')
SELECT *
FROM zoo
UPDATE zoo
SET jcol = JSON_MERGEPATCH(jcol, '{"admission":2.75}')
SELECT *
FROM zoo
UPDATE zoo
SET jcol = JSON_MERGEPATCH(jcol, '{"height":5, "notes" : "He bites."}')
WHERE id = 4
SELECT *
FROM zoo
WHERE id = 4;
UPDATE zoo z
SET jcol = JSON_MERGEPATCH(jcol,
JSON_OBJECT('species' VALUE UPPER(z.jcol.species))
)
SELECT *
FROM zoo
UPDATE zoo z
SET jcol = JSON_MERGEPATCH(jcol,
JSON_OBJECT('admission' VALUE z.jcol.admission * 1.4)
)
WHERE z.jcol.name = 'Flipper'
SELECT *
FROM zoo
UPDATE zoo
SET jcol = JSON_MERGEPATCH(jcol, '{"admission" : null}')
SELECT *
FROM zoo
UPDATE zoo
SET jcol = JSON_MERGEPATCH(jcol, '{
"food" : {
"breakfast" : "milk",
"dinner" : "fish"
}
}')
WHERE id = 3
SELECT *
FROM zoo
WHERE id = 3
UPDATE zoo
SET jcol = JSON_MERGEPATCH(jcol, '{
"food" : {
"lunch" : "Meow Mix",
"dinner" : "chicken"
}
}')
WHERE id = 3
SELECT *
FROM zoo
WHERE id = 3
SELECT *
FROM zoo
WHERE id = 5
UPDATE zoo
SET jcol = JSON_MERGEPATCH(jcol, '{"food" : ["Giraffes", "Meow Mix", "Fish"]}')
WHERE id = 5
SELECT *
FROM zoo
WHERE id = 5
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;
SELECT * FROM zoo WHERE id = 2;
SELECT JSON_MERGEPATCH('{"a" : 1}', '["hello"]')
FROM dual;
SELECT JSON_MERGEPATCH('["hello"]', '{"a":1, "b":null}')
FROM dual;
SELECT 'Example = ' || JSON_MERGEPATCH('{"a":1}', '{"b":{"c":2}}' )
FROM dual;
SELECT 'Example = ' || JSON_MERGEPATCH('{"a":1}', '{"b":{"c":2}}' PRETTY)
FROM dual;
SELECT JSON_MERGEPATCH('{"a":1}', '{"b":2}' RETURNING BLOB)
FROM dual;