如何从 MySQL 的 JSON 数组中删除一个数字? [英] How to remove a number from MySQL's JSON array?

问题描述

如果我有一个 MySQL 表，其中有一个名为 numbers 的 JSON 列，并且该列(整数数组)中有一个 [1, 2, 3] 记录，如何更新该记录以删除 2(使其变为 [1, 3])?

If I have a MySQL table with a JSON column called numbers and a record that has [1, 2, 3] in that column (array of integers), how do I update that record to remove the 2 (so it becomes [1, 3])?

推荐答案

我自己在寻找答案并来到这个问题，不想使用对象我继续搜索.但是我找到了一个解决方案，你需要结合使用 json_remove 和 json_search

I was searching for an answer my self and came to this question, not wanting to use objects I continued the search. But I found a solution, you need to use a combination of json_remove and json_search

以下从表 tbl 和列 numbers

UPDATE tbl
SET numbers = JSON_REMOVE(
  numbers, replace(json_search(numbers, 'one', 1), '"', '')
)
WHERE json_search(numbers, 'one', 1) IS NOT NULL

1. json_search 返回值所在的路径，即."$[0]"
2. replace 删除 " 否则 json_remove
会出现错误
3. json_remove 将从 json_search 结果中删除路径

1. json_search returns the path of where the value is, ie. "$[0]"
2. replace remove the " otherwise an error will occur with json_remove
3. json_remove will remove the path from the json_search result

等等，你的值被删除了.

Et voila, your value is removed.

注意:这里假设没有重复值

这篇关于如何从 MySQL 的 JSON 数组中删除一个数字?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！