JSON_
On this page
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_is for all numeric data.SPLICE_ DOUBLE Javascript has only one numeric type. -
JSON_is for all text and binary types.SPLICE_ STRING -
JSON_is for all valid Javascript types, includingSPLICE_ JSON true,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_DOUBLE('[1,2,3]', 5, 6) AS numbers;
+---------------+
| numbers |
+---------------+
| [1,2,3] |
+---------------+SELECT JSON_SPLICE_STRING('[1,2,3,4,5]', 2, 2, 'alpha', 'beta', '!') AS alphabet;
+----------------------------+
| alphabet |
+----------------------------+
| [1,2,"alpha","beta","!",5] |
+----------------------------+SELECT JSON_SPLICE_STRING('[1,2,3,4,5]', -1, 1) AS num_set;
+-----------+
| num_set |
+-----------+
| [1,2,3,4] |
+-----------+SELECT JSON_SPLICE_JSON('[1,2,3]', 2, 3, '[4,5,6]') AS nested;
+---------------+
| nested |
+---------------+
| [1,2,[4,5,6]] |
+---------------+Note
A JSON or Javascript null value is distinct from SQL NULL.
Last modified: April 4, 2023