JSON_INSERT

Inserts one or more values into a JSON document at specified paths.

This function returns a new JSON document that is a copy of the input json with new values inserted at the given paths. It only inserts values into locations that do not already exist. If a specified path already has a value, the original value is retained, and the new value is ignored.

For inserting into a JSON array, use a path like '$.arr[3]' to insert 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[#]'.

Use JSON_REPLACE to update existing values, and JSON_SET to either update existing values or insert new ones.

Example:

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

// Insert a new key 'b' and a new element into array 'c'
// SELECT JSON_INSERT(data, '$.b', 7, '$.c[#]', 6)
// Result: '{"a":2,"c":[4,5,6],"b":7}'
select(JSON_INSERT(jsonCol, "$.b" to 7, "$.c[#]" to 6)).from(MyTable).execute()

// Attempt to insert at an existing path 'a' (will be ignored)
// SELECT JSON_INSERT(data, '$.a', 9)
// Result: '{"a":2,"c":[4,5]}'
select(JSON_INSERT(jsonCol, "$.a" to 9)).from(MyTable).execute()

Parameters

See also