Laravel Eloquent find all records where a date is within a range in JSON column

You can pass whereJsonContains second param as array

Model::whereJsonContains('dates',['date'=>'2018-01-01'])->get();

Here is a raw SQL version, I don't know Eloquent well but I'm pretty sure you can execute raw SQL.

Here is your table

CREATE TABLE MyTable(id int, dates varchar(100));
INSERT INTO MyTable(id, dates) values(1, '[{"date": "2020-01-01"},{"date": "2020-01-02"},{"date": "2020-01-03"}]');
INSERT INTO MyTable(id, dates) values(2, '[{"date": "2021-01-01"},{"date": "2021-01-02"},{"date": "2021-01-03"}]');
INSERT INTO MyTable(id, dates) values(3, '[{"date": "2022-01-01"},{"date": "2022-01-02"},{"date": "2022-01-03"}]');

You can use this SQL

SELECT * FROM MyTable 
WHERE EXISTS (
    SELECT * 
    FROM JSON_TABLE(
            JSON_EXTRACT(dates, "$[*].date")
            , "$[*]" 
            COLUMNS(jsondate date path '$'
        )
    ) as fn 
    WHERE jsondate >= '2021-06-01'
);

The parent query is actually straight forward, but the subquery is more complex. It first use the JSON_EXTRACT() function from MySQL which take JSON and a "path" to designate which values JSON to return : in our case every date field in our json objects, for example for the first row, it will return : ["2020-01-01", "2020-01-02", "2020-01-03"].

With this JSON array you can now use the JSON_TABLE() function to create a anonymous table, which also take JSON and the definition of your new table. We now have a table where one column jsondate where each row is one date, therefore we can use a simple WHERE clause to check if the date is greater than the one you want.

Example

You should try whereRaw with JSON_EXTRACT -

$this->whereRaw('JSON_EXTRACT(`dates` , "$[*].date") <= ?', [date($value)]);

I think this will work in your case, please try and let me know if it worked.

Code link - https://phpsandbox.io/n/calm-term-y1yo-upqak

to run the code please open tinker in terminal using

php artisan tinker

app(App\Http\Controllers\UserController::class)->testingJsonExtract()

For your reference - https://dev.mysql.com/doc/refman/5.7/en/json.html#json-paths

You can use multiple orWhereJsonContains() constraints

$query->where(function ($query) use ($dates) {
   foreach ($dates as $date) {
       $query->orWhereJsonContains('dates', ['date' => $date]);
   }
});

$this->whereDate("json_extract('dates', '$.date')", '>', $date)->get();

As you can see above, I used json_extract() method which is MySQL's function to extract a field from JSON column since I can not simply instruct eloquent using arrow operator.

$this->whereDate('dates->date', '>', $date)->get();

Rather I have to explicitly tell eloquent to extract json field and then proceed.

Try the following:

Model::whereJsonContains('dates', ['starts_at' => '2021-01-01'])->get();

Similarly, for dates upto a value:

Model::whereJsonContains('dates', ['ends_at' => '2021-01-01'])->get();

JSON_TABLE is probably the only (good) pure SQL way of doing this. This requires MySQL 8.0.4 or later. Here it is with Eloquent:

$this->whereExists(function($query) {
        $query->fromRaw(
            'json_table(json_extract(`dates`, "$[*].date"), "$[*]" columns(`json_date` date path "$")) as `sub`'
        )
        ->where('json_date','>=','2021-01-01');
    })->get();

Explanation:

json_extract with $[*].date as the path parameter turns the data into a regular array ["2020-01-01", "2020-01-02", "2020-01-03"])

json_table turns json data into regular sql rows:

The where clauses are applied and filters the resulting rows down to just those that have an existing json_date row in the json table.