Query Json (Array) column

Hi guys,

I have a json column that stores user_id in my table design. I was planning to optimise performance this way.

So the column might look something like this

[1, 3, 5, 6, 8, 9]

//I am currently using like to query.

->where('user_id','like','%'.$id.'%');

//Thinking ahead, i believe this will cause an error
user_Id: [1, 11, 21, 31, 41]

a search for user with id of 1 will return everything.
Is there any advice on how i should write my query instead?

I hope this is clear, thanks in advanced!

tisuchi

9 years ago

Level 70

If I get you properly, then you might be looking for that-

$listOfUser = [1, 3, 5, 6, 8, 9];

$users = DB::table('users')
                    ->whereIn('user_id', $listOfUser)
                    ->get();

Now you can print User object.

Ref: https://laravel.com/docs/5.3/queries#where-clauses

1 like

ralee

9 years ago

Level 5

Found the solution, in case someone needs them.

whereRaw("JSON_CONTAINS(user_id, '[3]' )")->get();

11 likes

staudenmeir

7 years ago

Level 24

Laravel 5.6.24 includes whereJsonContains():

->whereJsonContains('user_id', 3)

7 likes

Please or to participate in this conversation.