mysql filter multipal json data in one column with range

Question

one table with column contain data like follow

[{"id":"1","value":"addy"},{"id":"2","value":"fname"},{"id":"3","value":"lname"},{"id":"4","value":"kfjsdkfj"},{"id":"5","value":"1985-12-02"},{"id":"6","value":"20"},{"id":"7","value":"20"},{"id":"8","value":"2"},{"id":"9","value":"20"},{"id":"10","value":"20"},{"id":"11","value":"20"},{"id":"12","value":"20"},{"id":"13","value":"1"},{"id":"14","value":"kljfk"},{"id":"15","value":"2"},{"id":"16","value":"1"},{"id":"17","value":"2"},{"id":"18","value":"3"},{"id":"19","value":""},{"id":"20","value":"1"}]

this is only one example data , i have thousand of row like same with different value

i want filter on this column like date range and data range

i tried in

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

` in condition of query but it not work out

what regular expression that help me to find value by range ?

i dont think this is a good datamodel. I understand you want to store dynamic data, but storing json like this limits your search conditions. Although you can get it to work, it will never be that fast. I would suggest that you add the date fields as columns, so you can filter on them. I would add the generic fields as columns, and store then the dynamic fields as json. Or use a NoSQL database (of possible) like mogoDB. — Roger
– Roger, Commented Aug 15, 2012 at 7:41
@Rogier thank you to show me right way , but the problem is data is already in database and now i have to filter the data :( — Hardik Raval
– Hardik Raval, Commented Aug 15, 2012 at 8:31
how you access the data, php / ruby? a simple procedure that loops over all the records might be easier. Or you could do that with a stored-procedure. Is it a one time thing? — Roger
– Roger, Commented Aug 15, 2012 at 8:35
i access data using php , yeah that gud idea i will try it :) — Hardik Raval
– Hardik Raval, Commented Aug 15, 2012 at 8:40
if you found a good solution, please post it here as your own answer. Then this question does not keep popping up as unanswered. — Roger
– Roger, Commented Aug 20, 2012 at 18:18

Valentino · Accepted Answer · 2015-08-04 21:55:25Z

1

Try it

SELECT id FROM table_name WHERE field_name REGEXP '"key_name":"([^"])key_word([^"])"';

answered Aug 4, 2015 at 21:55

Valentino

1651 silver badge3 bronze badges

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

Collectives™ on Stack Overflow

mysql filter multipal json data in one column with range

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related