CODEWITHSUNDEEP
mysqljsonrange
Benn
Benn

Reputation: 5023

Mysql filter json data value range

This one is bit complicated than sinmple range in mysql , my field data is {"id":"15","value":"1200"} and I can find it by ,

SELECT * 
FROM  `my_table` 
WHERE  `my_field` 
REGEXP  '{"id":"15","value":"1200"}'

but what I need is the range for the value

so I need to look for range between 1000 > 2000 and field with json value 1200 should match. I cant redo the output with php because it will eat up the resource , so please if it is possible within Mysql without me processing the data trough php on return

any help is appreciated!

SOLUTION TO ANYONE !

LC gave us very nice solution 

REGEXP '{"id":"15","value":"(1[0-9][0-9][0-9]|2000)"}'

Upvotes: 2

Views: 1671

Answers (1)

lc.
lc.

Reputation: 116528

My knee-jerk reaction is to say "stop storing more than one value in a single column", but if you have to do it that way and you have regex power, use it (untested):

SELECT * 
FROM `my_table` 
WHERE `my_field` REGEXP '{"id":"15","value":"(1[0-9][0-9][0-9]|2000)"}'

Keep in mind, there's no way to optimize this query using indexes so you are stuck with examining every row in my_table.

Upvotes: 5

Related Questions