Laravel 中查询 MySQL JSON 数组字段：深度解析与类型匹配策略

本文深入探讨了在 laravel 中使用 `wherejsoncontains` 方法查询 mysql json 类型字段中嵌套对象数组的实践。重点强调了在进行 json 包含查询时，确保查询值的类型与 json 内部存储的类型严格匹配的重要性，尤其是在处理字符串和数字时，避免因类型不一致导致的查询失败，并提供了具体的解决方案和代码示例。

理解 MySQL JSON 字段与 Laravel 查询

随着现代应用对非结构化数据存储的需求日益增长，MySQL 5.7 及更高版本引入了 JSON 数据类型，允许开发者直接在数据库中存储和操作 JSON 文档。Laravel 提供了强大的 Eloquent ORM 和查询构建器，能够便捷地与这些 JSON 字段进行交互，其中 whereJsonContains 方法是查询 JSON 数组或对象中特定内容的关键工具。

假设我们有一个名为 sizes 的 JSON 字段，其结构是一个包含多个对象的数组，每个对象都有 SizeID、Sku、Value 和 stock 等属性，例如：

[
  {"SizeID":34,"Sku":null,"Value":"10","stock":2},
  {"SizeID":35,"Sku":null,"Value":"12","stock":0},
  {"SizeID":36,"Sku":null,"Value":"14","stock":0},
  {"SizeID":37,"Sku":null,"Value":"16","stock":0},
  {"SizeID":38,"Sku":null,"Value":"18","stock":0},
  {"SizeID":32,"Sku":null,"Value":"6","stock":0},
  {"SizeID":33,"Sku":null,"Value":"8","stock":1}
]

我们的目标是查询 Value 属性为特定值的记录，例如查询 Value 为 "10" 的记录。

使用 whereJsonContains 查询 JSON 数组对象

Laravel 的 whereJsonContains 方法可以用于检查 JSON 字段是否包含指定的 JSON 片段。对于上述的 JSON 结构，如果我们想查找 Value 为 "10" 的对象，可以这样构建查询：

use Illuminate\Database\Eloquent\Builder;

class SizeFilter
{
    public function filter(Builder $builder, $value): Builder
    {
        // 原始尝试，可能因类型问题失败
        // return $builder->whereJsonContains('sizes',[['Value' => $value]]);

        // 正确处理类型后的查询
        return $builder->whereJsonContains('sizes', [['Value' => (string) $value]]);
    }
}

这里 sizes 是 JSON 字段的名称，[['Value' => $value]] 是我们希望在 sizes 数组中匹配的 JSON 片段。请注意，我们传递的是一个包含单个对象的数组，因为 sizes 字段本身就是一个对象数组。

关键陷阱：JSON 值的数据类型匹配

在使用 whereJsonContains (其底层是 MySQL 的 JSON_CONTAINS 函数) 时，一个非常常见的陷阱是查询值的数据类型必须与 JSON 字段中存储的实际数据类型严格匹配。

在上面的示例 JSON 中，"Value":"10" 明确表示 Value 属性的值是一个字符串 "10"，而不是一个数字 10。

Veed Video Background Remover

Veed推出的视频背景移除工具

69

查看详情

考虑以下 MySQL 客户端的测试：

SET @j = '[{"SizeID":34,"Sku":null,"Value":"10","stock":2}]';

-- 查询 '{"Value":"10"}'，成功匹配
SELECT JSON_CONTAINS(@j, '{"Value":"10"}');
-- 结果: 1 (匹配成功)

-- 查询 '{"Value":10}'，失败，因为类型不匹配
SELECT JSON_CONTAINS(@j, '{"Value":10}');
-- 结果: 0 (匹配失败)

这个测试清楚地表明，当 JSON 内部的值是字符串时，即使其内容看起来像数字，我们也必须以字符串形式进行查询。如果 $value 变量是一个整数（例如 10），那么 [['Value' => $value]] 会被转换为 [['Value' => 10]]，这在 JSON 内部对应的是 {"Value":10}。由于原始 JSON 中存储的是 {"Value":"10"}（字符串），两者类型不匹配，导致查询失败。

解决方案：强制类型转换

为了解决这个问题，我们必须确保传递给 whereJsonContains 的 $value 始终是正确的类型。最稳妥的方法是对 $value 进行强制类型转换，使其与 JSON 字段中存储的类型一致。在我们的例子中，Value 属性存储为字符串，因此我们需要确保 $value 也是字符串：

use Illuminate\Database\Eloquent\Builder;

class SizeFilter
{
    public function filter(Builder $builder, $value): Builder
    {
        // 强制将 $value 转换为字符串，以匹配 JSON 内部的字符串类型
        return $builder->whereJsonContains('sizes', [['Value' => (string) $value]]);
    }
}

通过 (string) $value，无论 $value 原始类型是整数、浮点数还是其他，它都会被安全地转换为字符串，从而确保与 JSON 字段中的 "Value":"10" 严格匹配。

注意事项与最佳实践

1. 始终检查 JSON 字段的实际数据类型： 在进行 JSON 查询时，务必明确 JSON 字段内部存储的数据类型（字符串、数字、布尔、null）。这是避免查询失败的关键。
2. 强制类型转换的必要性： 当从外部输入（如请求参数）获取值时，它们通常是字符串。但在某些情况下，它们可能被转换为数字。为了保证查询的健壮性，根据 JSON 字段的实际存储类型进行显式类型转换是一个好习惯。
3. 性能考量： 尽管 JSON 字段提供了灵活性，但对大型 JSON 文档进行 whereJsonContains 查询可能会带来性能开销，尤其是在没有适当索引的情况下。MySQL 8.0.17 及更高版本支持 JSON 路径表达式上的函数索引，这可以在某些情况下提高查询性能。
4. JSON 路径查询： 对于更复杂的 JSON 结构和更精确的查询，可以考虑使用 whereJsonContains 的 JSON 路径语法，例如 ->> 操作符或 JSON_EXTRACT 函数配合 where 子句。然而，对于简单的包含检查，whereJsonContains 已经足够。

总结

在 Laravel 中查询 MySQL JSON 字段，特别是包含对象数组的字段时，whereJsonContains 方法是一个强大的工具。然而，成功进行查询的关键在于理解并严格匹配 JSON 内部值的数据类型。通过对查询值进行适当的强制类型转换，我们可以有效避免因类型不匹配导致的查询失败，确保数据检索的准确性和可靠性。始终记住，JSON 是类型敏感的，"10" 和 10 在 JSON 世界中是截然不同的。

以上就是Laravel 中查询 MySQL JSON 数组字段：深度解析与类型匹配策略的详细内容，更多请关注php中文网其它相关文章！