我需要确保数组已排序
这不是最容易理解的方法,但我认为您必须使用 SQL 数字生成器将 json 数组解析为标记(记录),您可以更轻松地过滤和排序。
Query
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 , ']')), '"', '')
Result
| json |
| ----------- |
| ["1", "18"] |
see demo
另一方面,MySQL 8+ 让它变得更加容易
Query
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
Result
| json |
| ----------- |
| ["1", "18"] |
see demo