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: stackoverflow.com/questions/30411210/… looks similar to what you need: SELECT * FROM jobs WHERE skills->"$.[*]" in (1,4) ? — TomFirth
– TomFirth, Commented Oct 1, 2017 at 16:59

Ravi · Accepted Answer · 2017-10-02 04:45:20Z

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 2, 2017 at 4:45

answered Oct 1, 2017 at 17:04

Ravi

31.6k44 gold badges124 silver badges180 bronze badges

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

3 Comments

Simon Lobo Over a year ago

Don't I need to use \b - but can't get it working: sqlfiddle.com/#!9/e62bd3/3

Ravi Over a year ago

@SimonLobo no. Why do you need that ?

Simon Lobo Over a year ago

Otherwise it might match a record 42 when searching for just 4

Redacted · Accepted Answer · 2017-10-01 17:06:22Z

0

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

answered Oct 1, 2017 at 17:06

Redacted

9741 gold badge9 silver badges13 bronze badges

Comments

Felipe Valdes · Accepted Answer · 2017-10-01 17:09:20Z

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!

Collectives™ on Stack Overflow

Check if column containing json string has specific value

3 Answers 3

3 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Linked

Related