Laravel Eloquent 查询 JSON 数组特定元素值的技巧

本文深入探讨了在 Laravel Eloquent 中查询存储为 JSON 数组的数据库字段中特定元素值的方法。针对 Laravel 9.0 之前版本，介绍了如何利用 `whereRaw` 结合 MySQL 的 `JSON_EXTRACT` 函数来正确构建查询路径。同时，也指出了 `whereJsonDoesntContain` 和 `where` 等内置方法在处理 JSON 数组索引时的局限性。对于 Laravel 9.0 及更高版本，文章展示了改进后的 `whereJsonDoesntContain` 语法，使其能直接支持 JSON 数组索引查询。

在现代 Web 开发中，将复杂数据结构以 JSON 格式存储在数据库字段中已成为一种常见做法。Laravel 的 Eloquent ORM 为查询 JSON 字段提供了便利的方法，但在处理 JSON 数组中特定索引的元素时，可能会遇到一些挑战。本文将详细介绍如何在 Laravel 中有效查询 JSON 数组的特定元素值，并提供适用于不同 Laravel 版本的解决方案。

理解问题：查询 JSON 数组元素的挑战

假设我们有一个 Book 模型，其中包含一个名为 readings 的 JSON 字段。这个字段存储了一个包含 12 个整数的数组，代表了该书在一年中每个月的阅读次数，例如：[1, 0, 3, 2, 5, 5, 2, 1, 3, 0, 0, 2]。 我们的目标是查询在特定月份（例如，第 i 个月）阅读次数不为 0 的所有书籍。

初次尝试时，开发者可能会使用 Laravel 提供的 whereJsonDoesntContain 或 where 方法，如下所示：

// 尝试一：使用 whereJsonDoesntContain
for ($i = 0; $i <= 11; $i++) {
    // 这里的 $i 对应数组索引
    $books = Book::whereJsonDoesntContain('readings->' . $i, 0)->get();
    // ...
}

// 尝试二：使用 where
for ($i = 0; $i <= 11; $i++) {
    $books = Book::where('readings->' . $i, '!=', 0)->get();
    // ...
}

然而，这些尝试往往无法返回预期的结果。问题出在 Laravel 内部将这些查询转换为 SQL 语句时，对 JSON 路径的解析不正确。

为什么内置方法会失败？

Laravel 的 whereJsonDoesntContain 方法在内部会使用 MySQL 的 JSON_CONTAINS 函数。当传入 readings-youjiankuohaophpcn3 这样的路径时，它会生成类似于 JSON_CONTAINS(readings, '0', '$."3"') 的 SQL 语句。这里的关键在于 $."3" 这个路径。对于 JSON 对象，$."key" 是正确的语法，但对于 JSON 数组，正确的路径应该是 $[index]。因此，$."3" 导致了无效的 JSON 路径，使得查询无法匹配数组元素。

同样，where('readings->3', '!=', 0) 这样的查询会被转换为使用 JSON_EXTRACT 函数，生成类似于 JSON_UNQUOTE(JSON_EXTRACT(readings, '$."3"')) != '0' 的 SQL。它也面临着同样的问题：$."3" 是一个错误的数组路径表示。

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

对于 Laravel 9.0 之前的版本，最可靠的解决方案是利用 whereRaw 方法，直接编写 SQL 查询，并使用 MySQL 的 JSON_EXTRACT 函数来构建正确的 JSON 数组路径。

JSON_EXTRACT(json_doc, path) 函数允许我们从 JSON 文档中提取数据，并且它支持正确的数组索引路径 $[index]。

Find JSON Path Online

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

30

查看详情

以下是使用 whereRaw 的正确实现示例：

