dongsui4658 2014-06-26 22:06
浏览 26
已采纳

Mysql Regexp按编号选择

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.

  • 写回答

2条回答 默认 最新

  • doukucai0099 2014-06-26 22:15
    关注

    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

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥30 dbLinq最新版linq sqlite
  • ¥20 对D盘进行分盘之前没有将visual studio2022卸载掉,现在该如何下载回来
  • ¥15 完成虚拟机环境配置,还有安装kettle
  • ¥15 2024年全国大学生数据分析大赛A题:直播带货与电商产品的大数据分析 问题5. 请设计一份优惠券的投放策略,需要考虑优惠券的数量、优惠券的金额、投放时间段和投放商品种类等因素。求具体的python代码
  • ¥15 有人会搭建生鲜配送自营+平台的管理系统吗
  • ¥15 用matlab写代码
  • ¥30 motoradmin系统的多对多配置
  • ¥15 求组态王串口自定义通信配置方法或代码?
  • ¥15 实验 :UML2.0 结构建模
  • ¥20 用vivado写数字逻辑实验报告撰写,FPGA实验