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"].
Table of Contents
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();
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.