Simon Lobo Simon Lobo - 3 years ago 212
JSON Question

Check if column containing json string has specific value in mysql

I have a 'jobs' table like this:

----------------------------------------------
|job_id |name |skills |
----------------------------------------------
|1 |Job 1 |[1] |
|2 |Job 2 |[2,3] |
|3 |Job 3 |[4,5,6] |
----------------------------------------------


The 'skills' column contains a JSON array.

I need to select jobs where one or more skills are met - something like this (but obviously won't work):

SELECT * FROM jobs WHERE skills IN (1,4)


Should return:

----------------------------------------------
|job_id |name |skills |
----------------------------------------------
|1 |Job 1 |[1] |
|3 |Job 3 |[4,5,6] |
----------------------------------------------

Answer Source

You could use REGEXP

select * from jobs where skills REGEXP '[[:<:]]1[[:>:]]|[[:<:]]4[[:>:]]'

Append, [[:<:]] before and [[:>:]] after, to match exact value and | to match for multiple values.

>>>Demo<<<

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download