How to Search a JSON Column Using Laravel Eloquent

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-app

Step 2: Create a Model and Migration

Generate the ‘Car’ model and its migration file using Artisan:

php artisan make:model Car -m

Open the migration file and add our regular columns and a JSON column that stores unstructured car details:

database/migrations/2023_12_10_151512_create_cars_table.php
<?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 migrate

Step 4: Add the Model Code

Open the ‘Car’ model file and define the ‘details’ attribute as a casted JSON field:

app/Models/Car.php
<?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 CarSeeder

Within the seeder, populate the ‘cars’ table with sample records:

database/seeders/CarSeeder.php
<?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=CarSeeder

Step 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 tinker

Then 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:

Laravel Tinker Printing the Results of Our Eloquent Searches in the JSON Data Column

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

This entry is part 3 of 3 in the series Automatic Casting With Eloquent $casts

  1. How to Save JSON Data in Database in Laravel (With Example)
  2. How to Encrypt and Decrypt Model Data Using Casts in Laravel
  3. How to Search a JSON Column Using Laravel Eloquent

Johan van den Broek

Johan is the creator of laracoding.com. As a child, he began tinkering with various programming languages, many of which have been long forgotten today. Currently, he works exclusively with PHP, and his passion for programming remains to this day.

2 thoughts on “How to Search a JSON Column Using Laravel Eloquent

  1. 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.

    1. 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

Leave a Reply

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

Recent Posts