for ($i = 0; $i <= 11; $i++) {
    // 构建 SQL 语句，使用 JSON_EXTRACT 和正确的数组路径 $[$i]
    // 注意：在 PHP 字符串中，需要对 $ 符号进行转义，写成 \$
    $books = Book::whereRaw("JSON_EXTRACT(`readings`, '\$[$i]') != 0")->get();

    // 可以在这里处理每个月的数据
    echo "Month " . ($i + 1) . " books with readings: " . $books->count() . "\n";
    foreach ($books as $book) {
        echo "- Book ID: " . $book->id . "\n";
    }
}

代码解释：

• JSON_EXTRACT(\readings`, '\$[$i]')`：这部分是核心。
  • ``readings```：是数据库中存储 JSON 数据的列名。
  • '\$[$i]'：是 JSON 路径表达式。
    • $：表示 JSON 文档的根。
    • [ 和 ]：表示数组索引。
    • $i：是 PHP 循环变量，代表当前的数组索引（0到11）。
    • \：在 PHP 双引号字符串中，$ 符号需要被转义，以确保它被视为 SQL 字符串的一部分，而不是 PHP 变量。如果使用单引号字符串，则不需要转义。

这个 whereRaw 查询会生成正确的 SQL 语句，例如 SELECT * FROM books WHERE JSON_EXTRACT(readings, '$[3]') != 0，从而能够准确地查询到指定月份有阅读记录的书籍。

解决方案二：改进后的 whereJsonDoesntContain (适用于 Laravel 9.0 及更高版本)

值得庆幸的是，Laravel 在 9.0 版本中对 whereJsonDoesntContain 等方法进行了改进，使其能够正确处理 JSON 数组的索引路径。从 Laravel 9.0 开始，你可以使用以下语法来指定数组索引：

// 适用于 Laravel 9.0 及更高版本
for ($i = 0; $i <= 11; $i++) {
    // 注意路径中的 '[i]' 语法
    $books = Book::whereJsonDoesntContain('readings->[' . $i . ']', 0)->get();

    // ...
}

通过在路径中明确使用 ->[index] 语法，Laravel 现在能够生成正确的 SQL 路径，例如 JSON_CONTAINS(readings, '0', '$[3]')，从而避免了之前版本的问题。

注意事项与最佳实践

1. 数据库兼容性： JSON 函数（如 JSON_EXTRACT, JSON_CONTAINS）通常在 MySQL 5.7+ 或 PostgreSQL 9.4+ 版本中可用。请确保你的数据库版本支持这些功能。
2. 性能考量： 频繁地对 JSON 字段进行复杂查询可能会影响性能，尤其是在大型数据集上。如果某个 JSON 字段的特定元素被频繁查询，可以考虑以下策略：
   • 创建虚拟列 (Virtual Columns)： MySQL 8.0+ 和 PostgreSQL 允许创建基于 JSON 字段的虚拟列，并可以对这些虚拟列进行索引，从而显著提高查询性能。
   • 数据结构优化： 如果某个 JSON 数组元素是核心业务逻辑的一部分，并且需要高性能查询，可能需要重新评估数据库设计，考虑将该数据提取到单独的列或关联表中进行存储（即数据非规范化）。
3. 可读性： 尽管 whereRaw 提供了最大的灵活性，但在 Laravel 9.0+ 版本中，推荐使用更新后的 whereJsonDoesntContain 语法，因为它更具表达性，并且符合 Eloquent 的惯用风格。

总结

在 Laravel Eloquent 中查询 JSON 数组的特定元素值，需要根据你的 Laravel 版本选择合适的策略。对于 Laravel 9.0 之前的版本，whereRaw 结合 JSON_EXTRACT 是一个强大且必要的工具，它允许你精确控制 SQL 查询中的 JSON 路径。而对于 Laravel 9.0 及更高版本，框架已经优化了内置的 JSON 查询方法，使得使用 whereJsonDoesntContain('field->[index]', value) 这样的语法变得简单直观。理解这些方法的内部工作原理和它们生成的 SQL 语句，是高效处理 JSON 字段查询的关键。

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