Matheeus's avatar

Use % wildcard in whereJsonContains

Hello, in my database I have a table "profiles" with a field "info" structured in this way:

{ "skills": ["Organizzazione aziendale", "Sales Manager", "Linguistica"], "interests": ["Logistica", "Sales", "Comunicazione"] }

I'm trying to make a search into these array with this Query Builder function:

$query->whereJsonContains('info->skills', '%'.$term.'%')->orWhereJsonContains('info->interests', '%'.$term.'%');

But Laravel returns to me zero results, if I use instead this: $query->whereJsonContains('info->skills', $term)->orWhereJsonContains('info->interests', $term');

I get correct results but only if I search the entire string (like 'Comunicazione' but i got nothing if i search something like 'Comun').

How I can correctly use wildcards in whereJsonContains?

0 likes
6 replies
VinayPrajapati's avatar

In Your model

public function scopeSearch($query, $value) 
    {
        $query->orWhereRaw("lower(info) like ?", ['%"skills":"'.strtolower($value).'"%'])
        ->orWhereRaw("lower(info) like ?", ['%"interests":"'.strtolower($value).'"%'])
        ->latest();
    }

in controller

$query->search($term);
VinayPrajapati's avatar

Try direct this


$query->orWhereRaw("lower(info) like ?", ['%"skills":"'.strtolower($term).'"%'])
        ->orWhereRaw("lower(info) like ?", ['%"interests":"'.strtolower($term).'"%'])
        ->latest();
kevinm9's avatar

SELECT * FROM db0000004.collections WHERE active = true AND JSON_UNQUOTE(JSON_EXTRACT(items, '$[*].description')) LIKE '%tv%';

    if (isset($request->description)) {
        $validatedData = $request->validate([
            'description' => ['required','string', new AlphanumericwithspaceRule]
        ]);
        $description = $validatedData['description']??null;
        //only work on MySQL
        $query->whereRaw("JSON_UNQUOTE(JSON_EXTRACT(items, '$[*].description')) LIKE ?", ["%{$description}%"]);
    }
2 likes
kevinbui's avatar

The DB query by @kevinm9 seems correct. But that is very likely to be slow.

Do you have many records in that profiles table? If not, you can just get all the results and filter the records you want.

Optionally, you might want to extract those skills and interests to new DB tables.

Please or to participate in this conversation.