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

Question

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.

According to the documentation: "You may use whereJsonContains to query JSON arrays." Since you are looping, you can/should use normal where query, ->where("readings->{$i}", '>', 0). — zoispag
– zoispag, Commented Dec 5, 2021 at 22:35
Thanks @zoispag, but your answer does not work, although it does not throw any errors. $books returns empty once more. Working with JSON is differently treated as I see. — andcl
– andcl, Commented Dec 5, 2021 at 23:04

miken32 · Accepted Answer · 2021-12-06 16:36:55Z

1

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

edited Dec 6, 2021 at 16:36

answered Dec 6, 2021 at 0:31

miken32

42.5k16 gold badges127 silver badges177 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

andcl Over a year ago

Thank you @miken32, it worked :)

matiaslauriti · Accepted Answer · 2021-12-05 23:41:24Z

0

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...

answered Dec 5, 2021 at 23:41

matiaslauriti

8,2004 gold badges37 silver badges48 bronze badges

4 Comments

andcl Over a year ago

I need the for loop because apart from the $books filtering, I do some other things inside the loop...

miken32 Over a year ago

Question was looking for books read in each month, this only returns books that have been read in every month.

matiaslauriti Over a year ago

@miken32 that it is true, my bad

matiaslauriti Over a year ago

@andcl what column do you have to know when was it read? My query will work but for all books... And also, what do you need to do in the loop? You need to explain more...

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related