Check whether any values in array match any values in json column

Question

I have a problem of checking whether any values in an array match any values in json column which contains an array with a name. suppose i have an array [25,36,45,52] and json column is {"values": [25,24,15]}. I want to check whether any values in array match any of values in json column in xampp mysql. please provide a better solution of doing this. this image show table structure of my database

i have 4 tables.

user
profile
profile
jobs

user table (id,userid)
jobs table (id,user_id,skill_id)
skill table (id,job_id,)
profile table (id,user_id)

now i want to search all jobs that match some or at least one skills. i have tried with this but this is giving all jobs with out skills filtered.

$jobs = Job::with(['user','profile'])->with(['skills' => function($query){
$query->whereJsonContains('skills->skills',[35]);
}])->where('jobs.is_completed',0);

please help me.

i have not tried yet but i am not getting how to do this. please provide some solution — Deepak Dawson
– Deepak Dawson, Commented Sep 12, 2019 at 15:50

Ahmed Atoui · Accepted Answer · 2019-09-12 16:20:20Z

1

you can use where Clause easily for example you would like to get rows that match skills 35,54:

$users = DB::table('table')
                -> whereJsonContains('skills->skills', [35,54])
                ->get();

for more details about how to querying json column check official docs :

https://laravel.com/docs/5.8/queries#json-where-clauses

answered Sep 12, 2019 at 16:20

Ahmed Atoui

1,5661 gold badge10 silver badges11 bronze badges

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

3 Comments

Deepak Dawson Over a year ago

But with multiple values it reaches with multiple values like AND condition but I want search Like OR condition. please provide a solution

Ahmed Atoui Over a year ago

if you want complicated where and some advanced filter i advise you using relations with eloquent models feature it's easy to implement

Deepak Dawson Over a year ago

hello, i did some edit in post. can you please help me now.

Collectives™ on Stack Overflow

Check whether any values in array match any values in json column

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related