Outdated Version

You are viewing an older version of this section. View current production version.


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, ...])


  • 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


mysql> select json_splice_json('[1,2,3]', 2, 3, '[4,5,6]') as nested;
| nested        |
| [1,2,[4,5,6]] |

mysql> select json_splice_string('[1,2,3,4,5]', 2, 2, 'o', 'hai', '!') as ohai;
| ohai                  |
| [1,2,"o","hai","!",5] |

mysql> 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.