Skip to content
Advertisement

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

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 = AppModelsService::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.

Advertisement

Answer

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

your query will be like this:

$services = AppModelsService::where("status", "accepted")
    ->where(function($query) {
        $query->whereJsonContains('support_tags', 'smoking')
            ->orWhereJsonContains('support_tags', 'caring');
    });    
    
User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement