MySQL 8.0 存储键值对象的JSON列无法搜索目标值怎么解决

原查询失败原因 #

JSON_CONTAINS 用于校验目标JSON文档是否包含完整的指定JSON结构，你传入的'true'是JSON布尔标量，函数会尝试匹配整列的JSON值是否等于true，而非匹配JSON对象内部的属性值，因此无法返回预期结果。

标准实现方案 #

方案1：使用JSON_SEARCH（兼容性最优，写法最简单）

JSON_SEARCH 用于返回JSON中指定值对应的路径，只要返回非空即代表存在匹配的属性：

SELECT *
FROM `redirects`
WHERE JSON_SEARCH(`synced_markets`, 'one', true) IS NOT NULL;

参数说明：第二个参数'one'代表只要找到第一个匹配项就返回，无需遍历所有属性，刚好匹配「至少有一个市场值为true」的需求。注意第三个参数直接传布尔值true即可，若写成字符串格式的'true'会匹配JSON中的字符串值，无法匹配布尔类型的true，这也是你之前使用JSON_SEARCH无结果的常见原因。

方案2：使用JSON_TABLE（适合复杂查询场景，MySQL 8.0.4及以上支持）

将JSON对象的所有键值对展开为临时表后筛选，适合需要同时关联市场编码做其他逻辑的场景：

SELECT DISTINCT r.*
FROM `redirects` r,
JSON_TABLE(
  JSON_KEYS(r.synced_markets),
  '$[*]' COLUMNS(market_code VARCHAR(10) PATH '$')
) jt
WHERE JSON_EXTRACT(r.synced_markets, CONCAT('$.', jt.market_code)) = true;

以上两种方案均为MySQL官方支持的标准JSON查询写法，不会出现CAST转字符串后LIKE可能引发的误匹配问题（比如属性名包含true字符串的极端场景）。

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