Watch the 7.3 Webinar On-Demand
This new release brings updates to Universal Storage, query
optimization, and usability that you won’t want to miss.
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, including true
, false
, JSON maps, and lists.JSON_SPLICE_<type>(json, start, length, value, [value, ...])
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] |
+-----------+
A JSON or Javascript null value is distinct from SQL NULL.