在包含JSON数组的JSON字段上进行Laravel 5.4查询 [英] Making a Laravel 5.4 query on a JSON field containing a JSON array

问题描述

我正在尝试查询包含值数组的JSON字段. 例如，我们将表命名为"用户"，并将字段命名为"朋友".这是朋友"字段的外观:

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 :

[{
    "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表上查询朋友"字段中ID等于3的所有内容.

所以类似:User::where('friends->id', 3)->orderBy('id', 'desc')->get(); 当然，如果该字段不包含数组，则上面的示例可以完美地工作，因此，如果它只是:

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 :

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

绝望的，即使我知道这不是很合逻辑，我还是尝试了"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()之类的东西.

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().

我还尝试了 JSON_CONTAINS 或 JSON_SEARCH :User::whereRaw('JSON_CONTAINS(friends->"$.id", "3")')->get()和许多不同的变体，但是什么也没做.

I have also tried with JSON_CONTAINS or JSON_SEARCH : User::whereRaw('JSON_CONTAINS(friends->"$.id", "3")')->get() and many different variants but nothing does it.

在来这里之前，我读过一些有趣的文章(列在下面)，但是我似乎是唯一将JSON数组存储在MySQL数据库中的人，这怎么可能? ^^

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 ? ^^

• 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/

因此，如果有人可以帮助我解决这个问题，我将非常感激. 旁注:我当前的MySQL版本是5.7.11，因此它确实支持JSON字段，并且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.

推荐答案

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

Your whereRaw attempt 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:

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

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

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.

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

Another option would be to build a json search string to use for JSON_CONTAINS(). For example, this query should also work:

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

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

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_SEARCH()有关的旁注中，此功能仅在您搜索字符串值时才有效.由于您的json显示ID是用整数而不是字符串表示的，因此JSON_SEARCH()将不起作用. MySQL声称这是预期的行为(错误79233 和

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 79233 and dup bug 79316).

仅供参考，此处是有关json搜索方法.

这篇关于在包含JSON数组的JSON字段上进行Laravel 5.4查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！