MySQL中查询varchar类型JSON字段内activities值为3的记录

需求概述 #

你的表中有一个data列（varchar(3000)类型），存储JSON格式数据，示例如下：

{"homeWidgetToShow":"chat_and_notes","official":false,"nationalFederation":false,"categories":[],"activities":{"0":2,"2":7,"1":3},"clubsBlackList":[],"privateMembers":false,"alwaysShowDescription":false,"restrictMembersFromPositingForums":false}

需要筛选出**data.activities子对象中存在值为3**的记录，需注意：

• activities键可能不存在于部分JSON中
• 值为3对应的键不固定（示例中是"1"，但可能是任意键）

可行的查询方案 #

方案1：使用JSON_CONTAINS函数（MySQL 5.7+） #

通过JSON提取和包含判断实现，自动忽略activities不存在的记录：

SELECT *
FROM your_table_name
WHERE JSON_CONTAINS(JSON_EXTRACT(data, '$.activities'), '3', '$');

• 逻辑：JSON_EXTRACT(data, '$.activities')提取activities子对象；JSON_CONTAINS的第三个参数'$'指定在整个子对象范围内搜索值为3的元素。若activities不存在，JSON_EXTRACT返回NULL，JSON_CONTAINS返回0，不会匹配该记录。

方案2：使用JSON_TABLE展开键值对（MySQL 8.0+） #

将activities的所有值展开为行数据，再筛选目标值，逻辑更直观：

SELECT DISTINCT t.*
FROM your_table_name t
JOIN JSON_TABLE(
    JSON_EXTRACT(t.data, '$.activities'),
    '$.*' COLUMNS(activity_val INT PATH '$')
) jt
WHERE jt.activity_val = 3;

• 逻辑：JSON_TABLE把activities的每个值转换成单独的行，筛选出值为3的行后，通过DISTINCT确保原表记录只返回一次（避免activities中有多个3时重复输出）。若activities不存在，JSON_TABLE无数据返回，原表记录不会被匹配。

注意事项 #

• 请将your_table_name替换为你的实际表名
• MySQL 5.7版本仅支持方案1，8.0及以上版本推荐方案2，可读性更强
• 确保data列存储的是合法JSON格式，否则JSON函数可能报错

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