Warning
SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.
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