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?
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.
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:
| json_date |
|---|
| 2020-01-01 |
| 2020-01-02 |
| 2020-01-03 |
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.
You can pass whereJsonContains second param as array
Model::whereJsonContains('dates',['date'=>'2018-01-01'])->get();
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();
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.
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