Query builder filtering rows that have atleast one value from array in JSON column

Question

In Laravel 8, I'm trying to make local scope filtering of users with JSON array columns in the related table. The problem is that I don't know how to check if the JSON column contains at least one value from the array I declared at the beginning.

public function scopeViewable($query)
{
    $user = Auth::user();
    $locations = $user->getShopAccess()->pluck('id')->toArray();
    if ($user->hasPermissionTo('users.index'))
    {
        return $query->whereHas('shopAccess', function (Builder $q) {
           // return $q->whereJsonContains('location_ids', "");
           // Here is the problem I want to check if location_ids contain at least one value 
           // from $locations
        });
    }
}

Example of values that are contained in column location_ids is ["1", "2", "5"] , integer values wrapped as string

$locations variable is an array of integer values.

If it is possible to check if the location_ids column is a subset of $locations, that would be great.

Answer 1

According to this question MySQL Filter JSON_CONTAINS Any value from Array

You have 2 options. Use json_contains for each element of the array or use the newer json_overlaps function if your mysql has access to it.

return $query->whereHas('shopAccess', function (Builder $q) use ($locations) {
    $q->where(function ($where) use ($locations) {
        foreach ($locations as $location) {
            $q->orWhereJsonContains('location_ids', $location);
        }
    });
});

$locations = $user->getShopAccess()->pluck('id')->values();

return $query->whereHas('shopAccess', function (Builder $q) use ($locations) {
    $q->whereRaw('JSON_OVERLAPS(location_ids, ?)', [$locations->toJson()]);
});