如何在MySQL中过滤JSON字段的两个指定属性（国家AU、占比20）？

针对你要筛选statistics表中stats JSON字段里包含国家为AU且占比20的城市记录的需求，这里给你两个实用的MySQL查询方案：

这个方法语法简洁，适合快速匹配数组中符合结构的对象：

SELECT * 
FROM statistics
WHERE JSON_CONTAINS(
    stats->'$.stats.cities',
    JSON_OBJECT('country', 'AU', 'percentage', 20)
);

说明： #

• stats->'$.stats.cities' 先提取出JSON字段里的cities数组
• JSON_OBJECT('country', 'AU', 'percentage', 20) 构造一个和目标城市元素结构完全一致的JSON对象
• JSON_CONTAINS会检查数组中是否存在和这个对象完全匹配的元素，满足条件就返回对应的表行

如果之后需要对城市数据做更多筛选、排序或计算，推荐用JSON_TABLE把JSON数组转成关系型临时表，再关联查询：

SELECT s.*
FROM statistics s
JOIN JSON_TABLE(
    s.stats->'$.stats.cities',
    '$[*]' COLUMNS(
        country VARCHAR(2) PATH '$.country',
        percentage INT PATH '$.percentage'
    )
) AS city_stats
WHERE city_stats.country = 'AU' AND city_stats.percentage = 20;

说明： #

• JSON_TABLE把cities数组拆成一行行的结构化记录，每一行包含country和percentage两个字段
• 通过JOIN关联原表后，用WHERE条件精准筛选出符合要求的记录，最终返回原表的完整数据

注意：JSON_TABLE是MySQL 8.0及以上版本才支持的功能，如果你的MySQL版本是5.7，优先用方案1就好。

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