如何从MySQL JSON中删除多个值 [英] How to remove multiple values FROM MySQL JSON
问题描述
我需要从MySQL的JSON数组中删除多个值
I need to remove multiple values from JSON Array in MySQL
我尝试对每个值使用JSON_SEARCH进行JSON_REMOVE,但在删除元素后INDEX更改
I've tried JSON_REMOVE with JSON_SEARCH on each value but after removing element the INDEX changes
JSON数组:
["1", "5", "18", "68"]
例如:删除"5"和"68"
for example : remove "5" and "68"
预期结果:
["1", "18"]
已
我尝试过的代码:
JSON_REMOVE(`can_see`, JSON_UNQUOTE(JSON_SEARCH(`can_see`, 'one', '5')), JSON_UNQUOTE(JSON_SEARCH(`can_see`, 'one', '68')))
查询结果:
["1", "18", "68"]
在索引$ [0]处删除"5"后,"68"索引更改为$ [2],但JSON_SEARCH从原始json返回$ [3]
after removing "5" at index $[0], "68" index changed to $[2] but the JSON_SEARCH return $[3] from the original json
我也尝试过嵌套:
JSON_REMOVE(JSON_REMOVE(`can_see`, JSON_UNQUOTE(JSON_SEARCH(`can_see`, 'one', '5'))), JSON_UNQUOTE(JSON_SEARCH(JSON_REMOVE(`can_see`, JSON_UNQUOTE(JSON_SEARCH(`can_see`, 'one', '5'))), 'one', '68')))
可以,但是如果我想删除两个以上的值,则会变得混乱
that works but it gets messy if i want to remove more than 2 values
推荐答案
我需要确保数组已排序
i need to be sure that the array is sorted
不是最容易理解的方法,但是我认为您必须使用SQL数字生成器将json数组解析为令牌(记录),您可以更轻松地对其进行过滤和排序.
Not the most easy understandable way but i think you would have to use a SQL number generator to parse out the json array as tokens (records) which you can filter and order by more easy.
查询
SELECT
JSON_ARRAYAGG(
JSON_EXTRACT(records.json, CONCAT('$[', number_generator.number , ']'))
) AS json
FROM (
SELECT
@row := @row + 1 AS number
FROM (
SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) row1
CROSS JOIN (
SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) row2
CROSS JOIN (
SELECT @row := -1
) init_user_params
) AS number_generator
CROSS JOIN (
SELECT
json
, JSON_LENGTH(records.json) AS json_array_length
FROM (
SELECT
'["1", "5", "18", "68"]' AS json
FROM
DUAL
) AS records
) AS records
WHERE
number BETWEEN 0 AND json_array_length - 1
AND
JSON_EXTRACT(records.json, CONCAT('$[', number_generator.number , ']')) NOT IN(5, 68)
ORDER BY
REPLACE(JSON_EXTRACT(records.json, CONCAT('$[', number_generator.number , ']')), '"', '')
结果
| json |
| ----------- |
| ["1", "18"] |
请参见演示
另一方面,MySQL 8+使它变得更加简单
MySQL 8+ on the other hand makes it much more easy
查询
SELECT
JSON_ARRAYAGG (
result_table.item
) AS json
FROM JSON_TABLE(
'["1", "5", "18", "68"]'
, "$[*]"
COLUMNS (
rowid FOR ORDINALITY
, item VARCHAR(100) PATH "$"
)
) AS result_table
WHERE
CAST(result_table.item AS UNSIGNED) NOT IN(5, 68)
ORDER BY
CAST(result_table.item AS UNSIGNED) ASC
结果
| json |
| ----------- |
| ["1", "18"] |
请参见演示
这篇关于如何从MySQL JSON中删除多个值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!