MyBatis Plus与JSON字段查询：动态构建JSON条件

前言

在实际项目中，随着数据存储需求的不断变化，JSON 数据类型被越来越多地应用到数据库中。许多关系型数据库，如 MySQL，已经开始支持 JSON 类型字段，并允许对其进行查询。对于这些 JSON 字段，我们可能需要根据 JSON 路径进行查询和条件构建。

在本文中，我们将展示如何通过 MyBatis Plus 动态构建查询条件，以便查询 JSON 类型字段的特定属性。特别地，我们将介绍如何使用 JSON_EXTRACT 和 JSON_UNQUOTE 函数来处理 JSON 字段，并将其与 QueryWrapper 结合使用，简化动态 SQL 查询的编写。

1. 背景与需求

假设我们有一个数据库表 your_table，其中有一个字段 data 存储 JSON 格式的数据，如下所示：

{
  "miniProgramId": "123456",
  "userId": "78910"
}

我们希望能够查询出 data 字段中 miniProgramId 的值，并且在查询时能够动态指定 JSON 路径和比较值。例如，如果 miniProgramId 为 "123456"，则返回该行数据。

为了实现这个目标，我们需要使用 SQL 函数 JSON_EXTRACT 来提取 JSON 字段中的值，并用 JSON_UNQUOTE 去掉字符串两边的引号。接下来，我们会将这个 SQL 条件与 MyBatis Plus 的 QueryWrapper 结合使用。

2. 解决方案：动态构建 JSON 查询条件

2.1. MyBatis Plus QueryWrapper 简介

MyBatis Plus 是 MyBatis 的增强工具，提供了 QueryWrapper 来简化常见的查询操作。通过 QueryWrapper，我们可以动态地构建 SQL 查询条件。

2.2. 构建 JSON 查询条件

可以创建一个工具类 JsonConditionBuilder，由于Mybatis Plus的底层依赖于JSqlParse，可以直接借助JSqlParse 这个SQL语句工具依赖能够动态构建 SQL 条件，并通过 MyBatis Plus 的 QueryWrapper 来执行查询。通过传入表字段名、JSON 子路径和比较值，它可以拼接成 SQL 语句。

SQL JSON 操作相关的常量：

package com.hsqyz.web.common.constant;

/**
 * SqlFunctions 类提供了与 SQL JSON 操作相关的常量。
 */
@SuppressWarnings("ALL")
public interface SqlFunctions {

    /**
     * 表示 JSON_UNQUOTE 常量，用于从 JSON 字符串中移除外层引号。
     * 该常量主要用于 SQL 语句中引用 JSON_UNQUOTE 函数。
     */
    String JSON_UNQUOTE = "JSON_UNQUOTE";

    /**
     * 表示 JSON_EXTRACT 常量，用于从 JSON 字符串中提取数据。
     * 该常量主要用于 SQL 语句中引用 JSON_EXTRACT 函数。
     */
    String JSON_EXTRACT = "JSON_EXTRACT";

}

JSON条件构建工具：

package com.hsqyz.web.util;

import com.hsqyz.web.common.constant.SqlFunctions;
import com.hsqyz.web.domain.UserRadarData;
import com.hsqyz.web.radar.data.RadarData;
import lombok.AccessLevel;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.expression.*;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.schema.Column;

import static com.baomidou.mybatisplus.core.toolkit.StringPool.*;

/**
 * JSON 条件构建工具类
 *
 * @author lcz
 */
@Slf4j
@SuppressWarnings("all")
@NoArgsConstructor(access = AccessLevel.PRIVATE)
public class JsonConditionBuilderUtil {

    // JSON 路径前缀
    private final static String DOLLAR_DOT = DOLLAR + DOT;

