So in Laravel 5 there's the handy thing called JSON Where Clauses using MySQL's new ability to store and fetch JSON stored in a column:
User::where('meta->colors', 'red')->get()
would return all rows, where colors
in the column meta
would be set to red
.
Now let's say colors
is not a string, but an array containing multiple colors (colors => ['red', 'blue', 'green']
).
What would be an efficient way to retrieve all rows, where colors
contains e.g. the value red
?
To query JSON data, you can use standard T-SQL. If you must create a query or report on JSON data, you can easily convert JSON data to rows and columns by calling the OPENJSON rowset function. For more information, see Convert JSON Data to Rows and Columns with OPENJSON (SQL Server).
Querying the JSON documentPostgreSQL has two native operators -> and ->> to query JSON documents. The first operator -> returns a JSON object, while the operator ->> returns text. These operators work on both JSON as well as JSONB columns. There are additional operators available for JSONB columns.
json_build_object(VARIADIC "any") Builds a JSON object out of a variadic argument list. By convention, the argument list consists of alternating keys and values.
PostgreSQL provides two native operators -> and ->> to help you query JSON data. The operator -> returns JSON object field by key. The operator ->> returns JSON object field by text.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With