Mysql filter json data value range

Question

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)"}'

lc. · Accepted Answer · 2012-07-12 18:26:01Z

5

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.

edited Jul 12, 2012 at 18:26

answered Jul 12, 2012 at 18:10

lc.

117k21 gold badges163 silver badges190 bronze badges

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

5 Comments

Benn Over a year ago

im getting Got error 'repetition-operator operand invalid' from regexp

Benn Over a year ago

got it! need dot . infront ?

lc. Over a year ago

Sorry, was missing the : in the (?: for the non-capturing group. But no, you shouldn't need a dot in front, and that would probably make it interpret the curly-brace as a repetition operator.

Benn Over a year ago

for m eit works only if there is . or : INfont of ? not after

lc. Over a year ago

Right. Non-capturing groups mean nothing in mysql because there's no place to return it, so all groups are non-capturing and the (?: syntax is not implemented. stackoverflow.com/questions/7119338/…

Collectives™ on Stack Overflow

Mysql filter json data value range

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related