MySQL 5.7环境下如何从JSON列中筛选符合指定条件的行

问题原因 #

你原来的SQL无法生效的核心原因是JSON_EXTRACT(data, "$[*].id")返回的是JSON数组类型（示例中返回结果为[1,2,3]），直接将数组和数值做比较时，MySQL会按字符串规则进行匹配，自然得不到正确结果。

MySQL 5.7 实现方案 #

MySQL 5.7的JSON功能不支持路径过滤表达式，也没有8.0才新增的JSON_TABLE函数，所以没法直接提取数组中符合条件的元素，但可以通过以下两种方案实现需求：

方案1：已知数组最大长度时，逐个下标匹配

如果你的业务中data字段的JSON数组长度固定，或者有明确的上限，可以直接逐个下标判断：

• 筛选存在id > 2的元素的Record行：

SELECT 
    JSON_EXTRACT(data, "$[*].id") as result
FROM Record
WHERE 
    JSON_EXTRACT(data, "$[0].id") > 2
    OR JSON_EXTRACT(data, "$[1].id") > 2
    OR JSON_EXTRACT(data, "$[2].id") > 2;

• 筛选存在timestamp在指定区间（示例为1623672921 ~ 1623900000）的元素的Record行：

SELECT 
    JSON_EXTRACT(data, "$[*].timestamp") as result
FROM Record
WHERE 
    (JSON_EXTRACT(data, "$[0].timestamp") BETWEEN 1623672921 AND 1623900000)
    OR (JSON_EXTRACT(data, "$[1].timestamp") BETWEEN 1623672921 AND 1623900000)
    OR (JSON_EXTRACT(data, "$[2].timestamp") BETWEEN 1623672921 AND 1623900000);

方案2：数组长度不固定时，自定义函数遍历判断

如果JSON数组长度不固定，可以自定义函数遍历数组元素判断是否符合条件：

1. 首先创建判断数组中是否存在id大于指定值的函数：

DELIMITER //
CREATE FUNCTION JSON_ARRAY_HAS_ID_GREATER(json_arr JSON, target INT) 
RETURNS BOOLEAN
DETERMINISTIC
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE arr_len INT DEFAULT JSON_LENGTH(json_arr);
    WHILE i < arr_len DO
        IF JSON_EXTRACT(json_arr, CONCAT("$[", i, "].id")) > target THEN
            RETURN TRUE;
        END IF;
        SET i = i + 1;
    END WHILE;
    RETURN FALSE;
END //
DELIMITER ;

2. 调用函数实现筛选：

SELECT 
    JSON_EXTRACT(data, "$[*].id") as result
FROM Record
WHERE JSON_ARRAY_HAS_ID_GREATER(data, 2);

按照同样的逻辑，你可以自定义判断timestamp是否在指定区间的函数，实现第二个筛选需求。

内容的提问来源于stack exchange，提问作者Gijs