MySQL json样式数据查询、修改等操作

最近接了一个不友好的项目，整个项目中好多关键数据存成了json串，项目再开发新需求时各种不友好，查询统计更是举步维艰，建议大家设计数据结构还是要遵循第三范式，该分表的分表，改分字段的分字段，json能不用最好别用，第一次开发时数据保存时确实爽，之后随着需求的变更和叠加，之后肯定会被维护人吐槽的，自己还是要对自己作品负债的嘛-_-

废话不说了，下边是关于json的sql整理，客观请笑纳~

#1.通过包含
select * from doc_customer where json_contains(customer_info, '{ "city": "北京市"}')

#2.通过json中的Key:value
select * from doc_customer where JSON_CONTAINS(customer_info,json_object("city","北京市")) 

#3.json中的数据
select * from doc_customer where JSON_CONTAINS(customer_info,  json_array(json_object("companycontact","Air21")))

#4.修改
UPDATE doc_customer SET customer_info = JSON_REPLACE(customer_info, '$.city', '天津') WHERE id = 1;

#5.创建表
CREATE TABLE t_json(id INT PRIMARY KEY, NAME VARCHAR(20) , info  JSON);
 
#6.插入记录
INSERT INTO t_json(id,sname,info) VALUES(1 ,'test','{"time":"2019-01-01 13:00:00","ip":"192.168.0.1","result":"fail"}');
INSERT INTO t_json(id,sname,info)  VALUES(2 ,'my',JSON_OBJECT("time",NOW(),'ip','192.168.0.1','result','fail'));
 
#7.查询IP键
SELECT sname,JSON_EXTRACT(info,'$.ip') FROM t_json;

#8.查询有多少个键
SELECT id,json_keys(info) AS "keys" FROM t_json;
  
#9.删除键
UPDATE t_json SET info = json_remove(info,'$.ip');

#10.增加键
UPDATE t_json SET info = json_set(info,'$.ip','192.168.0.1');

#11.变更值
UPDATE t_json SET info = json_set(info,'$.ip','192.168.0.2');