Laravel’s Eloquent supports storing JSON data and automatically casts it for us to easily use. One of the gotchas this may present is properly searching through the contents of this data using where conditions. You’ll be happy to learn that eloquent supports searching JSON so we don’t need to write slow foreach loops to search JSON contents.
In this guide, we’ll create a ‘Car’ model + migration with a JSON column, seed some test data and search through the JSON data using Laravel Eloquent queries.
Let’s dive in!
Step 1: Create a Laravel Project
Begin by creating a new Laravel project if you haven’t done so already. Open your terminal and run:
composer create-project laravel/laravel car-data-app
cd car-data-appStep 2: Create a Model and Migration
Generate the ‘Car’ model and its migration file using Artisan:
php artisan make:model Car -mOpen the migration file and add our regular columns and a JSON column that stores unstructured car details:
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
return new class extends Migration
{
/**
* Run the migrations.
*/
public function up(): void
{
Schema::create('cars', function (Blueprint $table) {
$table->id();
$table->string('brand');
// Other columns...
$table->json('details')->nullable(); // JSON column for car details
$table->timestamps();
});
}
/**
* Reverse the migrations.
*/
public function down(): void
{
Schema::dropIfExists('cars');
}
};Step 3: Run the Migration
Run the migration to create the ‘cars’ table:
php artisan migrateStep 4: Add the Model Code
Open the ‘Car’ model file and define the ‘details’ attribute as a casted JSON field:
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class Car extends Model
{
protected $casts = [
'details' => 'json',
];
}Step 5: Seed the Database
Generate a seeder for the ‘cars’ table:
php artisan make:seeder CarSeederWithin the seeder, populate the ‘cars’ table with sample records:
<?php
namespace Database\Seeders;
use App\Models\Car;
use Illuminate\Database\Seeder;
class CarSeeder extends Seeder
{
/**
* Run the database seeds.
*/
public function run(): void
{
Car::create([
'brand' => 'Mercedes-Benz',
'details' => [
'features' => [
'color' => 'Blue',
'transmission' => 'Automatic',
// Other feature details...
],
'ownership_history' => [
[
'owner' => 'Tony Stark',
'purchase_date' => '2019-05-20',
],
[
'owner' => 'Scott Lang',
'purchase_date' => '2021-02-15',
],
// Other ownership history entries...
],
],
// Other car attributes...
]);
Car::create([
'brand' => 'Ford',
'details' => [
'features' => [
'color' => 'Black',
'transmission' => 'Manual',
// Other feature details...
],
'ownership_history' => [
[
'owner' => 'Nick Fury',
'purchase_date' => '2017-03-15',
],
[
'owner' => 'Natasha Romanoff',
'purchase_date' => '2020-06-17',
],
// Other ownership history entries...
],
],
// Other car attributes...
]);
// Create more seeded 'Car' records...
}
}
Seeding databases with more data can be done using Laravel factories. Factories allow us to generate lots of data which helps testing our application at scale. I highly recommend you check out my guide: How to Seed Multiple Rows at Once Using Laravel Factories
Step 6: Running the Seeder
Now let’s run our seeder by using the following artisan command:
php artisan db:seed --class=CarSeederStep 7: Performing a JSON Column Search Query
Now we can use Laravel Eloquent to perform searches within the ‘details’ JSON column. For testing things like this quickly I use tinker. Start tinker by running:
php artisan tinkerThen paste the following:
use App\Models\Car;
// Searching for cars with a `Blue` color
$colorMatch = Car::where('details->features->color', 'Blue')->get();
// Searching for cars that have `Nick Fury` as owner in its ownership history
$ownerMatch = Car::whereJsonContains('details->ownership_history', [['owner' => 'Nick Fury']])->get();
// Searching for cars that have `2019-05-20` as a purchase date in its ownership history
$dateMatch = Car::whereJsonContains('details->ownership_history', [['purchase_date' => '2019-05-20']])->get();You should see Laravel printing the correct matches as shown in the screenshot below:
Conclusion
That’s it! You’ve learned how to create models with JSON data and search through them, whether it’s accessing a direct property like color or using whereJsonContains to search through a data structure within the JSON, such as our ownership_history.
Tip: Read how use Eloquent to manipulate the data in JSON column here: How to Save JSON Data in Database in Laravel
Now go ahead and apply this technique to search through unstructured data in your own applications’ models. Happy coding!
References
- Eloquent Mutators: Array & JSON casting (Laravel Documentation)
- Laravel Migrations (Laravel Documentation
This entry is part 3 of 3 in the series Automatic Casting With Eloquent $casts
- How to Save JSON Data in Database in Laravel (With Example)
- How to Encrypt and Decrypt Model Data Using Casts in Laravel
- How to Search a JSON Column Using Laravel Eloquent
I’ve tried the method `whereJsonContains` in development mode for table column of type text. the content of this column was a json object.
in development mode it worked well but when I deploid the projects on shared host, this function got error! and I never figure out why this happen.
You might want to check the version on your server. When using MySQL you need at least version 5.7 to support JSON Where Clauses.
Check Laravel documentation for a full list of database servers that support searching JSON: https://laravel.com/docs/10.x/queries#json-where-clauses