表

CREATE TABLE `t_json` (
`id` int NOT NULL AUTO_INCREMENT,
`json` json DEFAULT NULL,
`jarray` json DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

 

数据

 

 查询

select sum(json->'$.oilFee') +sum(json->'$.partFee') + IFNULL(sum(json->'$.paratFee'),0), json->'$.remark' from t_json where json->'$.remark' is null; // 查json字段 ->'$.字段名'

select * from t_json where 1 member of (jarray) // 对json数组进行查找

select * from t_json where JSON_CONTAINS_PATH(json, 'all', '$.remark') // 查询json数据中存在remark键的数据. 其中all是条件,可以是one或all

update t_json set json = '{\"test\": \"test\"}' where id = 1;