Level 88
I believe it's not possible to use whereIn with JSON right now. However feel free to shoot in a PR with this functionality.
Documentation: https://laravel.com/docs/5.6/queries#json-where-clauses
1 like
Feb 18, 2018
10
Level 7
How to query json column array.
I tried doing this
// Migration
$table->json('map_data')->nullable();
// Map data is
map_data = {
'bands' => ['1', '2'],
}
// Query
$query->whereIn('map_data->bands', ['1']);
I'm getting this error
QLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'bands,
Level 7
Made it work by doing this
$arr = ['1', '2'];
$arr = (string) json_encode($arr);
$arr = "'" . $arr . "'";
$query->whereRaw('JSON_CONTAINS(map_data->"$.band",'. $arr.')');
What's the better to write this?
4 likes
Level 1
You have always shown us the possibilities that were offered to us and your love has guided us. You're very special.
Level 24
Laravel 5.6.24 includes whereJsonContains():
$query->whereJsonContains('map_data->band', ['1', '2'])
7 likes
Level 21
I came across this thread and was wondering how do you guys query this if the column name is users and the value is
[
{
"user_id": "1"
},
{
"user_id": "2"
}
]
whereJsonContains('users', 1) doesn't work though
typically it should have this value
[1, 2]
and this query
`whereJsonContains('users', 1)`
or
{
'user_ids': [
1, 2
]
}
and this query
`whereJsonContains('users->user-ids', 1)`
this one works.
but how do you query it if it's like that in the above example.
Level 24
@skeith22 Use this:
$query->whereJsonContains('users', [['user_id' => 1]])
7 likes
Level 21
I'll try this. thanks @staudenmeir
Level 1
Thanks @staudenmeir !
Level 7
HI, i have a "filters" field with this data
{
"services": [{
"price": 150,
"filter_id": 5
}, {
"price": "",
"filter_id": 6
}, {
"price": 300,
"filter_id": 8
}, {
"price": 200,
"filter_id": 10
}
]
}
$search = [5,10];
$q->whereJsonContains('filters->services', [['filter_id', $search]]);
i have an empty result
how can i do?
Level 73
@aldoZumaran You start with opening you own thread, and not highjack an already solved one. Other than that, don't use json inside the database, it's bad practice.
Please or to participate in this conversation.