MySQL中对JSON数据操作（较全）

MySQL对JSON数据操作

链接：(160条消息) 【MySQL】对JSON数据操作（全网最全）_mysql json_0世界和平0的博客-CSDN博客

1. 创建json格式字段

   CREATE TABLE `dept` (
     `id` int(11) NOT NULL,
     `dept` varchar(255) DEFAULT NULL,
     `json_value` json DEFAULT NULL,
     PRIMARY KEY (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
   

2. 条件查询：使用json字段名->'$json属性'

   SELECT * from dept WHERE json_value->'$.deptLeaderId'='5';
   

3. 多条件查询

   SELECT * from dept WHERE json_value->'$.deptLeaderId'='5' and json_value->'$.deptId'='5';
   

4. 多个字段查询

   SELECT * from dept WHERE json_value->'$.deptLeaderId'='5' and json_value->'$.deptId'='5';
   

5. 关联查询

   SELECT * from dept,dept_leader WHERE dept.json_value->'$.deptLeaderId'=dept_leader.json_value->'$.id' ;
   

6. 一般函数查询操作：

   1. json_extract(字段名,$.json字段名)：从json返回想要的字段

      select id,json_extract(json_value,'$.deptName') as deptName from dept;
      

   2. JSON_CONTAINS()：JSON格式数据是否在字段中包含特定对象

      select * from dept WHERE JSON_CONTAINS(json_value, JSON_OBJECT("deptName","部门5"))
      

   3. JSON_OBJECT()：将一个键值对列表转换成json对象

      SELECT * from (
      SELECT *,json_value->'$.deptName' as deptName FROM dept
      ) t WHERE JSON_CONTAINS(deptName,JSON_OBJECT("depp","dd"));
      

   4. JSON_ARRAY()：创建JSON数组

      SELECT * from dept WHERE JSON_CONTAINS(json_value->'$.deptName',JSON_ARRAY("1"))
      

   5. JSON_TYPE()：查询某个json字段属性类型

      SELECT json_value->'$.deptName' ,JSON_TYPE(json_value->'$.deptName') as type from dept 
      

   6. JSON_EXTRACT()：从JSON文档返回数据

      SELECT * FROM dept WHERE JSON_EXTRACT(json_value,'$.deptName') like '%部门%';
      

   7. JSON_KEYS()：JSON文档中的键数组

      SELECT JSON_KEYS(json_value) FROM dept 
      

   8. JSON_SET(json_doc, path, val, path2,val2,...)：将数据插入JSON格式中，有key则替换，无key则新增

      update dept set json_value=JSON_SET('{"deptName": "部门2", "deptId": "2", "deptLeaderId": "4"}','$.deptName','新增的部门1','$.newData','新增的数据') WHERE id=2;
      
      select * from dept WHERE id =2
      

      注意：如果json_doc不包含之前的值，之前的值会被新值覆盖

   9. JSON_INSERT()：插入值（往json中插入新值，但不替换已经存在的旧值）

      UPDATE dept set json_value=JSON_INSERT('{"a": "1", "b": "2"}', '$.deptName', '新增的部门2','$.newData2','新增的数据2') 
      WHERE id=2
      

   10. JSON_REPLACE()：更新JSON数据

       UPDATE dept set json_value=JSON_REPLACE('{"a": "1", "b": "2", "deptName": "新增的部门2", "newData2": "新增的数据2"}', '$.newData2', '更新的数据2') WHERE id =2;
       
       select * from dept WHERE id =2
       

   11. JSON_REMOVE()：从JSON文档中删除数据

       UPDATE dept set json_value=JSON_REMOVE('{"a": "1", "b": "2", "deptName": "新增的部门2", "newData2": "更新的数据2"}','$.a') WHERE id =2;
       

   12. JSON_SEARCH(json_doc,查询终止条件,搜索参数)：用于在json格式中查询并返回条件的节点。