How to query whereIn inside json column in laravel 5.3?

Question

I have been working with a laravel 5.3 version and in a table i have a json column where i have saved average rating of a category table. so, A product category table has a column "detail" as a json data type which saves avgRating. I want to run a query inside that json column. And i want to filter that category with a average rating. So, from the frontend side rating comes in a parameter with a comma seperated so that category can be filtered with multiple ratings.

$productCategory = ProductCategory::query();
$ratings = explode(',',$params['ratings']);
$productCategory = $productCategory->whereIn('detail->avgRating',$ratings)->get();

I want to achieve something like this. I am using Postgres

Lajos Arpad · Accepted Answer · 2021-12-12 19:08:51Z

1

It turns out that there was too much uncertainty at the time when the question was asked. Once the asker separated the problems and figured out that the raw query of

DB::select(DB::raw("SELECT * FROM product_categories where detail->>'avgRating' in ('2.0','4.0')"));

works in Postgres, the asker from that point onwards had a much easier time figuring out the actual solution in the where clause. I presuppose that it was

detail->>avgRating

instead of

detail->avgRating

but from the comment section that was not yet confirmed. But the moral of the story is that whenever one has an eloquent problem that might be related to the RDBMS, then it makes a lot of sense to first sort out the raw query and then, having solid knowledge about what should be generated, at that point one can switch to the Eloquent code and apply the solution there.

answered Dec 12, 2021 at 19:08

Lajos Arpad

80.4k42 gold badges123 silver badges235 bronze badges

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

1 Comment

A4fam Over a year ago

Also, do not forget to quote like this detail->>'avgRating' other wise it will consider avgRating as separate column and throws error like undefined column

Faesal · Accepted Answer · 2021-12-13 08:27:21Z

1

You should use whereJsonContains or whereRaw:

$productCategory->whereJsonContains('detail->>avgRating',$ratings)->get();

OR

$productCategory->whereRaw('JSON_CONTAINS(detail->>avgRating, ?)', $ratings)->get();

edited Dec 13, 2021 at 8:27

answered Dec 12, 2021 at 18:02

Faesal

8377 silver badges22 bronze badges

1 Comment

A4fam Over a year ago

laravel 5.3 doesn't support "whereJsonContains" . and another query doesn't work. It says undefined column avgRating

Collectives™ on Stack Overflow

How to query whereIn inside json column in laravel 5.3?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related