Matching json array from mysql table

Question

i have a column in my users database called "tags" which could look like this:

`[1,2,3,4]`

That array only has numbers.

I am now wondering if I can select from the database where the json array has specific value in it. For example:

`SELECT * FROM users WHERE tags = "HAS 1 IN ARRAY"`

I was testing by just matching the number 1 but then it will probably return values with 10 to 19 if any user has number 10-19 in that array.

Yura Ivanov · Accepted Answer · 2015-03-22 22:51:33Z

1

Try this:

SELECT * FROM users WHERE tags REGEXP '[[:<:]]1[[:>:]]';

For more info, see docs Regular Expressions.

PS I would recommend to decompose table, move tags into dependent table. On large table regexp will be slow...

answered Mar 22, 2015 at 22:51

Yura Ivanov

24213 silver badges17 bronze badges

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

1 Answer 1