本文深入探讨了在 Laravel Eloquent 中查询 JSON 数组字段中特定索引值的问题。针对 `whereJsonDoesntContain` 和普通 `where` 方法在处理 JSON 数组路径时的局限性，提供了使用 `whereRaw` 结合 MySQL 的 `JSON_EXTRACT` 函数的有效解决方案。同时，文章也指出了 Laravel 9.0 及更高版本对 JSON 数组路径查询的改进，为开发者提供了更简洁的语法。
理解 Laravel Eloquent 查询 JSON 数组的挑战
在 Laravel 应用中，将复杂数据结构（如数组）存储在数据库的 JSON 字段中是一种常见做法。然而，当需要查询 JSON 数组中特定索引位置的值时，开发者可能会遇到一些挑战。例如，假设我们有一个 Book 模型，其中包含一个 readings 字段，它是一个存储每月阅读次数的 JSON 数组，格式如下：[1, 0, 3, 2, 5, 5, 2, 1, 3, 0, 0, 2]。现在，我们希望筛选出特定月份阅读次数不为零的图书。
直观地，我们可能会尝试使用 Laravel 提供的 whereJsonDoesntContain 或简单的 where 方法：
// 尝试使用 whereJsonDoesntContain
for ($i = 1; $i <= 12; $i++) {
$books = Book::whereJsonDoesntContain('readings->'. $i - 1, 0)->get();
// ...
}
// 尝试使用 whereRaw (与 whereJsonDoesntContain 内部实现类似)
for ($i = 1; $i <= 12; $i++) {
$books = Book::whereRaw('not json_contains(readings->'.$i-1.', 0)')->get();
// ...
}
// 尝试使用简单的 where
Book::where('readings->3', '!=', 0)->get();然而，这些尝试可能无法返回预期的结果。问题在于 Laravel 在内部将 readings->3 这样的路径转换为 $."3"。这种语法适用于查询 JSON 对象中的键，但不适用于 JSON 数组中的索引。对于 JSON 数组，正确的路径语法应该是$[3]`。
解决方案：使用 whereRaw 结合 JSON_EXTRACT
为了正确地查询 JSON 数组中的特定索引值，我们需要利用数据库底层的 JSON 函数。MySQL 提供了 JSON_EXTRACT 函数，它允许我们通过正确的路径语法提取 JSON 文档中的元素。结合 Laravel 的 whereRaw 方法，我们可以构建出满足需求的查询：
use App\Models\Book;
// 循环遍历月份（0-11 代表一年中的12个月）
for ($i = 0; $i <= 11; $i++) {
// 构建查询，使用 JSON_EXTRACT 提取指定索引的值，并检查其是否不等于0
$books = Book::whereRaw("JSON_EXTRACT(`readings`, '\$[$i]') != 0")->get();
// 在这里处理 $books 集合，例如：
echo "Month " . ($i + 1) . " books with readings: " . $books->count() . "\n";
foreach ($books as $book) {
// var_dump($book->title);
}
}代码解释：
- for ($i = 0; $i
- Book::whereRaw(...): 允许我们直接写入原始的 SQL 查询条件。
- JSON_EXTRACT(\readings`, '\$[$i]')`: 这是关键部分。
- ``readings```: 是数据库表中的 JSON 列名。
- '\$[$i]': 是 JSON_EXTRACT 函数的路径参数。
- $ 表示 JSON 文档的根。
- [ 和 ] 用于指定数组索引。
- $i 是循环变量，代表当前的数组索引。
- 请注意，由于 $i 是 PHP 变量，我们需要确保在 SQL 字符串中正确地拼接它。在双引号字符串中，$ 符号需要转义（\$），以避免 PHP 将其误认为是变量。
- != 0: 检查提取出的值是否不等于 0。JSON_EXTRACT 返回的值通常是字符串形式，MySQL 会在比较时进行类型转换。
这个 whereRaw 查询会生成类似 SELECT * FROM books WHERE JSON_EXTRACT(readings, '$[0]') != 0 的 SQL 语句，从而正确地从 JSON 数组中筛选出符合条件的记录。
Laravel 9.0 及更高版本的改进
值得注意的是，Laravel 框架在 9.0 版本中对 JSON 数组路径的查询支持进行了改进。从 Laravel 9.0 开始，whereJsonDoesntContain 等方法应该能够识别 readings->[3] 这样的数组路径语法。
// 适用于 Laravel 9.0 及更高版本
Book::whereJsonDoesntContain('readings->[3]', 0)->get();这意味着在较新版本的 Laravel 中，开发者可以使用更简洁、更符合 Eloquent 风格的语法来查询 JSON 数组，而无需依赖 whereRaw。然而，对于使用旧版本 Laravel 或需要兼容更广泛数据库环境的情况，whereRaw 结合 JSON_EXTRACT 仍然是可靠的通用解决方案。
注意事项与最佳实践
- 数据库版本兼容性： JSON_EXTRACT 和其他 JSON 函数在 MySQL 5.7+、PostgreSQL 9.3+ 等现代关系型数据库中得到支持。请确保您的数据库版本符合要求。
-
性能考虑： 对 JSON 字段进行查询，特别是涉及到 JSON_EXTRACT 等函数时，可能会比查询普通列的性能开销更大。数据库通常无法直接索引 JSON 字段的内部结构。如果您的应用需要频繁地查询 JSON 数组中的特定元素，并且数据量庞大，您可能需要考虑以下替代方案：
- 冗余存储： 将需要频繁查询的 JSON 数组元素提取到单独的数据库列中。
- 数据模型优化： 考虑将 JSON 数组中的每个元素建模为单独的关联表记录，实现数据规范化。例如，为每本书的每月阅读次数创建一个 book_readings 表。
- 路径转义： 在使用 whereRaw 构建 SQL 字符串时，务必注意路径中的特殊字符（如 $, [, ]）的转义，以防止 SQL 注入或解析错误。在 PHP 的双引号字符串中，$ 需要用反斜杠转义。
- 类型转换： JSON_EXTRACT 返回的值通常是字符串。在进行比较操作时，数据库会尝试进行隐式类型转换。如果需要严格的类型比较，可以使用 CAST 或 CONVERT 函数显式转换类型。
总结
在 Laravel Eloquent 中查询 JSON 数组的特定索引值，需要理解其底层 SQL 实现和路径语法。对于旧版 Laravel 或需要通用解决方案的场景，通过 whereRaw 结合 JSON_EXTRACT 和正确的数组路径（'$[index]'）是高效且可靠的方法。对于 Laravel 9.0 及更高版本，框架提供了更友好的 readings->[index] 语法支持，简化了开发流程。在选择查询策略时，应综合考虑数据库版本、性能需求和代码可维护性。
