mysql用范围过滤一列中的多个json数据

Question

一个包含列的表格包含如下数据

[{"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"}]

这只是一个示例数据，我有数千行类似但值不同的行

我想对此列进行过滤，例如日期范围和数据范围

我尝试过

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

`处于查询条件但没有成功

什么正则表达式可以帮助我按范围查找值？

Answer 1

尝试一下

从表名 WHERE 字段名 REGEXP '"key_name":"([^"])key_word([^"])"' 中选择 ID；