创建测试表

CREATE TABLE `sys_log` (

  `id` int(11) NOT NULL AUTO_INCREMENT,
  `message_type` varchar(255) DEFAULT NULL,
  `message_body` text,
  PRIMARY KEY (`id`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=5412 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

补充测试数据

INSERT INTO `sys_log`(`id`,`message_type`,`message_body`) VALUES (1,"上线","{"online":"1"}"),(2,"下线","{"online":"0"}"),(3,"告警1",'[{"name":"WATER_DETC","serialId":"0","value":"1"}]'),(4,"告警2",'[{"name":"WATER_DETC","serialId":"0","value":"0"}]'),(5,"告警3",'[{"name":"DEMOLISH","serialId":"0","value":"1"}]')

查询sql

select * from sys_log where message_body ->"$.online" = "1" or JSON_CONTAINS(message_body->"$[*].name", '"WATER_DETC"', "$")