MySQL中JSON操作

引言

Mysql5.7版本以后提供了一个原生的Json类型，Json值将不再以字符串的形式存储，而是采用一种允许快速读取文本元素（document elements）的内部二进制（internal binary）格式。 在Json列插入或者更新的时候将会自动验证Json文本，未通过验证的文本将产生一个错误信息。 Json文本采用标准的创建方式，可以使用大多数的比较操作符进行比较操作，例如：=, <, <=, >, >=, <>, != 和 <=>。
mybatis中操作json

1.建表及添加数据

--先创建一个简单的含json格式的数据库表，其中json_value就为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;
--添加数据
insert into dept VALUES(1,'部门1','{"deptName": "部门1", "deptId": "1", "deptLeaderId": "3"}');
insert into dept VALUES(2,'部门2','{"deptName": "部门2", "deptId": "2", "deptLeaderId": "4"}');
insert into dept VALUES(3,'部门3','{"deptName": "部门3", "deptId": "3", "deptLeaderId": "5"}');
insert into dept VALUES(4,'部门4','{"deptName": "部门4", "deptId": "4", "deptLeaderId": "5"}');
insert into dept VALUES(5,'部门5','{"deptName": "部门5", "deptId": "5", "deptLeaderId": "5"}');
复制

2.基础查询

用法提示：
如果json字符串不是数组，则直接使用$.字段名
如果json字符串是数组[Array]，则直接使用$[对应元素的索引id]

2.1 一般json查询

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

--查询deptLeaderId=5的数据
SELECT * from dept WHERE json_value->'$.deptLeaderId'='5';
复制

2.2 多个条件查询

--查dept为“部门3”和deptLeaderId=5的数据，sql如下：
SELECT * from dept WHERE json_value->'$.deptLeaderId'='5' and dept='部门3';
复制

2.4 关联表查询

