In this short article, we'll learn how to search in JSON array using WhereJsonContains clause.

Note: Tested on Laravel 9.48

1. WhereJsonContains Clause
2. Demo Data
3. The Query

Laravel supports querying JSON column types on databases that support JSON column types. To query a JSON column, use the -> operator. An example:

$users = DB::table('users')
                ->whereJsonContains('options->languages', 'en')
                ->get();

You can read this doc to learn more about the JSON clause.

I created a reports table and let's insert some demo data:

// report 1
$data = [
    [
        'sales' => 100,
        'product_id' => 10,
    ],
    [
        'sales' => 78,
        'product_id' => 20,
    ],
    [
        'sales' => 99,
        'product_id' => 30,
    ]
];

$report = new Report();
$report->data = json_encode($data);
$report->save();

// report 2
$data = [
    [
        'sales' => 120,
        'product_id' => 10,
    ],
    [
        'sales' => 56,
        'product_id' => 20,
    ],
    [
        'sales' => 76,
        'product_id' => 30,
    ]
];

$report = new Report();
$report->data = json_encode($data);
$report->save();

// report 3
$data = [
    [
        'sales' => 89,
        'product_id' => 10,
    ],
    [
        'sales' => 110,
        'product_id' => 20,
    ],
    [
        'sales' => 96,
        'product_id' => 30,
    ]
];

$report = new Report();
$report->data = json_encode($data);
$report->save();

In the database, it looks like this:

Now, let's try to retrieve all rows which data column contains product_id = 10.

$report = Report::query()
    ->toBase()
    ->whereJsonContains('data', ['product_id' => 10])
    ->get();
return $report;

That's it. Thank you.