Mysql Regexp按编号选择

Question

Hi guy i have a field in the database as json field

table some_table

field|   {"number":34,"name":"john"}
field|   {"number":60,"name":"mary"}
field|   {"number":44,"name":"mark"}

I want to make a query and select data where number > some value

select from some_table where field regexp (get_number) > 40

Is this possible?

---

I found this example to make a regexp select but only matching data

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

but i want to make a select inside a json where some number inside is bigger than...

thank you all for your future help.

Answer 1

First, I want to tell you that you cannot extract regex "captures" on mysql, you can only "match"

2nd, I want to tell you that the following query will only work if "number" is the first item

SELECT * FROM some_table WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(field,',',1),':',-1) > 40

This uses SUBSTRING_INDEX of mysql to get the string before the first "," which results to: {"number":34

then get the string after the last ":" (which in this case, also the first)

that will result to:

34

edit: Here's another version that will allow other order

SELECT * FROM some_table WHERE SUBSTRING_INDEX(SUBSTRING_INDEX('{"number":34,"name":"john"}','"number":',-1),",",1) > 40

First call makes it: 34,"name":"john"} Second call makes it: 34

SUBSTRING_INDEX: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring-index