本教程详细阐述了在 laravel 中如何高效地查询包含数组的 json 列，以实现类似 sql `where in` 的匹配逻辑。针对 `json_contains` 默认匹配所有元素的行为，文章介绍了利用 eloquent 的 `wherejsoncontains` 和 `orwherejsoncontains` 方法，结合查询闭包，精确检索数据库中 json 数组列包含指定列表中任意一个或多个值的记录。
引言：理解 JSON 数组列的查询挑战
在现代 Web 应用中，将结构化数据存储在数据库的 JSON（或 JSONB）列中变得越来越普遍。例如，一个 support_tags 列可能存储一个简单的 JSON 数组，如 ["caring", "budgets", "careers_employment", "addictions"]，用来表示服务的支持标签。
当我们需要查询这些 JSON 数组列时，一个常见的需求是找到那些 support_tags 数组中包含我们指定列表中的任意一个或多个标签的服务，而不是必须包含所有标签。这类似于 SQL 中的 WHERE column IN ('value1', 'value2') 操作。
然而，直接使用 whereRaw("JSON_CONTAINS(support_tags, '" . json_encode($categories) . "')") 这样的语句，当 $categories 是一个数组时，通常会匹配 support_tags 字段中包含 $categories 数组中所有元素的记录。这与我们期望的“任一匹配”行为不符。
Laravel Eloquent 解决方案：whereJsonContains 与 orWhereJsonContains
Laravel Eloquent 提供了 whereJsonContains 方法，专门用于查询 JSON 列是否包含某个特定值。结合 orWhereJsonContains 和查询闭包（where(function($query) { ... })），我们可以优雅地实现“任一匹配”的逻辑。
whereJsonContains($column, $value) 方法用于检查指定 JSON 列中是否包含 $value。这里的 $value 可以是字符串、数字、布尔值，甚至是 JSON 对象或数组。当 $column 存储的是 JSON 数组时，它会检查数组中是否存在 $value。
要实现“任一匹配”，我们需要为每个待匹配的标签构建一个 OR 条件。这正是查询闭包的作用：它允许我们将多个 WHERE 或 OR WHERE 条件组合在一起，形成一个独立的逻辑组。
示例代码
假设我们有一个 Service 模型，其中包含 support_tags JSONB 列和 status 字符串列。我们希望找到所有状态为 accepted，并且 support_tags 包含 ['caring', 'smoking'] 中任意一个标签的服务。
where(function ($query) use ($categoriesToMatch) {
// 确保 $categoriesToMatch 不为空，避免生成空查询条件
if (empty($categoriesToMatch)) {
// 如果没有类别需要匹配，可以添加一个永不为真的条件，
// 以确保此部分查询不返回任何结果，或根据业务逻辑调整。
$query->whereRaw('1 = 0'); // 示例：确保不匹配任何记录
return;
}
// 遍历待匹配的类别，使用 orWhereJsonContains 构建 OR 条件
foreach ($categoriesToMatch as $index => $category) {
if ($index === 0) {
// 第一个条件使用 whereJsonContains 启动 OR 组
$query->whereJsonContains('support_tags', $category);
} else {
// 后续条件使用 orWhereJsonContains
$query->orWhereJsonContains('support_tags', $category);
}
}
})
->get();
// 此时 $services 包含所有状态为 'accepted' 且 support_tags 包含 'caring' 或 'smoking' 任意一个的服务。
return $services;
}
}代码解析
- Service::where("status", "accepted"): 这是标准的 Eloquent 查询，用于筛选 status 字段为 accepted 的服务。
- ->where(function ($query) use ($categoriesToMatch) { ... }): 这是一个关键步骤。它创建了一个查询闭包，将内部的条件组合成一个逻辑单元。在生成的 SQL 中，这通常会被转换为 WHERE (...)，确保内部的 OR 条件不会与外部的 AND 条件混淆。use ($categoriesToMatch) 语句允许闭包访问外部定义的 $categoriesToMatch 数组。
- if (empty($categoriesToMatch)) { ... }: 这是一个健壮性检查。如果待匹配的类别列表为空，我们通过 whereRaw('1 = 0') 添加一个永不为真的条件，确保不返回任何服务，而不是匹配所有服务（这可能在没有条件时发生）。
- foreach ($categoriesToMatch as $index => $category): 我们遍历 $categoriesToMatch 数组中的每个类别。
-
if ($index === 0) { $query->whereJsonContains('support_tags', $category); } else { $query->orWhereJsonContains('support_tags', $category); }: 这是构建 OR 逻辑的核心。
- 对于数组中的第一个类别，我们使用 whereJsonContains。
- 对于数组中的后续类别，我们使用 orWhereJsonContains。
- 这种模式确保了生成的 SQL 语句类似 WHERE (support_tags JSON_CONTAINS 'caring' OR support_tags JSON_CONTAINS 'smoking')，从而实现了“任一匹配”的需求。
工作原理深入解析
whereJsonContains 方法在底层会根据你使用的数据库驱动生成相应的 SQL 函数。
- MySQL (5.7+): 它会使用 JSON_CONTAINS(json_column, 'value')。例如，whereJsonContains('support_tags', 'caring') 会生成 JSON_CONTAINS(support_tags, '"caring"')。注意，这里 'caring' 会被自动包装成 JSON 字符串 '"caring"'。
- PostgreSQL: 它会使用 json_column ? 'value' 操作符。例如，whereJsonContains('support_tags', 'caring') 会生成 support_tags ? 'caring'。
通过在查询闭包中组合多个 whereJsonContains 和 orWhereJsonContains，我们实际上是在告诉数据库：“查找 support_tags 列包含 'caring' 或者 包含 'smoking' 的记录”。
注意事项与最佳实践
-
数据库兼容性: 确保你的数据库版本支持 JSON 数据类型及其相关的查询函数。
- MySQL 5.7 或更高版本。
- PostgreSQL 9.4 或更高版本（推荐使用 JSONB 类型以获得更好的性能）。
-
性能优化: 对于包含大量数据的 JSON 列，直接查询可能会影响性能。
- PostgreSQL: 考虑为 JSONB 列创建 GIN 索引。例如：CREATE INDEX idx_support_tags ON services USING GIN (support_tags); 这将显著加速 ? (contains) 操作。
- MySQL 8.0+: 可以创建表达式索引。例如：CREATE INDEX idx_support_tags ON services ((CAST(support_tags AS CHAR(255) ARRAY))); 或者更具体的函数索引，但通常 GIN 索引在 PostgreSQL 中更直接有效。
- 动态条件处理: 示例代码展示了如何动态地构建查询条件，这对于处理用户输入或配置的标签列表非常有用。
- 安全性: 使用 whereJsonContains 方法比手动拼接 whereRaw 语句更安全，因为它会自动处理参数绑定，有效防止 SQL 注入攻击。
- 嵌套 JSON: whereJsonContains 也可以用于查询嵌套 JSON 结构中的值，只需使用点符号指定路径即可，例如 whereJsonContains('settings->preferences->theme', 'dark')。
总结
在 Laravel 中查询包含数组的 JSON 列以实现类似 WHERE IN 的“任一匹配”逻辑，最佳实践是利用 Eloquent 的 whereJsonContains 和 orWhereJsonContains 方法，并将其封装在一个查询闭包中。这种方法不仅提供了清晰、可读的代码，而且通过 Eloquent 的抽象层，确保了跨数据库的兼容性，并自动处理了安全问题。通过结合适当的数据库索引，可以进一步优化查询性能，从而构建高效且健壮的 Laravel 应用。
