Laravel Eloquent query JSON column with Where In?

Question

I am having trouble querying a json column.

Previously my code was

$query->whereIn('user', $users);

Now i have changed the db type to JSON column and am trying to modify the code for the new version.

In RAW MYSQL this works

JSON_CONTAINS(user, '"tom","Bill"')

But when i put it in eloquent / php it keeps failing or returning noting. Here i am passing in an array of Users, previous using an WhereIn which works in raw SQL

$leads->whereRaw("JSON_CONTAINS(user, '"$users"')")

Any idea how to make it work in Laravel so i can pass in an array of strings, query them against the json column which contains an array of strings too.

My Json colum has data like so

["Paul", "Tom", "Bob"]

Don't you need to add it like this? $leads->whereRaw("JSON_CONTAINS(user, '" .$users ."')") — dns_nx
– dns_nx, Commented Jan 10, 2018 at 13:31
Just tied that there, gives a 500, the query executed is JSON_CONTAINS(user, 'Tom'') and it says 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 ''Tom'')' at line 1 — Logon
– Logon, Commented Jan 10, 2018 at 13:36

Jonas Staudenmeir · Accepted Answer · 2018-07-01 13:44:50Z

19

MySQL expects a JSON string:

$leads->whereRaw('JSON_CONTAINS(user, ?)', [json_encode($users)])

In Laravel 5.6.24 you can use whereJsonContains():

$leads->whereJsonContains('user', $users)

answered Jul 1, 2018 at 13:44

Jonas Staudenmeir

26.1k7 gold badges70 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

YouneL · Accepted Answer · 2018-01-10 15:25:09Z

3

If $users is array of names then you should iterate over it and add orWhereRaw condition, note that orWhereRaw can accept an array of parameter:

 $users = ["Tom", "Bob"];

 foreach( $users as $user) {
      $leads->orWhereRaw("JSON_CONTAINS(user, ?)", [$user]);
 }

 $leads = $leads->get();

answered Jan 10, 2018 at 15:25

YouneL

8,3792 gold badges30 silver badges52 bronze badges

5 Comments

Logon Over a year ago

When i run this i get back an empty array. what is the ? for in this case?

YouneL Over a year ago

? represent $user parameter, something like PDO prepared statement, you can't just concatenate $user in the DB::raw because of sql injection attacks

YouneL Over a year ago

have you tried $leads->whereRaw("JSON_CONTAINS(user, ?)", [$users]); with $users = '"tom","Bill"'

Logon Over a year ago

this sql works fine where JSON_CONTAINS(user, '"Paul"') But the code returns just an empty array foreach($users as $u){ $query->whereRaw("JSON_CONTAINS(user, ?)", [$u]); die(var_dump($query->get())); }

YouneL Over a year ago

You have to use orWhereRaw not whereRaw I think that's why you get empty array

Bennett · Accepted Answer · 2023-07-28 20:49:14Z

1

You don't necessarily have to use whereRaw. Assume data below is the json column:

$categories = ['category-one', 'category-two', 'category-three'];

Article::where('published', true)
    ->where(function ($query) use ($categories) {

        foreach ($categories as $category) {
            $query->orWhereJsonContains('data->categories', $category);
        }
    })
    ->get();

answered Jul 28, 2023 at 20:49

Bennett

7681 gold badge17 silver badges29 bronze badges

Collectives™ on Stack Overflow

Laravel Eloquent query JSON column with Where In?

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related