MySQL查询中如何提取JSON字段内匹配当前星期的对应键值

首先注意你提供的JSON示例格式存在小问题：外层对象缺少键名，以下示例默认你存储的JSON结构为外层键名为schedule的数组（如果直接存储数组，将下方路径中的$.schedule替换为$即可），数据表名为task_table，存储JSON的字段名为config，你可以根据实际名称替换。 #

步骤1：确定当前星期的数值匹配规则 #

MySQL自带的星期函数返回值规则如下，你可以根据你day_of_week字段的数值规则调整：

• DAYOFWEEK(CURDATE())：返回1（周日）~7（周六）
• WEEKDAY(CURDATE()) + 1：返回1（周一）~7（周日）
  以下示例统一用@current_weekday存储待匹配的星期值。

方案1：MySQL 8.0及以上版本（支持JSON_TABLE） #

变量初始化 #

-- 赋值当前待匹配星期值，根据你的规则调整函数
SET @current_weekday = WEEKDAY(CURDATE()) + 1;
-- 初始化存储结果的变量
SET @interval_val = NULL;
SET @start_val = NULL;
SET @end_val = NULL;
SET @matched_weekday = NULL;

查询匹配并赋值 #

SELECT 
    JSON_UNQUOTE(JSON_EXTRACT(item, '$.interval')),
    JSON_UNQUOTE(JSON_EXTRACT(item, '$.start')),
    JSON_UNQUOTE(JSON_EXTRACT(item, '$.end')),
    JSON_UNQUOTE(JSON_EXTRACT(item, '$.day_of_week'))
INTO @interval_val, @start_val, @end_val, @matched_weekday
FROM task_table,
     JSON_TABLE(
         config ->> '$.schedule', -- 替换为你实际的JSON数组路径
         '$[*]' COLUMNS (
             item JSON PATH '$'
         )
     ) AS json_items
WHERE JSON_EXTRACT(item, '$.day_of_week') = @current_weekday
LIMIT 1; -- 存在多个匹配时只取第一个，不需要可删除

执行完成后，如果存在匹配项，四个变量会存储对应值，无匹配则值为NULL，你可以直接在后续查询中使用这些变量。 #

方案2：MySQL 5.7版本（无JSON_TABLE支持） #

SET @current_weekday = WEEKDAY(CURDATE()) + 1;
SET @i = 0;
SET @matched_index = -1;
-- 遍历JSON数组找匹配项的下标
WHILE @i < JSON_LENGTH(config ->> '$.schedule') DO
    IF JSON_EXTRACT(config ->> '$.schedule', CONCAT('$[', @i, '].day_of_week')) = @current_weekday THEN
        SET @matched_index = @i;
        LEAVE WHILE;
    END IF;
    SET @i = @i + 1;
END WHILE;
-- 匹配到则赋值变量
IF @matched_index != -1 THEN
    SET @interval_val = JSON_UNQUOTE(JSON_EXTRACT(config ->> '$.schedule', CONCAT('$[', @matched_index, '].interval')));
    SET @start_val = JSON_UNQUOTE(JSON_EXTRACT(config ->> '$.schedule', CONCAT('$[', @matched_index, '].start')));
    SET @end_val = JSON_UNQUOTE(JSON_EXTRACT(config ->> '$.schedule', CONCAT('$[', @matched_index, '].end')));
    SET @matched_weekday = @current_weekday;
END IF;

注意事项 #

• 若你的day_of_week字段为字符串类型存储，匹配时需要用JSON_UNQUOTE包裹提取的字段，或把@current_weekday转为字符串类型避免类型不匹配
• 如果需要处理表中多行数据，将上述逻辑套入游标或对应WHERE条件即可

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