JSON_SET

Inserts or replaces values in a JSON document at specified paths.

This function returns a new JSON document that is a copy of the input json with values set at the given paths. If a path already exists, its value is replaced. If it does not exist, the new value is inserted. This behavior combines the functionality of JSON_INSERT and JSON_REPLACE.

For inserting into a JSON array, use a path like '$.arr[3]' to set the value at index 3. If the index is out of bounds, the value is appended to the end of the array. To append to an array, use '$.arr[#]'.

Example:

val jsonCol = jsonColumn("data") // Contains '{"a":2,"c":[4,5]}'

// Replace existing key 'a' and insert a new key 'b'
// SELECT JSON_SET(data, '$.a', 9, '$.b', 7)
// Result: '{"a":9,"c":[4,5],"b":7}'
select(JSON_SET(jsonCol, "$.a" to 9, "$.b" to 7)).from(MyTable).execute()

// Replace an element in the array and append a new one
// SELECT JSON_SET(data, '$.c[0]', 99, '$.c[#]', 6)
// Result: '{"a":2,"c":[99,5,6]}'
select(JSON_SET(jsonCol, "$.c[0]" to 99, "$.c[#]" to 6)).from(MyTable).execute()

Parameters

See also