Laravel Eloquent 查询 JSON 数组字段中特定索引的值

本文旨在解决 Laravel Eloquent 在查询 JSON 数组字段中特定索引值时遇到的挑战。我们将深入探讨 Eloquent 默认 JSON 路径解析的局限性，特别是在处理数组索引时的不准确性，并提供两种有效的解决方案：针对 Laravel 9.0 之前版本，推荐使用 whereRaw 结合 JSON_EXTRACT 手动构建正确的 SQL 路径；对于 Laravel 9.0 及更高版本，则可利用其改进的 whereJsonDoesntContain 语法，实现更简洁的查询。

理解 Laravel Eloquent 对 JSON 数组路径的限制

在 Laravel 中，我们经常会遇到需要将数组数据存储在数据库的 JSON 字段中的场景。例如，一个 Book 模型可能有一个 readings 字段，用于存储一年中每个月的阅读量，其格式为一个包含 12 个整数的 JSON 数组，如 [1, 0, 3, 2, 5, 5, 2, 1, 3, 0, 0, 2]。

当我们需要查询特定月份（即数组的特定索引）阅读量不为零的书籍时，直观上可能会尝试使用 Eloquent 提供的 whereJsonDoesntContain 或 where 方法配合 -youjiankuohaophpcn 操作符，例如：

// 尝试查询第三个月（索引为 2）阅读量不为 0 的书籍
$books = Book::whereJsonDoesntContain('readings->2', 0)->get();
// 或者
$books = Book::where('readings->2', '!=', 0)->get();

然而，这些尝试往往无法返回预期的结果。问题在于 Laravel 在将 readings->2 这样的路径转换为 SQL 语句时，会将其错误地解析为 JSON 对象的键路径，而非 JSON 数组的索引路径。

例如，Book::whereJsonDoesntContain('readings->2', 0) 会被转换为类似于以下 SQL 查询：

SELECT * FROM books WHERE NOT JSON_CONTAINS(`readings`, '0', '$."2"')

而 Book::where('readings->2', '!=', 0) 则可能转换为：

SELECT * FROM books WHERE JSON_UNQUOTE(JSON_EXTRACT(`readings`, '$."2"')) != '0'

注意 SQL 路径中的 $"2"。这表示查询一个名为 "2" 的键，而不是数组中索引为 2 的元素。正确的 JSON 数组索引路径应该形如 $[2]。由于路径不正确，数据库无法找到匹配的元素，导致查询结果为空。

解决方案：使用 whereRaw 和 JSON_EXTRACT (适用于 Laravel 9.0 之前版本)

为了绕过 Laravel 默认的 JSON 路径解析限制，我们可以使用 whereRaw 方法，结合数据库原生的 JSON_EXTRACT 函数来手动构建正确的查询语句。JSON_EXTRACT 函数允许我们精确地指定 JSON 路径，包括数组索引。

Find JSON Path Online

Easily find JSON paths within JSON objects using our intuitive Json Path Finder

30

查看详情

以下是针对上述场景的解决方案，它在一个循环中查询每个月阅读量不为零的书籍：

for ($i = 0; $i <= 11; $i++) {
    // $i 代表月份的索引，从 0 到 11
    // 注意：在 SQL 字符串中，需要对 $ 符号进行转义，即 \$
    // 但在这个特定场景中，由于 $i 是 PHP 变量，它会被直接替换，
    // 因此路径字符串 '$\['.$i.']' 最终会生成正确的 JSON 路径，例如 '$[0]', '$[1]' 等。
    $books = Book::whereRaw("JSON_EXTRACT(`readings`, '$[{$i}]') != 0")->get();

    // 在此处处理每个月查询到的书籍
    // 例如：
    // echo "Month " . ($i + 1) . " books with readings: " . $books->count() . "\n";
}

代码解析：

• JSON_EXTRACT(\readings`, '$[{$i}]')`: 这段 SQL 片段是核心。
  • readings 是我们的 JSON 字段名。
  • '$[{$i}]' 是正确的 JSON 路径表达式，其中 {$i} 会被 PHP 循环变量替换为当前的数组索引（例如 $[0]、$[1] 等）。
• != 0: 检查提取出的值是否不等于 0。

这种方法直接利用了数据库的 JSON 函数，确保了路径的准确性，从而能够正确地查询 JSON 数组中的特定元素。

Laravel 9.0 及更高版本的改进

值得一提的是，Laravel 框架在 9.0 版本中引入了一项改进，允许在 whereJsonDoesntContain 等方法中直接使用数组索引语法。这意味着在 Laravel 9.0 及更高版本中，你可以使用更简洁、更符合 Eloquent 风格的方式来解决这个问题：

// 适用于 Laravel 9.0+
for ($i = 0; $i <= 11; $i++) {
    // 直接使用 'readings->['.$i.']' 作为路径
    $books = Book::whereJsonDoesntContain('readings->[' . $i . ']', 0)->get();

    // 在此处处理每个月查询到的书籍
}

通过在路径中使用方括号 [] 包裹索引，Laravel 9.0+ 能够正确识别这是一个 JSON 数组索引，并生成正确的 SQL 路径（例如 $[2]），从而避免了手动使用 whereRaw 的复杂性。

注意事项

1. 数据库兼容性： JSON_EXTRACT、JSON_CONTAINS 等 JSON 函数是特定于数据库的。本教程中的示例基于 MySQL 5.7 及更高版本。如果您使用 PostgreSQL，则需要使用其对应的 JSON 函数（如 ->> 或 #>> 运算符）。
2. 性能考量： 尽管 JSON 字段提供了存储灵活性的便利，但在大型数据集上频繁对 JSON 字段的内部结构进行查询（尤其是使用 whereRaw 或 JSON_EXTRACT）可能会影响查询性能。数据库通常无法对 JSON 字段的内部结构进行有效索引。如果对 JSON 数组的特定元素有高频率、高性能的查询需求，可能需要重新评估数据模型，考虑将这些特定元素规范化到单独的列或表中。
3. 数据类型： JSON_EXTRACT 提取出的值通常是字符串类型。在进行比较时，请确保数据类型匹配，或者进行适当的类型转换。在上述 != 0 的例子中，MySQL 会尝试进行隐式转换。

总结

在 Laravel Eloquent 中查询 JSON 数组字段的特定索引值，需要特别注意 JSON 路径的正确构建。对于 Laravel 9.0 之前的版本，推荐使用 whereRaw 结合数据库原生的 JSON_EXTRACT 函数，手动指定 $[$i] 这样的数组索引路径。而对于 Laravel 9.0 及更高版本，框架通过改进 whereJsonDoesntContain 等方法的路径解析能力，允许我们直接使用 readings->[$i] 这样的语法，大大简化了查询。理解这些机制和版本差异，将帮助开发者更高效、准确地处理 Laravel 中的 JSON 数据查询需求。

以上就是Laravel Eloquent 查询 JSON 数组字段中特定索引的值的详细内容，更多请关注php中文网其它相关文章！