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

Question

I'm trying to use Eloquent to query a table of records in the database and only retrieve those records where at least 1 record in a json column, consisting of an array of objects, is within a given date range.

The dates column would look as follows:

[
  {'date': '2021-01-01'},
  {'date': '2021-01-02'},
  {'date': '2021-01-03'},
]

If I pass a start date of '2021-01-01' it then needs to fetch all records where dates.*.date is equal to or after this start date.

The same applies to the an end_date.

I've different kinds of syntax like:

$this->where('dates->[*]->date', '<=', date($value));
$this->where('dates->*->date', '<=', date($value));
$this->where('dates.*.date', '<=', date($value));

Nothing seems to work.

What am I doing wrong?

Answer 1

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

Answer 2

You can pass whereJsonContains second param as array

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