Laravel Search in JSON Array Object using WhereJsonContains

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

Note: Tested on Laravel 9.48

Table of Contents

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

WhereJsonContains Clause

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.

Demo Data

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:

 

The Query

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.