MySQL如何查询嵌套JSON中的数组对象元素？

针对你的需求，查询community__sharded表中data字段的moderators数组包含指定userId的记录，以下是几种可行的解决方案：

方法1：使用JSON_CONTAINS函数（推荐） #

JSON_CONTAINS可直接检查JSON文档中是否包含指定的JSON片段，适配数组元素匹配场景。注意JSON里的userId是字符串类型，查询时要加引号：

SELECT * 
FROM community__sharded 
WHERE JSON_CONTAINS(data->'$.moderators', JSON_OBJECT('userId', '000001'));

方法2：使用JSON_TABLE展开数组查询 #

如果需要对数组元素做更复杂筛选，或要返回数组内具体元素，可通过JSON_TABLE将JSON数组转为关系型临时表，再关联查询：

SELECT c.*
FROM community__sharded c
JOIN JSON_TABLE(
    c.data->'$.moderators',
    '$[*]' COLUMNS (userId VARCHAR(20) PATH '$.userId')
) AS mods
WHERE mods.userId = '000001';

方法3：修复原查询的问题 #

你的原查询存在两个问题：一是JSON中userId是字符串（"000001"），你用了数字000001（MySQL会解析为整数1），类型不匹配；二是data->'$.moderators.*.userId'返回多值JSON数组，不能直接用=匹配。可改用JSON_OVERLAPS检查交集：

SELECT * 
FROM community__sharded 
WHERE JSON_OVERLAPS(data->'$.moderators.*.userId', JSON_ARRAY('000001'));

关键注意点 #

• 类型匹配：必须保证查询时的userId格式和JSON中一致（字符串类型要加引号），否则会因类型不匹配无法命中结果。
• 版本要求：上述函数（JSON_CONTAINS、JSON_TABLE、JSON_OVERLAPS）需MySQL 5.7及以上版本支持。

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