MySQL中JSON字段NOT IN查询失效问题求助

我来帮你搞定这个需求！你要从Request表中筛选出Details字段里的Key值，不存在于Response表response字段Key值中的行，对吧？结合你的示例数据，最终应该会返回Key为cf287991-434c-e811-80d1-6a077df8507e的那一行。

这里需要注意：直接用->运算符获取JSON属性时，返回的结果是带双引号的字符串（比如"9523beaf-934b-e811-80d1-6a077df8507e"），如果直接用来匹配可能会出问题，所以推荐用->>运算符（或者JSON_UNQUOTE()函数）来获取不带引号的纯字符串值。

下面给你两种可行的查询方案：

方案一：使用NOT EXISTS子查询 #

这种方式逻辑清晰，直接检查当前Request行的Key是否在Response中存在：

SELECT msr.*
FROM `Request` msr
WHERE NOT EXISTS (
    SELECT 1
    FROM `Response` rsp
    WHERE rsp.response->>'$.Key' = msr.Details->>'$.Key'
);

方案二：使用LEFT JOIN筛选不匹配行 #

通过左连接保留Request的所有行，然后筛选出没有匹配到Response的记录：

SELECT msr.*
FROM `Request` msr
LEFT JOIN `Response` rsp ON rsp.response->>'$.Key' = msr.Details->>'$.Key'
WHERE rsp.response IS NULL;

兼容旧版本MySQL的写法 #

如果你的MySQL版本低于5.7.9（不支持->>运算符），可以用JSON_UNQUOTE()替代：

SELECT msr.*
FROM `Request` msr
WHERE NOT EXISTS (
    SELECT 1
    FROM `Response` rsp
    WHERE JSON_UNQUOTE(rsp.response->'$.Key') = JSON_UNQUOTE(msr.Details->'$.Key')
);

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