Vic-Gutt
1 year ago

Making a Laravel 5.4 query on a JSON field containing a JSON array

Posted 1 year ago by Vic-Gutt

Hi everyone, 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"
}]

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.

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"
}

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

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.

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

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.

Please sign in or create an account to participate in this conversation.