在包含JSON数组的JSON字段上进行Laravel 5.4查询

Question

我正在尝试查询包含值数组的JSON字段。例如，我们将表命名为“ User ”，并将字段命名为“ Friends ”。这是“朋友”字段的外观：

[{
    "id": 1,
    "img": "img-1.jpg",
    "name": "Name 1"
},
{
    "id": 2,
    "img": "img-2.jpg",
    "name": "Name 2"
},
{
    "id": 3,
    "img": "img-3",
    "name": "Name 3"
}]

因此，我想 在User表上查询来自Friends字段的所有内容，其中id等于3 。

就像这样：User::where('friends->id', 3)->orderBy('id', 'desc')->get();
当然，如果该字段不包含数组，那么上面的示例可以完美地工作，所以它只是：

{
    "id": 1,
    "img": "img-1.jpg",
    "name": "Name 1"
}

绝望了，尽管我知道这是不是很符合逻辑，我试图用“其中”： User::whereIn('friends->id', [3])->get()。或类似的东西：User::where('friends->[0]->id', 3)->get()，User::where('friends->[*]->id', 3)->get()，User::where('friends->*->id', 3)->get()。

我还尝试了 JSON_CONTAINS 或 JSON_SEARCH
：User::whereRaw('JSON_CONTAINS(friends->"$.id", "3")')->get()和许多不同的变体，但没有任何作用。

在来到这里之前，我已经阅读了一些有趣的文章（下面列出了它们），但是我似乎是唯一将JSON数组存储在MySQL数据库中的人，这怎么可能？^^

• https://mattstauffer.com/blog/new-json-column-where-and-update-syntax-in-laravel-5-3/
• https://themsaid.com/laravel-myhtml" target="_blank">sql-json-colum-fast-lookup-20160709
• http://www.qcode.in/use-mysql-json-field-in-laravel/

因此，如果有人可以帮助我解决这个问题，我将不胜感激。注意事项：我当前的MySQL版本是5.7.11，因此它确实支持JSON字段，并且Laravel不会抛出任何错误，它只返回一个空数组。

Answer 1

您的whereRaw尝试非常接近。如果存储单个对象，则路径为$.id。但是，由于要存储对象数组，因此路径为$[*].id。这应该为您工作：

User::whereRaw('JSON_CONTAINS(friends->"$[*].id", "3")')->get();

该friends->"$[*].id"选择器（这是这个的快捷方式JSON_EXTRACT()）将返回的ID的JSON数组。JSON_CONTAINS()然后将检查该json数组是否包含指定的ID。

另一个选择是构建用于的json搜索字符串JSON_CONTAINS()。例如，此查询也应工作：

User::whereRaw('JSON_CONTAINS(friends, \'{"id": 3}\')')->get();

这样避免了第一次调用JSON_EXTRACT()，因此您只调用了一个json方法。我不知道哪个版本实际上会更快，或者是否会有任何差异。

另外，在与相关的旁注中JSON_SEARCH()，此功能仅在您搜索字符串值时才有效。由于您的json显示ID是用整数而不是字符串表示的，因此JSON_SEARCH()将不起作用。MySQL声称这是预期的行为（错误79233和dup错误79316）。

仅供参考，以下是json搜索方法的文档。