如何在MySQL中查询JSON列包含指定值的数据行？

现有数据表：

需求：查询lessons列中包含"3M-232"的数据行，预期仅返回id为1的行。

当前使用的SQL语句无法得到正确结果：

SELECT * FROM merged_lesson where lessons = "3M-232";

原因是lessons是JSON数组类型，直接用等于号匹配单个元素不生效，需要根据数据库类型使用对应的JSON数组查询方法。

解决方案 #

MySQL（5.7及以上版本） #

使用JSON_CONTAINS函数检查数组是否包含目标元素：

SELECT * FROM merged_lesson WHERE JSON_CONTAINS(lessons, '"3M-232"');

注意：目标值需要用双引号包裹，符合JSON字符串的格式要求。

PostgreSQL #

推荐使用@>运算符（JSONB包含判断，性能更优）：

SELECT * FROM merged_lesson WHERE lessons::jsonb @> '["3M-232"]'::jsonb;

也可以通过展开数组进行匹配：

SELECT DISTINCT ml.* 
FROM merged_lesson ml,
     jsonb_array_elements(ml.lessons) elem
WHERE elem = '"3M-232"';

SQL Server #

通过OPENJSON解析数组后匹配：

SELECT ml.*
FROM merged_lesson ml
CROSS APPLY OPENJSON(ml.lessons) j
WHERE j.value = '3M-232';

SQLite #

使用json_each展开数组进行匹配：

SELECT DISTINCT ml.*
FROM merged_lesson ml,
     json_each(ml.lessons)
WHERE json_each.value = '3M-232';

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