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

Question

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

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

Answer 1

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

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.

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

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.

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

Just for reference, here is the documentation for the json search methods.