Level 8
Can you modify how this is stored? So that you can remove the [ ] ?
Dec 28, 2017
9
Level 11
Making a Laravel 5.4 query on a JSON field containing a JSON array
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 ? ^^
- 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.
Level 11
My question was answered on Stackoverflow. Here's two working solutions :
-
User::whereRaw('JSON_CONTAINS(friends->"$[*].id", "3")')->get(); -
User::whereRaw('JSON_CONTAINS(friends, \'{"id": 3}\')')->get();
The full answer is available here : https://stackoverflow.com/questions/48016520/making-a-laravel-5-4-query-on-a-json-field-containing-a-json-array
Thank you @xmarks for trying to help.
2 likes
Level 1
I had a similar issue that I resolved by keying my JSON. In this example, I would have a user with user.friends =
{
"108" : {
"img": "laisha.jpg",
"name": "LAISHA LANGWORTH"
},
"95" : {
"img": "profile.jpg",
"id": "AMELY OBERBRUNNER"
},
"637" : {
"img": "orieeee.png",
"name": "ORIE HAMMES"
}
}
I can then retrieve all friends of Amely with User::where('friends->95', '!=', 'null')->get() or "friends->{$id}" or whatever.
Hope that helps someone.
1 like
Level 11
Looks like a nice workaround, I did not think of that. But the data I was getting was coming from an external Api anyway so I pretty much went with the way it was designed.
Thank you for giving us an other approach to it though.
Level 8
I got a similar problem. Here is my case:
Inside users table I have a json column named "agencies" that stores data as a simple array like this:
[
"0eb2edf0-50cb-44ff-a0a6-b2a104a9dc12",
"f7c748d4-8718-441e-aa69-91b890ead5ed"
],
the above is valid json. When I try to select all users that contain 0eb2edf0-50cb-44ff-a0a6-b2a104a9dc12 I get null
Is my query correct?
$users = User::whereRaw('JSON_CONTAINS(agencies->"$[*]", "0eb2edf0-50cb-44ff-a0a6-b2a104a9dc12")')->get();
is the below correct way to do write JSON select query considering how I store uuids as an array inside agencies column which is defined as json?
'JSON_CONTAINS(agencies->"$[*]", "0eb2edf0-50cb-44ff-a0a6-b2a104a9dc12"
I looked at the original stackoverflow answer posted above and modified it to this agencies->"$[*]" but it does not seem to be working for me.
Using Laravel 5.4
I'd appreciate if you guys had any ideas what may be wrong in my case?
Level 11
Hm... just a quick look, but maybe something similar to the following will do the trick : User::whereRaw('JSON_CONTAINS(agencies, \'{"0eb2edf0-50cb-44ff-a0a6-b2a104a9dc12"}\')')->get();.
Since you posted your question about 2hrs ago, have you found a working solution yet ?
Level 4
@nikocraft actually your JSON data is not formatted correctly, you can store it in the following way
//for single object
{
"0eb2edf0-50cb-44ff-a0a6-b2a104a9dc12"
}
//for multiple object
[
{"0eb2edf0-50cb-44ff-a0a6-b2a104a9dc12"},
{"0eb2edf0-50cb-44ff-a0a6-b2a104a9dc12"},
{"0eb2edf0-50cb-44ff-a0a6-b2a104a9dc12"}
]
Level 7
If you are only storing UUIDs in that format, then why not just do a like search and include the uuid and double quotes?
$users = User::where('agencies', 'like', '%"0eb2edf0-50cb-44ff-a0a6-b2a104a9dc12"%')->get();
and yes @Shahrukh4 I agree it is formatted incorrectly for multiple entries.
Level 4
@MARKLL like queries will slow it down. Try something that retrieve data faster. but yeah like queries will work.
Please or to participate in this conversation.