JSON_SPLICE_<type>
This is a set of three JSON functions, which deletes the given element indexes from an array, and optionally splices the new values in.
Arrays are in the form:
[1, 2, 3]
JSON_SPLICE_DOUBLE
is for all numeric data. Javascript has only one numeric type.JSON_SPLICE_STRING
is for all text and binary types.JSON_SPLICE_JSON
is for all valid Javascript types, includingtrue
,false
, JSON maps, and lists.
JSON_SPLICE_<type>(json, start, length, value, [value, ...])
Arguments
json: a valid JSON array, or the name of a JSON column.
start: the zero-indexed start position. This argument may also be negative, in which case it counts from the end of the array.
length: the number of array elements to delete.
value: any number of values as optional arguments. If no values are given, the elements in positions from start to start + length will simply be removed.
Return Value
The complete array
SQL NULL if json is not a valid JSON array
Examples
SELECT JSON_SPLICE_JSON('[1,2,3]', 2, 3, '[4,5,6]') AS nested; +---------------+ | nested | +---------------+ | [1,2,[4,5,6]] | +---------------+ SELECT JSON_SPLICE_STRING('[1,2,3,4,5]', 2, 2, 'o', 'hai', '!') AS ohai; +-----------------------+ | ohai | +-----------------------+ | [1,2,"o","hai","!",5] | +-----------------------+ SELECT JSON_SPLICE_STRING('[1,2,3,4,5]', -1, 1) AS pop; +-----------+ | pop | +-----------+ | [1,2,3,4] | +-----------+
Notice
A JSON or Javascript null value is distinct from SQL NULL.