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 *