--再创建一张包含json格式的表
CREATE TABLE `dept_leader` (
  `id` int(11) NOT NULL,
  `leaderName` varchar(255) DEFAULT NULL,
  `json_value` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--插入一些测试数据
insert into dept_leader VALUES(1,'leader1','{"name": "王一", "id": "1", "leaderId": "1"}');
insert into dept_leader VALUES(2,'leader2','{"name": "王二", "id": "2", "leaderId": "3"}');
insert into dept_leader VALUES(3,'leader3','{"name": "王三", "id": "3", "leaderId": "4"}');
insert into dept_leader VALUES(4,'leader4','{"name": "王四", "id": "4", "leaderId": "5"}');
insert into dept_leader VALUES(5,'leader5','{"name": "王五", "id": "5", "leaderId": "5"}');
--连表查询在dept表中部门leader在dept_leader中的详情
SELECT * from dept,dept_leader 
WHERE dept.json_value->'$.deptLeaderId'=dept_leader.json_value->'$.id' ;
复制

3.JSON函数操作

上述查询的json都是整条json数据，这样看起来不是很方便，那么如果我们只想看json中的某个字段怎么办？

3.1 ->、->>区别

->会保持json文档格式中原来格式，但->>会把所有引号去掉。
特别注意：->当做where查询是要注意类型的，->>是不用注意类型的

select * from dept where json_value->'$.deptId'=1
复制

select * from dept where json_value->'$.deptId'='1'
复制

3.2 JSON_EXTRACT 函数：从json中返回想要的字段

1.可以通过key查询value值(如果是json数组类型,可以通过下标获取对应位置的值)，非常方便。

2.使用场景：JSON_EXTRACT性能验证 , 通过查看执行计划，验证全部都是全表扫描。数据量不大json字符串较大则可以采用，数据量较大不建议使用。

3.语法：JSON_EXTRACT(json_doc, path[, path] …)

4.栗子

select 
	json_extract('{"name":"zhangsan","tel_no":"136-6666-6666","hobbies":["basketball","run","sing"]}',"$.name") as name,
	json_extract('{"name":"zhangsan","tel_no":"136-6666-6666","hobbies":["basketball","run","sing"]}',"$.tel_no") as tel_no,
	json_extract('{"name":"zhangsan","tel_no":"136-6666-6666","hobbies":["basketball","run","sing"]}',"$.hobbies[0]") as hobby_1,
	json_extract('{"name":"zhangsan","tel_no":"136-6666-6666","hobbies":["basketball","run","sing"]}',"$.hobbies[1]") as hobby_2,
	json_extract('{"name":"zhangsan","tel_no":"136-6666-6666","hobbies":["basketball","run","sing"]}',"$.hobbies[2]") as hobby_3,
	json_extract('{"name":"zhangsan","tel_no":"136-6666-6666","hobbies":["basketball","run","sing"]}',"$.hobbies[3]") as hobby_4;
复制

结果：

举例查询：

-- 创建测试表
CREATE TABLE `tab_json` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `data` json DEFAULT NULL COMMENT 'json字符串',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 新增数据
-- {"Tel": "132223232444", "name": "david", "address": "Beijing"}
-- {"Tel": "13390989765", "name": "Mike", "address": "Guangzhou"}
INSERT INTO `testdb`.`tab_json`(`id`, `data`) VALUES (1, '{\"Tel\": \"132223232444\", \"name\": \"david\", \"address\": \"Beijing\"}');
INSERT INTO `testdb`.`tab_json`(`id`, `data`) VALUES (2, '{\"Tel\": \"13390989765\", \"name\": \"Mike\", \"address\": \"Guangzhou\"}');
INSERT INTO `testdb`.`tab_json`(`id`, `data`) VALUES (3, '{"success": true,"code": "0","message": "","data": {"name": "jerry","age": "18","sex": "男"}}');
INSERT INTO `testdb`.`tab_json`(`id`, `data`) VALUES (4, '{"success": true,"code": "1","message": "","data": {"name": "tome","age": "30","sex": "女"}}');

-- 查询
select * from tab_json;

-- json_extract
select json_extract('{"name":"Zhaim","tel":"13240133388"}',"$.tel");
select json_extract('{"name":"Zhaim","tel":"13240133388"}',"$.name");

-- 对tab_json表使用json_extract函数
select json_extract(data,'$.name') from tab_json;

#如果查询没有的key,那么是可以查询,不过返回的是NULL.
select json_extract(data,'$.name'),json_extract(data,'$.Tel') from tab_json;  
select json_extract(data,'$.name'),json_extract(data,'$.tel') from tab_json;  
select json_extract(data,'$.name'),json_extract(data,'$.address') from tab_json;

-- 条件查询
select json_extract(data,'$.name'),json_extract(data,'$.Tel') from tab_json where json_extract(data,'$.name') = 'Mike';  

-- 嵌套json查询
select * from tab_json where json_extract(data,'$.success') = true;  
select json_extract(data,'$.data') from tab_json where json_extract(data,'$.success') = true;  
-- 查询data对应json中key为name的值
select json_extract( json_extract(data,'$.data'),'$.name') from tab_json where json_extract(data,'$.code') = "1";  
select json_extract( json_extract(data,'$.data'),'$.name'),json_extract( json_extract(data,'$.data'),'$.age') from tab_json where json_extract(data,'$.code') = "0";  

-- 性能验证 , 通过验证全部都是全表扫描，使用场景：数据量不大json字符串较大则可以采用，数据量较大不建议使用。
explain select * from tab_json where json_extract(data,'$.success') = true;  
explain select json_extract(data,'$.data') from tab_json where json_extract(data,'$.success') = true;  
-- 查询data对应json中key为name的值
explain select json_extract( json_extract(data,'$.data'),'$.name') from tab_json where json_extract(data,'$.code') = "1";  
explain select json_extract( json_extract(data,'$.data'),'$.name'),json_extract( json_extract(data,'$.data'),'$.age') from tab_json where json_extract(data,'$.code') = "0"; 

复制

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

用法: JSON_CONTAINS(target, candidate[, path])

--查询包含deptName=部门5的对象
select * from dept WHERE JSON_CONTAINS(json_value, JSON_OBJECT("deptName","部门5"))
复制

3.4 JSON_OBJECT():将一个键值对列表转换成json对象

比如我们想查询某个对象里面的值等于多少
比如我们添加这么一组数据到dept表中：

insert into dept VALUES(6,'部门9','{"deptName": {"dept":"de","depp":"dd"}, "deptId": "5", "deptLeaderId": "5"}');
复制

我们可以看到deptName中还有一个对象，里面还有dept和depp两个属性字段，那么我们应该怎么查询depp=dd的员工呢。

用法：JSON_OBJECT([key, val[, key, val] …])
事例：

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

3.5 JSON_ARRAY():创建JSON数组

比如我们添加这么一组数据到dept表中：

insert into dept VALUES(7,'部门9','{"deptName": ["1","2","3"], "deptId": "5", "deptLeaderId": "5"}');
insert into dept VALUES(7,'部门9','{"deptName": ["5","6","7"], "deptId": "5", "deptLeaderId": "5"}');
复制

用法：JSON_ARRAY([val[, val] …])

事例：我们要查询deptName包含1的数据

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

3.6 JSON_TYPE():查询某个json字段属性类型

用法：JSON_TYPE(json_val)
事例：比如我们想查询deptName的字段属性是什么

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

3.7 JSON_KEYS():JSON文档中的键数组

用法：JSON_KEYS(json_value)
事例：比如我们想查询json格式数据中的所有key

SELECT JSON_KEYS(json_value) FROM dept 
复制

接下来的3种函数都是新增数据类型的：

JSON_SET(json_doc, path, val[, path, val] …)
JSON_INSERT(json_doc, path, val[, path, val] …)
JSON_REPLACE(json_doc, path, val[, path, val] …)
复制

3.8 JSON_SET():将数据插入JSON格式中，有key则替换，无key则新增

这也是我们开发过程中经常会用到的一个函数
用法：JSON_SET(json_doc, path, val[, path, val] …)
事例：比如我们想针对id=2的数据新增一组：newData:新增的数据,修改deptName为新增的部门1

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

注意：json_doc如果不带这个单元格之前的值，之前的值是会新值被覆盖的，比如我们如果更新的语句换成:

update dept set json_value=JSON_SET('{"a":"1","b":"2"}','$.deptName','新增的部门1','$.newData','新增的数据') WHERE id=2
复制

我们可以看到这里json_doc是{“a”:“1”,“b”:“2”}，这样的话会把之前的单元格值覆盖后再新增/覆盖这个单元格字段

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

用法：JSON_INSERT(json_doc, path, val[, path, val] …)
事例：

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

我们可以看到由于json_doc变化将之前的值覆盖了，新增了deptName和newData2.
如果我们再执行以下刚才的那个sql，只是换了value，我们会看到里面的key值不会发生变化。
因为这个函数只负责往json中插入新值，但不替换已经存在的旧值。

3.10 JSON_REPLACE()

用法：JSON_REPLACE(json_doc, path, val[, path, val] …)
用例：
如果我们要更新id=2数据中newData2的值为：更新的数据2

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

3.11 JSON_REMOVE():从JSON文档中删除数据

用法：JSON_REMOVE(json_doc, path[, path] …)
举例：删除key为a的字段。

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

Java操作Json

方法一：使用 FastJSON

import com.alibaba.fastjson.JSONObject;

public class Main {
    public static void main(String[] args) {
        String resultJson = "{\"code\":\"200\",\"message\":\"success\"}";
        
        JSONObject jsonObject = JSONObject.parseObject(resultJson);
        String code = jsonObject.getString("code");
        
        System.out.println(code); // 输出: 200
    }
}
复制

方法二：使用 Jackson

import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;

public class Main {
    public static void main(String[] args) {
        String resultJson = "{\"code\":\"200\",\"message\":\"success\"}";
        
        try {
            ObjectMapper objectMapper = new ObjectMapper();
            JsonNode jsonNode = objectMapper.readTree(resultJson);
            String code = jsonNode.get("code").asText();
            
            System.out.println(code); // 输出: 200
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
复制

区别

1. 库的选择：

   • FastJSON：阿里巴巴开发的高性能 JSON 库，广泛用于 Java 开发。
   • Jackson：一个流行的高性能 JSON 处理库，广泛用于 Java 开发。

2. 解析方法：

   • FastJSON：使用 JSONObject.parseObject 方法将 JSON 字符串解析为 JSONObject，然后使用 getString 方法获取字符串值。
   • Jackson：使用 ObjectMapper.readTree 方法将 JSON 字符串解析为 JsonNode，然后使用 get 方法获取节点，再使用 asText 方法获取字符串值。

3. 异常处理：

   • FastJSON：通常不需要显式处理异常，除非解析过程中出现错误。
   • Jackson：需要捕获 IOException 异常，因为 readTree 方法可能会抛出异常。

4. 性能：

   • FastJSON：通常被认为是高性能的 JSON 库，特别是在大数据量的情况下。
   • Jackson：也是一个高性能的 JSON 库，但在某些场景下可能不如 FastJSON 快。

5. API 设计：

   • FastJSON：API 设计简洁，方法调用链较短。
   • Jackson：API 设计较为灵活，提供了更多的方法和选项。

fastjson转换指定对象

1.List

import com.alibaba.fastjson.JSONArray;
List<PaymentDto> paymentList = JSONArray.parseArray(json, PaymentDto.class);
复制

2.String

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
JSONObject orderJsonObject = new JSONObject().fluentPut("orderNo", "111");
log.warn("thisMsg:{}",JSONObject.toJSONString(orderJsonObject));
log.warn("thisMsg:{}",JSON.toJSONString(orderJsonObject));
复制

3.对象

JSONObject.parseObject(JSONObject.toJSONString(object),VehicleProduct.class)
JSON.parseObject(JSON.toJSONString(object), VehicleProduct.class));
复制

4.其他范型

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.TypeReference;
 Response<ProvinceDTO> result = JSON.parseObject(json, new TypeReference<Response<ProvinceDTO>>() {});
复制

数据库json对应实体类

import com.fasterxml.jackson.annotation.JsonSetter;
import com.fasterxml.jackson.annotation.Nulls;
import java.time.LocalDateTime;
import java.util.List;
import javax.persistence.Column;
import javax.persistence.Convert;
import javax.persistence.Entity;
import javax.persistence.EntityListeners;
import javax.persistence.EnumType;
import javax.persistence.Enumerated;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.hibernate.annotations.DynamicInsert;
import org.hibernate.annotations.DynamicUpdate;
import org.hibernate.annotations.SelectBeforeUpdate;
import org.hibernate.annotations.Where;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;

/**
 * 订单级别的调价表
 */
@Entity//标识该类为一个 JPA 实体
@Data
@AllArgsConstructor
@NoArgsConstructor
@EntityListeners(AuditingEntityListener.class)//用于指定一个或多个监听器类，这些监听器可以在实体的生命周期事件（如插入、更新、删除）发生时执行某些操作,结合 @CreatedDate、@LastModifiedDate 等注解来实现自动审计功能
@DynamicInsert//启用动态 SQL 插入，只插入实际有值的字段，避免插入 null
@DynamicUpdate//启用动态 SQL 更新。启用后，Hibernate 只会在更新语句中包含那些实际发生了变化的字段，而不是更新所有字段。这可以提高性能，尤其是在表中有大量字段的情况下。
@SelectBeforeUpdate//在更新前先查询数据库中的现有数据，确保数据的一致性。
@Where(clause = "deleted = 0")//实现软删除功能，只查询 deleted = 0 的记录
@Table(name = "price")
public class PriceEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "special_tag_json_list")
    @Convert(converter = SpecialTagsJsonConverter.class)//使用转换器把数据库json字段转为list对象
    private List<SpecialTagsDTO> specialTagList;

    @Convert(converter = OrderSnapshotJsonConverter.class)
    private OrderSnapshotDTO orderSnapshot;
    
    @Enumerated(EnumType.STRING)//枚举类型映射到数据库
    @JsonSetter(nulls = Nulls.SKIP)//反序列化过程中跳过那些为 null 的字段
    private Status status = Status.NONE;

    @CreatedDate//用于标记一个字段，表示该字段将自动填充为实体的创建时间。
    @Column(name = "created_time", columnDefinition = "datetime", updatable = false)
    private LocalDateTime createdTime;

    @LastModifiedDate//用于标记一个字段，表示该字段将自动更新为实体的最后修改时间。
    @Column(name = "updated_time", columnDefinition = "datetime")
    private LocalDateTime updatedTime;

    @Column(name = "deleted", columnDefinition = "int default 0", nullable = false)
    //@Column(updatable = false, insertable = false)
    private Integer deleted;
}
复制

@Slf4j
public class SpecialTagsJsonConverter implements AttributeConverter<List<SpecialTagsDTO>, String> {

    @Override
    public String convertToDatabaseColumn(List<SpecialTagsDTO> attribute) {
        if (attribute == null || attribute.isEmpty()) {
            return null;
        }
        // 将列表转换成JSON字符串
        return JSON.toJSONString(attribute);
    }

    @Override
    public List<SpecialTagsDTO> convertToEntityAttribute(String dbVal) {
        if (dbVal == null || dbVal.isEmpty()) {
            return null;
        }
        // 将JSON字符串转换回列表
        return JSONArray.parseArray(dbVal, SpecialTagsDTO.class);
    }
}
复制