对包含 JSON 数组的 JSON 字段进行 Laravel 5.4 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/48016520/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Making a Laravel 5.4 query on a JSON field containing a JSON array
提问by Victor GUTT
i'm trying to query a JSON field containing an array of values. For exemple sake we'll name the table "User" and the field "Friends". Here's how the Friends field looks like :
我正在尝试查询包含值数组的 JSON 字段。例如，我们将表命名为“ User”和字段“ Friends”。这是 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"
}]
So what I would like to do is on the User table query everything from the Friends field where there is an id equals to 3.
所以我想做的是在 User 表上查询 Friends 字段中的所有内容，其中 id 等于 3。
So something like : User::where('friends->id', 3)->orderBy('id', 'desc')->get();Of course, the exemple above works perfectly if the field did not contain an array, so if it was just :
所以就像：User::where('friends->id', 3)->orderBy('id', 'desc')->get();当然，如果该字段不包含数组，则上面的示例可以完美运行，所以如果它只是：
{
"id": 1,
"img": "img-1.jpg",
"name": "Name 1"
}
Desperate, and even though I know it's not very logical, I have tried with "whereIn" : User::whereIn('friends->id', [3])->get(). Or stuff like : User::where('friends->[0]->id', 3)->get(), User::where('friends->[*]->id', 3)->get(), User::where('friends->*->id', 3)->get().
绝望了，尽管我知道这不是很合乎逻辑，但我已经尝试过使用 "whereIn" : 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()。
I have also tried with JSON_CONTAINSor JSON_SEARCH: User::whereRaw('JSON_CONTAINS(friends->"$.id", "3")')->get()and many different variants but nothing does it.
我也尝试过JSON_CONTAINS或JSON_SEARCH:User::whereRaw('JSON_CONTAINS(friends->"$.id", "3")')->get()以及许多不同的变体，但没有任何作用。
Before coming here I have read a few interesting articles on the matter (they are listed bellow), but I seem to be the only one who have ever stored a JSON array in a MySQL database, how is that possible ? ^^
在来这里之前，我已经阅读了一些关于此事的有趣文章（它们在下面列出），但我似乎是唯一一个在 MySQL 数据库中存储过 JSON 数组的人，这怎么可能？^^
- https://mattstauffer.com/blog/new-json-column-where-and-update-syntax-in-laravel-5-3/
- https://themsaid.com/laravel-mysql-json-colum-fast-lookup-20160709
- http://www.qcode.in/use-mysql-json-field-in-laravel/
- https://mattstauffer.com/blog/new-json-column-where-and-update-syntax-in-laravel-5-3/
- https://themsaid.com/laravel-mysql-json-colum-fast-lookup-20160709
- http://www.qcode.in/use-mysql-json-field-in-laravel/
So if anyone could help me solve this problem I would really appreciate it. Side notes : my current MySQL version is 5.7.11, so it does support JSON fields and Laravel doesn't throw any errors, it just returns an empty array.
所以如果有人能帮我解决这个问题，我将不胜感激。旁注：我当前的 MySQL 版本是 5.7.11，所以它确实支持 JSON 字段并且 Laravel 不会抛出任何错误，它只是返回一个空数组。
回答by patricus
Your whereRawattempt is very close. If you were storing a single object, your path would be $.id. However, since you're storing an array of objects, your path is $[*].id. This should work for you:
你的whereRaw尝试非常接近。如果您要存储单个对象，则您的路径将是$.id. 但是，由于您存储的是一组对象，因此您的路径是$[*].id. 这应该适合你：
User::whereRaw('JSON_CONTAINS(friends->"$[*].id", "3")')->get();
The friends->"$[*].id"selector (which is just a shortcut for JSON_EXTRACT()) will return a json array of the ids. JSON_CONTAINS()will then check if that json array contains the specified id.
该friends->"$[*].id"选择器（这是这个的快捷方式JSON_EXTRACT()）将返回的ID的JSON数组。JSON_CONTAINS()然后将检查该 json 数组是否包含指定的 id。
Another option would be to build a json search string to use for JSON_CONTAINS(). For example, this query should also work:
另一种选择是构建一个 json 搜索字符串以用于JSON_CONTAINS(). 例如，这个查询也应该有效：
User::whereRaw('JSON_CONTAINS(friends, \'{"id": 3}\')')->get();
This avoids the first call to JSON_EXTRACT(), so you're only calling one json method. I do not know which version would actually be faster, or if there would be any difference.
这避免了对 的第一次调用JSON_EXTRACT()，因此您只调用了一个 json 方法。我不知道哪个版本实际上会更快，或者是否会有任何区别。
Also, on a side note related to JSON_SEARCH(), this function will only work if you are searching for string values. Since your json shows that the ids are represented as integers instead of strings, JSON_SEARCH()will not work. MySQL claims this is intended behavior (bug 79233and dup bug 79316).
此外，在与 相关的旁注中JSON_SEARCH()，此功能仅在您搜索字符串值时才有效。由于您的 json 显示 id 表示为整数而不是字符串，JSON_SEARCH()因此不起作用。MySQL 声称这是预期行为（错误 79233和dup 错误 79316）。
Just for reference, here is the documentation for the json search methods.
仅供参考，这里是 json 搜索方法的文档。
