Check if column containing json string has specific value

Question

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]    |
----------------------------------------------

JSON means this isn't dealt with in the same way. The third answer from: https://stackoverflow.com/questions/30411210/how-to-search-json-data-in-mysql looks similar to what you need: `SELECT * FROM jobs WHERE skills->"$.[*]" in (1,4)` ? — TomFirth, Oct 01 '17 at 16:59

Ravi · Accepted Answer · 2017-10-02T04:45:20.137

2

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<<<

edited Oct 02 '17 at 04:45

answered Oct 01 '17 at 17:04

Ravi

30,829
42
119
173

Don't I need to use \b - but can't get it working: http://sqlfiddle.com/#!9/e62bd3/3 – Simon Lobo Oct 01 '17 at 17:27
@SimonLobo no. Why do you need that ? – Ravi Oct 01 '17 at 17:28
Otherwise it might match a record 42 when searching for just 4 – Simon Lobo Oct 01 '17 at 17:29

score 0 · Answer 2 · answered Oct 01 '17 at 17:06

Either of these queries will return your expected output.

Option 1

If skills is a string (varchar, text) you could use the LIKE string comparison function.

SELECT * FROM jobs WHERE skills LIKE "%1%" OR skills LIKE "%4%";

Option 2

Or, if you prefer regular expressions, you could use the REGEXP function.

SELECT * FROM jobs WHERE skills REGEXP "1|4";

score 0 · Answer 3 · answered Oct 01 '17 at 17:09

Storing JSON on the database is suitable for cases when you don't need to report or query on said fields, since it is less efficient, and doesn't take full advantage of years of query performance optimisations made by the database vendors. I advice using 2 tables, one for jobs and another for skills, and having a field called job_id on the skills table so you can simply do a query that looks like this:

select jobs.* from jobs where job.id in (select job_id in skill where skill_id in (1,4))

the skill table could later be expanded to have more fields or to be connected to other skills perhaps.

Hope that helps!

Check if column containing json string has specific value

3 Answers3

Linked