stabbie stabbie - 1 year ago 85
MySQL Question

MySQL query to search a field with JSON string

This might be simple enough with basic SQL or it might need a REGEXP but I've hit a brick wall.

My data is stored in a JSON string like these two examples (each in 1 field):




I want to search for values in between those last brackets which might consist of many numbers
["1","2","5"] or just a single on ["2"]
. The beginning numbers correspond to 2 categories - the single

with a simple LIKE of course matches everything. I can query by the
to return each category then use PHP to filter it through after we have the results, but the data can be quite large and I'm sure it's easy for a SQL guru.

I don't have the option to change the format of the field, it has to remain as JSON.

Any help appreciated! Thanks.

Answer Source

I think I solved it by using this: AND extra_fields REGEXP '(.*"id":"2".*)("\[.*"1".*\]")'. It's more to do with regular expressions than it is to do with MySQL :P

comment: (I couldn't find comment button)
This syntax becomes clearer when you learn that "extra_fields" is the name of the column in the table