    /**
     * 构建 SQL WHERE 子句来匹配 JSON 中的值
     *
     * @param columnName 需要处理的列名
     * @param jsonSubPath JSON 子路径（不包括 $.）
     * @param value 要比较的值
     * @return 构建的 SQL WHERE 子句
     */
    public static String buildJsonWhereClause(String columnName, String jsonSubPath, String value) {
        try {

            // 构建完整的 JSON 路径，拼接 $ 和传入的 jsonSubPath
            String jsonPath = DOLLAR_DOT + jsonSubPath;

            // 构建 JSON_EXTRACT 表达式
            Column dataColumn = new Column(columnName);  // 动态字段名
            Function jsonExtractExpr = new Function();
            jsonExtractExpr.setName(SqlFunctions.JSON_EXTRACT);
            jsonExtractExpr.setParameters(new ExpressionList(new Expression[]{
                dataColumn, new StringValue(jsonPath)
            }));

            // 构建 JSON_UNQUOTE
            Function jsonUnquoteExpr = new Function();
            jsonUnquoteExpr.setName(SqlFunctions.JSON_UNQUOTE);
            jsonUnquoteExpr.setParameters(new ExpressionList(jsonExtractExpr));

            // 构建右侧的常量值
            StringValue rightValue = new StringValue(value);

            // 构建 EqualsTo 表达式
            EqualsTo equalsTo = new EqualsTo();
            equalsTo.setLeftExpression(jsonUnquoteExpr);
            equalsTo.setRightExpression(rightValue);

            return equalsTo.toString();

        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     *  示例：传入实际参数
     *  String columnName = "data";           // 数据列名
     *  String jsonSubPath = "miniProgramId"; // JSON子路径（这里没有 $）
     *  String value = "1";                   // 比较值
     *  String whereClause = buildJsonWhereClause(columnName, jsonSubPath, value); // 构建的SQL WHERE子句
     *  System.out.println(whereClause); // 输出构建的SQL WHERE子句
     * @param args
     */
    public static void main(String[] args) {
        String jsonWhereClause = JsonConditionBuilderUtil.buildJsonWhereClause(UserRadarData.Fields.data, RadarData.Fields.miniProgramId, "1880073482997215234");
        System.out.println(jsonWhereClause);
    }

}

3. 代码解析

1. 构建 JSON 路径：拼接 $ 与传入的 jsonSubPath，构造出完整的 JSON 路径。

   • 例如：jsonSubPath 为 miniProgramId，生成的 jsonPath 为 $.miniProgramId。

2. 构建 JSON_EXTRACT 和 JSON_UNQUOTE 表达式：

   • 使用 JSON_EXTRACT 提取 JSON 字段中的数据。
   • 使用 JSON_UNQUOTE 去掉返回值两端的引号。

3. 构建查询条件：用 EqualsTo 构建 JSON_UNQUOTE(JSON_EXTRACT(...)) = value 的 SQL 条件。

4. 使用 MyBatis Plus 查询

可以将动态构建的条件与 MyBatis Plus 的 QueryWrapper 一起使用。只需要将 buildJsonWhereClause 返回的 SQL 片段传入 QueryWrapper 的 apply 方法中即可：

@Autowired
private YourMapper yourMapper;

public void queryExample() {

    String columnName = "data";           // 数据列名
    String jsonSubPath = "miniProgramId"; // JSON子路径
    String value = "1";                   // 比较值

    // 构建查询条件
    String whereClause = JsonConditionBuilder.buildJsonWhereClause(columnName, jsonSubPath, value);

    // 创建 QueryWrapper
    QueryWrapper queryWrapper = new QueryWrapper<>();
    queryWrapper.apply(whereClause);  // 使用构建的 SQL 条件

    // 执行查询
    List<YourEntity> result = yourMapper.selectList(queryWrapper);

    // 处理查询结果
    System.out.println(result);
}

5. 结果演示

假设 columnName = "data"，jsonSubPath = "miniProgramId"，value = "1"，你在控制台上看到的输出将是：

WHERE JSON_UNQUOTE(JSON_EXTRACT(data, '$.miniProgramId')) = '1'