Mysql提取json数据并搜索多个值

嗨，我有汽车列表网站，我想从 JSON 列中过滤多个值 data

我的数据库表如下所示：

id | label    | data
---|----------|---------------------------------
 1 | "test 1" | {"Year":"2014","Gear":"Auto"}
 2 | "test 2" | {"Year":"2010","Gear":"Manual"}
 3 | "test 3" | {"Year":"2009","Gear":"None"}

例如，我想按齿轮过滤，“自动”或“手动”：

SELECT * FROM test WHERE 
JSON_EXTRACT(data, "$.Year") in (2010,2014) AND
JSON_EXTRACT(data, "$.Gear") in ("Auto","Manual")

当我添加过滤器时，查询返回零结果Gear，但过滤器Year工作正常。

回答

SELECT * FROM test
WHERE JSON_EXTRACT(data, "$.Year") in (2010,2014)
    AND JSON_EXTRACT(data, '$.Gear') IN ('"Manual"','"Auto"');

SELECT * FROM test
WHERE JSON_EXTRACT(data, "$.Year") in (2010,2014)
    AND JSON_UNQUOTE(JSON_EXTRACT(data, '$.Gear')) IN ('Manual','Auto');