MySQL如何根据JSON数组中subscriptions的元素数量查询数据

首先注意你第一条数据的info字段JSON格式有误——数组里不能直接放键值对，正确格式应该是对象：{"subscriptions": [{"user": 1}, {"user": 2}]}，如果是当前错误格式，得先修正才能正常解析。

假设info是合法的JSON对象，用JSON_LENGTH()就能直接获取subscriptions数组的元素数量，实现你要的筛选：

基础查询语句 #

SELECT * 
FROM user_info 
WHERE JSON_LENGTH(info->>'$.subscriptions') = 2;

兼容特殊场景（空数组、null值） #

如果要避免info为null或者subscriptions不存在时的异常，加上额外判断：

SELECT * 
FROM user_info 
WHERE info IS NOT NULL 
  AND JSON_TYPE(info->>'$.subscriptions') = 'ARRAY'
  AND JSON_LENGTH(info->>'$.subscriptions') = 2;

关键函数说明 #

• info->>'$.subscriptions'：通过JSON路径提取subscriptions数组，->>是简化写法，等价于JSON_UNQUOTE(JSON_EXTRACT(info, '$.subscriptions'))
• JSON_LENGTH()：针对数组返回元素个数，针对对象返回键值对数量，这里刚好用来统计订阅数
• JSON_TYPE()：校验提取的内容是否为数组类型，避免非数组数据导致的计算错误

要是你的info字段确实是错误的数组包裹对象格式（["subscriptions": [...]]），优先建议修正数据格式；如果暂时没法改，也可以用JSON_LENGTH(info[0].subscriptions)来取值，但这种写法依赖错误格式，不推荐长期使用。

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