Select, where JSON Array contains
JSON_CONTAINS()
does exactly what you're looking for:
JSON_CONTAINS(target, candidate[, path])
Indicates by returning 1 or 0 whether a given candidate JSON document is contained within a target JSON document, or—if a path argument was supplied—whether the candidate is found at a specific path within the target. — 12.16.3 Functions That Search JSON Values
Currently, Laravel's query builder does not provide a corresponding API. There's an open internals proposal for it though.
In the meantime, you can execute a raw query:
\DB::table('users')->whereRaw(
'JSON_CONTAINS(meta->"$.colors", \'["red"]\')'
)->get();
Which would return all users that have "red" in their meta->colors
JSON field. Note that the ->
operator requires MySQL 5.7.9+.
You can also call the whereRaw()
directly on an Eloquent model.
Laravel 5.6
As of the 5.6 release, Laravel's query builder contains a new whereJsonContains
method.
I think a way would be using the like
operator:
User::where('meta->colors', 'like', '%"red"%')
However, this would only work if the values never contain the character "
and the delimiters wouldn't change.