Laravel中SQL高效查询JSON列：判断值是否存在于category_ids

嘿，完全理解你的需求——不想把所有数据拉出来解析再判断，直接在数据库层面搞定，这样能大大降低服务器的CPU和IO负载，毕竟数据库处理JSON的效率比应用层解析高多了。下面分几种主流数据库给你具体的实现方案：

MySQL（5.7+版本） #

MySQL从5.7开始原生支持JSON操作，用JSON_CONTAINS函数就能直接判断值是否在JSON数组里：

一维数组场景（比如[60, 59, 57]） #

SELECT * FROM Seofilter
WHERE JSON_CONTAINS(category_ids, '60', '$');

• 参数说明：$表示整个JSON文档的根路径，这里就是指整个数组；如果你的JSON是嵌套数组（比如你例子里的[[60, 59, 57]]），路径要改成$[*]，表示匹配所有内层数组：

SELECT * FROM Seofilter
WHERE JSON_CONTAINS(category_ids, '60', '$[*]');

如果是对象数组（比如[{"id":60}, {"id":59}]） #

要是你的JSON结构是带键的对象数组，就需要指定匹配的键路径：

SELECT * FROM Seofilter
WHERE JSON_CONTAINS(category_ids, '{"id":60}', '$');

PostgreSQL（9.4+版本） #

PostgreSQL的JSON支持更灵活，推荐用jsonb类型存储JSON（比普通json查询效率更高），用@>操作符就能快速判断包含关系：

jsonb类型场景 #

SELECT * FROM Seofilter
WHERE category_ids @> '[60]'::jsonb;

普通json类型场景 #

如果用的是普通json类型，可以通过json_array_elements展开数组再判断：

SELECT s.* FROM Seofilter s
WHERE EXISTS (
    SELECT 1 FROM json_array_elements(s.category_ids) AS elem
    WHERE elem::integer = 60
);

SQL Server（2016+版本） #

SQL Server 2016及以上支持JSON操作，两种方案可选：

方案1：用JSON_CONTAINS函数 #

SELECT * FROM Seofilter
WHERE JSON_CONTAINS(category_ids, '60');

方案2：用OPENJSON展开数组查询 #

如果需要更复杂的逻辑，比如同时过滤其他条件，用OPENJSON更灵活：

SELECT s.* FROM Seofilter s
CROSS APPLY OPENJSON(s.category_ids) AS j
WHERE j.value = '60';

额外优化建议 #

如果这类查询很频繁，建议做以下优化进一步降低负载：

• MySQL：可以给JSON列创建生成列（比如把数组转成逗号分隔的字符串），然后给生成列建索引；或者用JSON_TABLE结合索引（MySQL 8.0+支持）
• PostgreSQL：给jsonb类型的列建GIN索引，@>操作符可以直接利用索引加速查询
• SQL Server：可以创建JSON索引（需要SQL Server 2017+）

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