MySQL如何用JSON_EXTRACT查询嵌套层动态键下指定邮箱是否存在

错误原因 #

你之前的查询无法生效主要有两个问题：

• JSON路径漏写最外层form_data节点，根本匹配不到任何目标层级的值
• MySQL的JSON_EXTRACT不支持在路径中间用*通配符匹配任意动态键名，执行后只会返回NULL，自然无法匹配到目标邮箱

兼容MySQL 5.7及以上版本方案（推荐） #

使用MySQL内置的JSON_SEARCH函数实现动态路径匹配，语法如下：

SELECT * 
FROM `table` AS a 
WHERE JSON_VALID(a.data)
AND JSON_SEARCH(
    a.data,
    'one',
    'abc@yopmail.com',
    NULL,
    '$.form_data.company.additional.*.email'
) IS NOT NULL;

函数参数说明：

• 第二个参数填one表示只要找到一个匹配结果就直接返回，无需遍历所有节点，性能更好
• 第四个参数为转义字符，填NULL即可
• 第五个参数限制搜索范围，只匹配form_data.company.additional下任意子键的email字段，避免全JSON扫描提升效率

MySQL 8.0及以上版本可选方案（适合多字段提取场景） #

如果需要同时提取动态键下的其他字段，可以用JSON_TABLE将嵌套JSON结构转为临时表再筛选：

SELECT a.*
FROM `table` AS a,
JSON_TABLE(
    JSON_EXTRACT(a.data, '$.form_data.company.additional'),
    '$.*' COLUMNS (
        email VARCHAR(255) PATH '$.email'
        -- 这里可以继续加其他需要提取的字段
    )
) AS jt
WHERE JSON_VALID(a.data)
AND jt.email = 'abc@yopmail.com';

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