在mysql json中按值删除数组元素 [英] Remove array element by value in mysql json

问题描述

是否可以通过 mysql 的 json 数组中的值(而不是索引)删除元素?例如:

# ['new', 'orange']更新候补名单 SET new = JSON_REMOVE("orange", '$') WHERE id=2;# 现在它将是 ['new']

如果没有，有没有更好的方法来存储它，以便我可以根据需要删除元素?此外，数组中永远不会有重复项.

解决方案

如果您知道数组中永远不会有重复项，您可以使用 JSON_SEARCH 来查找要删除的值的路径，然后使用 JSON_REMOVE 删除它.请注意，您需要检查 JSON_SEARCH 是否确实找到了值，否则 JSON_REMOVE 将使整个字段无效:

更新候补名单SET new = JSON_REMOVE(new, JSON_UNQUOTE(JSON_SEARCH(new, 'one', 'orange')))WHERE JSON_SEARCH(new, 'one', 'orange') 不是 NULL

我制作了一个关于 dbfiddle 的小型演示.>

请注意，您必须对来自 JSON_SEARCH 的响应使用 JSON_UNQUOTE 以使其成为 JSON_REMOVE 的有效路径.

Is it possible to remove an element by its value (and not its index) in a json array in mysql? For example:

# ['new', 'orange']
update waitinglist SET new = JSON_REMOVE("orange", '$') WHERE id=2;
# now it will be ['new']

If not, is there a better way to store this, so I can remove elements as needed? Also, there would never be duplicates in the array.

解决方案

If you know there are never duplicates in the array, you can use JSON_SEARCH to find the path to the value you want to delete, and then use JSON_REMOVE to remove it. Note that you need to check that JSON_SEARCH actually finds a value, otherwise JSON_REMOVE will nullify the entire field:

UPDATE waitinglist 
SET new = JSON_REMOVE(new, JSON_UNQUOTE(JSON_SEARCH(new, 'one', 'orange')))
WHERE JSON_SEARCH(new, 'one', 'orange') IS NOT NULL

I've made a small demo on dbfiddle.

Note you have to use JSON_UNQUOTE on the response from JSON_SEARCH to make it a valid path for JSON_REMOVE.

这篇关于在mysql json中按值删除数组元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！