Laravel: Querying JSON column containing array, expecting similar results to "whereIn"

Question

I have a database column called support_tags. This is a jsonb column containing a simple array that looks like:

[
    "caring", 
    "budgets", 
    "careers_employment", 
    "addictions"
]

I am attempting to query this column using the following:

$services = \App\Models\Service::where("status", "accepted")->whereRaw("JSON_CONTAINS(support_tags, '" . json_encode($categories) . "')")->get();

This doesn't retrieve the results I am hoping for/expecting. If I send the following array:

[
    "caring",
    "smoking"
]

The results I get back are services that contain all array elements. I need this to work more like a whereIn, in that not all array values need to be present in the database column, but one or more. If anyone knows of a way to do this I'd be very grateful. Thanks.

I'm facing same issue. All I found is people suggesting orWhereJsonContains loop. In my case, I need something more efficient because my query does not know about the values. I'm using a subquery — Zalo
– Zalo, Commented Oct 31, 2024 at 20:32

Mehrdad Gharibdoost · Accepted Answer · 2021-12-06 11:31:22Z

2

you can use these eloquent methods: ->whereJsonContains() and ->orWhereJsonContains()

your query will be like this:

$services = \App\Models\Service::where("status", "accepted")
    ->where(function($query) {
        $query->whereJsonContains('support_tags', 'smoking')
            ->orWhereJsonContains('support_tags', 'caring');
    });

edited Dec 6, 2021 at 11:31

answered Dec 6, 2021 at 11:23

Mehrdad Gharibdoost

1961 silver badge6 bronze badges

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

Comments

DannyXCII · Accepted Answer · 2021-12-06 11:31:49Z

0

Just before I accept the other answer to this question, I thought it may be useful to share my implementation of this which is based on the accepted answer This does the trick:

$categories = request()->infoAdviceCategories;

$services = [];

if (count($categories)) {
    $services = \App\Models\Service::where("status", "accepted")->whereJsonContains("support_tags", $categories[0]);

    foreach ($categories as $category) {
        if ($category !== $categories[0]) {
            $services->orWhereJsonContains("support_tags", $category);
        }
    }

    $services = $services->get();
}

return $services;

answered Dec 6, 2021 at 11:31

DannyXCII

9285 silver badges14 bronze badges

Collectives™ on Stack Overflow

Laravel: Querying JSON column containing array, expecting similar results to "whereIn"

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related