MySQL JSON_REMOVE json array

Question

I have channels table:

+----+-------------------+---------+
| id | sort              | bouquet |
+----+-------------------+---------+
|  1 | ["1","2","3","4"] | ["1"]   |
|  2 | ["4"]             | ["4"]   |
+----+-------------------+---------+

And need to remove "2" value from id 1 so i need to get this:

+----+-------------------+---------+
| id | sort              | bouquet |
+----+-------------------+---------+
|  1 | ["1","3","4"]     | ["1"]   |
+----+-------------------+---------+

I try using this query:

SELECT id, sort, bouquet, JSON_REMOVE(sort, '$."2"') FROM channels WHERE id=1;

But value is not removed if i use '$[2]' then value is removed but i need to remove by value not index...does anyone knows how to remove from json array specific value?

Answer 1

Try:

SELECT
  `id`,
  `sort`,
  `bouquet`,
  JSON_REMOVE(`sort`,
              JSON_UNQUOTE(
                JSON_SEARCH(`sort`, 'one', 2)
              ))
FROM `channels`
WHERE `id` = 1;

See db-fiddle.

Answer 2

Try This

UPDATE channels SET `sort` = JSON_REMOVE(`sort`, '$[1]') WHERE `id` = 1 

Working for me