JSON_PATCH

Applies a JSON patch to a JSON document.

This function modifies the input json document according to the instructions in the patch document, following the standard defined in RFC 7396. The patch document is a JSON object or array of objects that describes a sequence of operations to perform (e.g., "add", "remove", "replace").

If either json or patch is not well-formed JSON, this function will throw an error. If the patch operations are invalid (e.g., trying to replace a value that doesn't exist), the function may throw an error or return the original json document, depending on the nature of the error.

Example:

val jsonCol = jsonColumn("data") // Contains '{"a": "hello", "b": [1, 2]}'
val patchCol = jsonColumn("patch") // Contains '[{"op": "replace", "path": "/a", "value": "world"}, {"op": "add", "path": "/b/-", "value": 3}]'

// SELECT JSON_PATCH(data, patch)
// Result: '{"a":"world","b":[1,2,3]}'
select(JSON_PATCH(jsonCol, patchCol)).from(MyTable).execute()

// Example with literals
val originalJson = JSON("""{"a": "hello"}""")
val patchJson = JSON("""[{"op": "add", "path": "/b", "value": "new"}]""")
// SELECT JSON_PATCH(originalJson, patchJson)
// Result: '{"a":"hello","b":"new"}'
select(JSON_PATCH(originalJson, patchJson)).execute()

Parameters

See also