stabbie stabbie - 2 months ago 8
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):


[{"id":"2","value":["1","3"]},{"id":"3","value":["1","2"]}]


and:


[{"id":"3","value":["2"]},{"id":"3","value":["1","2","5"]}]


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
"id":"2"
and
"id":"3"
.

Using
%"2"%
with a simple LIKE of course matches everything. I can query by the
"id":"$var"
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

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