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

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.

CodePudding user response：

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');
    });

CodePudding user response：

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;