brunokaue's avatar

Search in json

Hello, today i have a model with a column info the default value is null... I need search inside this column where has users, but inside the users i have array with name and email. The question is, how can i search inside users looking for name using like operator? I've tried: $query->whereJson('info->users->name', 'like', '%'.$name.'%') without success! $query->whereJson('info->users[*]->name', 'like', '%'.$name.'%') without success!

0 likes
9 replies
tisuchi's avatar

@brunokaue Does it work for you?

$query->where('info', '!=', null)
      ->whereJsonContains('info->users[*]->name', 'LIKE', '%'.$name.'%');
1 like
brunokaue's avatar

@tisuchi I get this error SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%Willian% json_contains(info, ?, '$."users"[]."name"')' at line 1 SELECT count() AS aggregate FROM interests WHERE info IS NOT NULL %Willian% json_contains(info, "LIKE", '$."users"[*]."name"')

1 like
tisuchi's avatar

@brunokaue How about this?

$query->whereRaw("JSON_CONTAINS(info->'$.users[*].name', '\"".$name."\"')");
1 like
brunokaue's avatar

@tisuchi the result now is empty $query->when($request->filled('name'), function ($query) use ($request) { return $query->whereRaw("JSON_CONTAINS(info->'$.users[*].name', '"".$request->name.""')"); })->get();

1 like
brunokaue's avatar

@tisuchi in database i have some rows that info not empty, but not have the users array....

1 like
brunokaue's avatar

I don't understanding why I can't use whereJsonContains with LIKE.

The following code is working:

public function scopeFilterByName($query, $name)
{
    return $query->where(function ($query) use ($name) {
        $query->selectRaw("json_unquote(json_extract(info, '$.\"users\"[*].\"name\"'))")
            ->whereRaw("LOWER(json_unquote(json_extract(info, '$.\"users\"[*].\"name\"'))) LIKE LOWER(?)", ['%' . $name . '%']);
    });
}

I expected the code to be simpler, something like already mentioned in the first answer.

public function scopeFilterByName($query, $name)
{
    return $query->whereNotNull('info->users')->whereJsonContains('info->users[*]->name', 'like', '%'.$name.'%');
}

@tisuchi, thank you for paying attention to this topic.

In case someone has a better solution, or explain to me why it was not possible to use the simplest code...

Thanks

jlrdw's avatar

Do yourself a favor look up some past post by @tray2 concerning not storing json.

PovilasKorop's avatar

@brunokaue if you read Laravel docs, it doesn't contain any info about the LIKE operator in JSON fields, it's not supported because it's not easy to implement. One of the reasons NOT to use JSON columns, like @jlrdw suggested.

But if you want to stick to your structure, you can find some ideas in this Laracasts thread from 4 years ago.

1 like

Please or to participate in this conversation.