JSON_PATCH

Applies a JSON MergePatch (RFC-7396) to a JSON document, modifying it according to specified operations. This function is used to add, modify, or delete elements within JSON objects but treats JSON arrays as atomic units.

See also:
[SELECT] [JSON_EXTRACT]

---

Syntax

SELECT JSON_PATCH(target_json, patch_json) FROM <multipartIdentifier>;

Arguments

target_json
The JSON document to be patched.

patch_json
A JSON document describing the changes to be applied according to the MergePatch algorithm.

Return Value(s)

Returns the modified JSON document after the patch has been applied.

---

Examples

Simple MergePatch

-- Example: Simple merge of new elements into the target JSON object
SELECT JSON_PATCH('{"a":1,"b":2}', '{"c":3,"d":4}') AS patched_json;

Output: {"a":1,"b":2,"c":3,"d":4}

Replace Element in Array

-- Example: Replacing an array with a single value in the JSON object
SELECT JSON_PATCH('{"a":[1,2],"b":2}', '{"a":9}') AS patched_json;

Output: {"a":9,"b":2}

Remove Element Using Null

-- Example: Removing an element by setting it to null
SELECT JSON_PATCH('{"a":1,"b":2}', '{"a":9,"b":null,"c":8}') AS patched_json;

Output: {"a":9,"c":8}

Nested MergePatch

-- Example: Nested merge patch updating sub-keys within a nested JSON object
SELECT JSON_PATCH('{"a":{"x":1,"y":2},"b":3}', '{"a":{"y":9},"c":8}') AS patched_json;

Output: {"a":{"x":1,"y":9},"b":3,"c":8}

This functionality allows for flexible and dynamic updates to JSON stored in a database, particularly useful for applications requiring conditional modifications of data structures.