Laravel Eloquent search JSON array for a specific column's value-CodePudding

I have an Eloquent Book model with a readings (JSON) field. It is an array of 12 positions, each corresponding to the number of readings of each book in each month of the current year, e.g.:

[1, 0, 3, 2, 5, 5, 2, 1, 3, 0, 0, 2]

In one of my controllers, I want to select all the books with one or more readings, by month. So I have a for loop which iterates over the months. The following is my best try, but the problem is that no books are selected with that query in any iteration, although the book from the example should be returned in all iterations except 2, 10 and 11:

for ($i = 1; $i <= 12; $i  ) {
    $books = Book::whereJsonDoesntContain('readings->'. $i - 1, 0)->get();
    ...
};

I have also tried with Book::whereRaw('not json_contains(readings->'.$i-1.', 0)'); inside the for loop, with the same luck.

Any ideas? Thanks in advance.

CodePudding user response：

Your query should be:

$books = Book::whereJsonDoesntContain('readings', 0)->get();

Remove the for or any loop... readings is the field, in this case 1, 0, 3, 2, 5, 5, 2, 1, 3, 0, 0, 2...

CodePudding user response：

It seems like whereJsonDoesntContain should work – it uses JSON_CONTAINS internally – but it converts this code:

Book::whereJsonDoesntContain('readings->3', 0);

Into this query:

SELECT *
  FROM books
  WHERE NOT JSON_CONTAINS(`readings`, '0', '$."3")

The problem with this is the invalid path; Laravel assumes an object key is being passed and not an array key, whose path should look like $[3].

Similarly, a plain where() query like this:

Book::where('readings->3', '!=', 0);

Is changed to use the same invalid path.

SELECT *
  FROM books
  WHERE JSON_UNQUOTE(JSON_EXTRACT(`readings`, '$."3"')) != '0'

So what we're left with is a raw query using JSON_EXTRACT:

for ($i = 0, $i <= 11; $i  ) {
    $books = Book::whereRaw("JSON_EXTRACT(`readings`, '\$[$i]') != 0");
}

This should return the records you need; a test fiddle of this query is here

Note that this syntax:

Book::whereJsonDoesntContain('readings->[3]', 0);

Should be working in Laravel 9.0