在 Laravel Eloquent 中高效查询 JSON 数组字段的教程

本文旨在解决 laravel eloquent 在查询 json 数组字段时遇到的路径解析问题。当需要根据 json 数组中特定索引的值进行筛选时，eloquent 的 `wherejsondoesntcontain` 或 `where` 方法可能因生成错误的 sql 路径（例如 `$."key"` 而非 `$[index]`）而失效。文章将详细解释此问题，并提供使用 `whereraw` 结合 `json_extract` 的有效解决方案，同时指出 laravel 9.0 及更高版本对此的改进。

Laravel Eloquent 查询 JSON 数组字段：问题与解决方案

在 Laravel 应用开发中，我们经常会遇到需要存储和查询 JSON 类型数据的情况。Eloquent ORM 提供了便捷的方法来处理 JSON 字段，例如 whereJsonDoesntContain 或使用 -youjiankuohaophpcn 运算符直接访问 JSON 路径。然而，当 JSON 字段存储的是数组，并且我们需要根据数组的特定索引值进行筛选时，可能会遇到意料之外的行为。

问题描述：Eloquent 对 JSON 数组路径的误解

考虑一个 Book 模型，其中包含一个 readings 字段，该字段是一个 JSON 数组，记录了每个月书籍的阅读量，例如 [1, 0, 3, 2, 5, 5, 2, 1, 3, 0, 0, 2]。我们希望查询每个月中阅读量不为零（即有阅读量）的书籍。

直观上，我们可能会尝试使用 whereJsonDoesntContain 方法，如下所示：

for ($i = 1; $i <= 12; $i++) {
    // 尝试查询第 $i-1 个月阅读量不为0的书籍
    $books = Book::whereJsonDoesntContain('readings->'. ($i - 1), 0)->get();
    // ...
}

或者使用更通用的 where 方法：

for ($i = 1; $i <= 12; $i++) {
    $books = Book::where('readings->'. ($i - 1), '!=', 0)->get();
    // ...
}

然而，这些查询在实际执行时可能无法返回预期的结果。原因是 Laravel 在内部将这些 Eloquent 调用转换为 SQL 查询时，对于 JSON 数组的索引路径处理存在偏差。

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

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

而 Book::where('readings->3', '!=', 0) 则会被转换为：

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

问题在于 SQL 的 JSON 函数（如 JSON_CONTAINS 或 JSON_EXTRACT）在访问数组元素时，需要使用 $[index] 这样的路径格式，而不是 $."key"。Laravel 默认将 -> 后面的数字视为对象键，导致生成的 SQL 路径不正确，从而无法准确查询 JSON 数组中的特定索引。

解决方案：使用 whereRaw 和 JSON_EXTRACT

为了正确地查询 JSON 数组的特定索引，我们需要绕过 Eloquent 自动生成的路径，直接使用 whereRaw 方法来构造 SQL 语句。通过 JSON_EXTRACT 函数，我们可以精确地指定数组元素的路径 $[index]。

Find JSON Path Online

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

30

查看详情

以下是修正后的查询方法：

for ($i = 0; $i <= 11; $i++) { // 月份索引从0到11
    // 使用 whereRaw 和 JSON_EXTRACT 来查询指定月份阅读量不为0的书籍
    $books = Book::whereRaw("JSON_EXTRACT(`readings`, '\$[$i]') != 0")->get();
    // ...
}

代码解释：

• for ($i = 0; $i <= 11; $i++): 循环遍历数组的索引，代表一年中的12个月份。
• Book::whereRaw(...): 允许我们直接编写原始 SQL 条件。
• JSON_EXTRACT(\readings`, '\$[$i]')`: 这是关键部分。
  • readings: 是你的 JSON 字段名。
  • '\$[$i]': 这是正确的 JSON 路径表达式。
    • \$: 在 PHP 字符串中，$ 需要转义，以确保它被视为 SQL 路径的一部分，而不是 PHP 变量。
    • [$i]: 表示访问 JSON 数组中索引为 $i 的元素。
• != 0: 筛选出该月阅读量不为零的记录。

通过这种方式，生成的 SQL 语句将是正确的，能够准确地从 JSON 数组中提取指定索引的值进行比较。

注意事项与版本兼容性

1. 数据库兼容性：JSON_EXTRACT 和 JSON_CONTAINS 等 JSON 函数是 MySQL 5.7+、PostgreSQL 9.4+ 等现代关系型数据库支持的特性。请确保你的数据库版本支持这些函数。

2. 性能考量：对 JSON 字段进行查询通常会比查询普通列的性能开销大。如果查询频率高且数据量大，可以考虑将关键数据点提取为单独的列，或者为 JSON 字段创建合适的索引（例如，MySQL 8.0+ 支持 JSON 路径索引）。

3. Laravel 9.0+ 的改进：值得注意的是，Laravel 9.0 及更高版本对 JSON 数组路径的处理进行了改进。从 Laravel 9.0 开始，你可以通过以下语法正确地访问 JSON 数组元素：

   // 在 Laravel 9.0+ 中，这将正确生成 $[3] 这样的路径
   Book::whereJsonDoesntContain('readings->[3]', 0);

   登录后复制

   这意味着在较新版本的 Laravel 中，你可能不再需要 whereRaw 来处理这种特定场景。然而，了解 whereRaw 的用法仍然是处理复杂或特定数据库函数查询时的重要技能。

总结

在 Laravel Eloquent 中查询 JSON 数组字段的特定索引时，由于 Eloquent 早期版本对 JSON 路径解析的限制，直接使用 whereJsonDoesntContain 或 where 可能会生成错误的 SQL。通过 whereRaw 结合 JSON_EXTRACT 函数，并使用正确的 '$[index]' 路径语法，可以有效地解决这个问题。对于使用 Laravel 9.0 及更高版本的开发者，框架本身已经对此进行了优化，提供了更简洁的语法支持。在选择解决方案时，应根据你的 Laravel 版本和数据库兼容性进行权衡。

以上就是在 Laravel Eloquent 中高效查询 JSON 数组字段的教程的详细内容，更多请关注php中文网其它相关文章！