MySQL中如何统计JSON字段内符合条件的数值出现次数并筛选数据

问题原因 #

你之前的SQL存在三个问题：

• 逻辑判断用了OR，只要有1个值小于65就会命中，不符合「至少2个值小于65」的要求
• 从JSON中取出的值是字符串类型，未转换为数值，直接比较可能出现不符合预期的结果
• 查询结果仅返回了提取的三个JSON字段，没有返回原表的完整记录

解决方案 #

方案1：已知JSON固定为3个键（1/2/3）时使用

直接对每个值的判断结果求和，布尔判断在MySQL中会返回1（真）或0（假），求和结果≥2即满足条件：

SELECT id, client_id, service_values
FROM service_data
WHERE 
  (CAST(JSON_UNQUOTE(JSON_EXTRACT(service_values, '$.1')) AS UNSIGNED) < 65) +
  (CAST(JSON_UNQUOTE(JSON_EXTRACT(service_values, '$.2')) AS UNSIGNED) < 65) +
  (CAST(JSON_UNQUOTE(JSON_EXTRACT(service_values, '$.3')) AS UNSIGNED) < 65) >= 2;

方案2：JSON键不固定/数量不固定时使用（仅支持MySQL 8.0及以上版本）

通过JSON_TABLE将JSON的所有值拆分为多行，聚合统计小于65的数值数量：

SELECT t.id, t.client_id, t.service_values
FROM service_data t
JOIN JSON_TABLE(
  t.service_values,
  '$.*' COLUMNS(val INT PATH '$')
) AS jv
GROUP BY t.id, t.client_id, t.service_values
HAVING SUM(IF(jv.val < 65, 1, 0)) >= 2;

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