How to insert new value in the middle of a JSON array?

Question

I have a JSON type field in my table, which has a value like this

[1, 3]

and I want to insert another value in the middle of the array, using something like a json splice function (I know it doesn't exist)

/* JSON_ARRAY_SPLICE(array, start, deleteCount, itemToInsert) */
JSON_ARRAY_SPLICE('[1, 3]', 1, 0, 2)
>>> [1, 2, 3]

I'm using php and I can make a function in order to do it, but i'm looking for a mysql solution. Is there a way I can achieve this?

Answer 1

mysql> set @j = '[1, 3]';

mysql> select json_array_insert(@j, '$[1]', 2) as new_j;
+-----------+
| new_j     |
+-----------+
| [1, 2, 3] |
+-----------+