MySQL query to search a field with JSON string

Question

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.

Community · Accepted Answer · 2011-12-01 08:59:15Z

14

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

edited Dec 1, 2011 at 8:59

CommunityBot

11 silver badge

answered Mar 5, 2011 at 10:02

stabbie

3751 gold badge4 silver badges13 bronze badges

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

2 Comments

r3wt Over a year ago

would you be able to help me out on a similar issue? '"(.*\"Reticle.*\":\"*.'.$reticle2.'.*\")"' is what my REGEXP looks like and its a parameter to bind :reticle. The query works but no results.

Oddman Over a year ago

Just want to add that these sorts of queries can't be indexed, so if you're using this is in a high-traffic website, such implementations can result in big performance hits.

i3arnon · Accepted Answer · 2014-06-22 22:42:52Z

0

In SQL it is really simple, because you only need to identify the last opening bracket. This can you do with substring index.

Maybe your table called test, and your field with the string called text - this should work

select text from (select substring_index(text,'[',-1) as text from test) as new where text like '%5%'**

If you have a text between 2 unique delimiters, maybe u find this tutorial helpful

edited Jun 22, 2014 at 22:42

i3arnon

117k33 gold badges334 silver badges358 bronze badges

answered Jun 22, 2014 at 22:21

chris2k

1691 silver badge2 bronze badges

Collectives™ on Stack Overflow

MySQL query to search a field with JSON string

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related