MySQL query to search a field with JSON string
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
stabbie
Updated on June 12, 2022Comments
-
stabbie almost 2 years
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.