How to search in JSON field Laravel Eloquent

When storing an array or an object in database as a JSON string, you need a different way to search for data.

For example, we have a field called phones of Market model, and its first data is stored as ["Samsung", "Apple", "Huawei"].

Use where clause

The result can be returned with a simple where clause for a string. This method works in any database versions. To know why I mentioned database version, read other methods below.

$term = 'Samsung';
$samsungPhones = Market::where('phones', 'like', '"%.$term.%"')->get();

Use json_contains

Combining json_contains method of database and whereRaw of Eloquent model can return the data we want to retrieve.

$term = 'Apple';
$phones = Market::whereRaw('json_contains(phones, \'["' . $term . '"]\')')->get();

Or use whereJsonContains starting in Laravel 5.6.

$applePhone = Market::whereJsonContains('phones ', ["Apple"])->get();
$phones = Market::whereJsonContains('phones ', ["Samsung", "Huawei"])->get();

Two approaches only works with MySQL 5.7, PostgreSQL, SQL Server 2016, and SQLite 3.9.0.

Leave a Comment

Your email address will not be published. Required fields are marked *

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close