MySQL JSON_REMOVE json 数组

Question

我有 channel 表:

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

并且需要从 id 1 中删除“2”值，所以我需要得到这个:

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

我尝试使用这个查询:

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

但如果我使用“$[2]”则值不会被删除然后值被删除但我需要按值而不是索引删除...有谁知道如何从 json 数组中删除特定值？

Answer 1

尝试:

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

参见 db-fiddle .