MYSQL 5.7如何查询JSON字段是否至少包含一个属性大于值的对象？

Question

在我的表中有一个具有以下值的JSON字段courses：

[
   {
      "name":"Corso1",
      "description":"Descrizione",
      "own_place_teaching":true,
      "own_place_teaching_rate":"50",
      "own_place_teaching_duration":"60",
      "home_teaching":true,
      "home_teaching_rate":"70",
      "home_teaching_duration":"60",
      "online_teaching":false,
      "online_teaching_rate":"",
      "online_teaching_duration":""
   }
]

如何选择所有有以下课程的记录：

own_place_teaching_rate大于30，

或者home_teaching_rate大于30，

或者online_teaching_rate大于30？

谢谢你的帮助

Answer 1

您的查询非常简单。

SELECT * 
  FROM your_table 
 WHERE JSON_EXTRACT(courses '$.own_place_teaching_rate') > 30
    OR JSON_EXTRACT(courses '$.home_teaching_rate') > 30 
    OR JSON_EXTRACT(courses '$.online_teaching_rate') >30;

如果使用此查询，将得到所需的结果。