MySQL JSON_ARRAYAGG 实现汇总+明细数据展示

一、业务场景

在投注记录查询功能中，我们需要展示每个彩票期号(userId + lotteryIssue分组)的汇总数据（总金额、总注数），同时也要显示该期号下的所有明细投注记录。

解决方案：JSON_ARRAYAGG

MySQL 5.7+ 提供的 JSON_ARRAYAGG 函数完美解决了这个问题，它可以将多行数据聚合成一个JSON数组。

二、代码实现

1. 后端接口（Controller层）

@GetMapping("/records")
public AjaxResult records(HttpServletRequest request,
                       @RequestParam(defaultValue = "1") Integer pageNum,
                       @RequestParam(defaultValue = "20") Integer pageSize) {
    return service.records(request, pageNum, pageSize);
}

• 接收分页参数，调用服务层

2. 服务层（Service层）

public AjaxResult records(HttpServletRequest request, Integer pageNum, Integer pageSize) {
    UserVo user = JwtUtils.getUser(request.getHeader("token")); // 鉴权
    List<FastThreeBettingVo> list = mapper.selectRecordsByUserId(user.getUserId());
    return AjaxResult.success(list); // 返回带分级的数据
}

3. 数据结构（VO对象）

@Data
public class FastThreeBettingVo {


    // 用户
    private Long userId;
    // 期号
    private String lotteryIssue;
    // 总金额
    private BigDecimal totalAmount;
    // 总注数
    private Long totalCount;


    // 投注详情列表
    private List<FastThreeBettingDetail> children;

    // 手动设置 children 的方法
    public void setChildren(String detailsJson) {
        ObjectMapper objectMapper = new ObjectMapper();
        try {
            // 将 JSON 字符串解析为 List<xx>
            this.children = objectMapper.readValue(detailsJson, new TypeReference<List<FastThreeBettingDetail>>() {});

            // 对 children 按 lotteryIssue 倒序排序
            sortChildrenByLotteryIssueDesc();
        } catch (Exception e) {
            throw new RuntimeException("Failed to parse details JSON", e);
        }
    }



    // 按 lotteryIssue 倒序排序 children 的方法
    private void sortChildrenByLotteryIssueDesc() {
        if (this.children != null && !this.children.isEmpty()) {
            // 使用 Comparator 对 lotteryIssue 进行倒序排序
            this.children.sort((detail1, detail2) -> {
                String issue1 = detail1.getLotteryIssue();
                String issue2 = detail2.getLotteryIssue();
                return issue2.compareTo(issue1); // 倒序排序
            });
        }
    }
}

4. 数据结构（children对象）

@Data
public class FastThreeBettingDetail {


    // 期号
    private String lotteryIssue;

    // 玩法名称
    private String clzName;

    // 下注信息
    private String specialName;

    // 下注金额
    private BigDecimal amount;

    // 中奖状态 0=待开奖 1=中奖 2=未中奖
    private String winningStatus;

    // 中奖金额
    private BigDecimal winningAmount;
}

5. 核心SQL（Mapper层）

1. 明细聚合：JSON_ARRAYAGG + JSON_OBJECT 将明细记录转为JSON数组

    <select id="selectRecordsByUserId" parameterType="Long" resultType="com.ruoyi.ruoyiliushu.mo.ls_fast_three_user_betting.vo.FastThreeBettingVo">
        SELECT
            user_id AS userId,
            lottery_issue AS lotteryIssue,
            SUM(amount) AS totalAmount,
            COUNT(*) AS totalCount,
            JSON_ARRAYAGG(
                    JSON_OBJECT(
                            'lotteryIssue', lottery_issue,
                            'clzName', clz_name,
                            'specialName', special_name,
                            'amount', amount,
                            'winningStatus',
                            CASE winning_status
                                WHEN 0 THEN '待开'
                                WHEN 1 THEN '中奖'
                                WHEN 2 THEN '未中'
                                ELSE '未知'
                                END,
                            'winningAmount', winning_amount
                        )
                ) AS children
        FROM
            ls_fast_three_user_betting
        where
        user_id = #{userId}
        group by
            user_id,
            lottery_issue
        order by
            lottery_issue desc
        limit 1
    </select>

6. 输出结构

{
    "msg": "操作成功",
    "code": 200,
    "data": [
        {
            "userId": 11,
            "lotteryIssue": "2025020010",
            "totalAmount": 6.00,
            "totalCount": 6,
            "children": [
                {
                    "lotteryIssue": "2025020010",
                    "clzName": "豹子",
                    "specialName": "6,6,6",
                    "amount": 1.00,
                    "winningStatus": "未中",
                    "winningAmount": 169.00
                },
                {
                    "lotteryIssue": "2025020010",
                    "clzName": "豹子",
                    "specialName": "5,5,5",
                    "amount": 1.00,
                    "winningStatus": "未中",
                    "winningAmount": 169.00
                },
                {
                    "lotteryIssue": "2025020010",
                    "clzName": "豹子",
                    "specialName": "4,4,4",
                    "amount": 1.00,
                    "winningStatus": "未中",
                    "winningAmount": 169.00
                },
                {
                    "lotteryIssue": "2025020010",
                    "clzName": "豹子",
                    "specialName": "3,3,3",
                    "amount": 1.00,
                    "winningStatus": "未中",
                    "winningAmount": 169.00
                },
                {
                    "lotteryIssue": "2025020010",
                    "clzName": "豹子",
                    "specialName": "2,2,2",
                    "amount": 1.00,
                    "winningStatus": "未中",
                    "winningAmount": 169.00
                },
                {
                    "lotteryIssue": "2025020010",
                    "clzName": "豹子",
                    "specialName": "1,1,1",
                    "amount": 1.00,
                    "winningStatus": "未中",
                    "winningAmount": 169.00
                }
            ]
        }
    ]
}

三、函数详解

1. JSON_OBJECT 函数

功能

JSON_OBJECT 用于创建一个JSON对象（键值对集合）

语法

sql

JSON_OBJECT(key1, value1, key2, value2, ...)

代码示例

sql


JSON_OBJECT(
    'lotteryIssue', lottery_issue,
    'clzName', clz_name,
    'specialName', special_name,
    'amount', amount,
    'winningStatus', CASE winning_status... END,
    'winningAmount', winning_amount
)

运行效果

将每行数据转换为：

 

json

{
    "lotteryIssue": "2025020010",
    "clzName": "豹子",
    "specialName": "6,6,6",
    "amount": 1.00,
    "winningStatus": "未中",
    "winningAmount": 169.00
}

 

2. JSON_ARRAYAGG 函数

功能

JSON_ARRAYAGG 将多行数据聚合成一个JSON数组

语法

sql

JSON_ARRAYAGG(expression)

代码示例

sql

JSON_ARRAYAGG(
    JSON_OBJECT(...)  -- 将每行的JSON对象聚合成数组
) AS children

运行效果

将分组内的所有明细记录转换为：

json

[
    {第一条明细记录},
    {第二条明细记录},
    ...
]

四. 注意事项

1. MySQL版本要求：5.7+ 支持JSON函数

2. 性能考虑：大数据量时可能影响性能

3. 字段别名：Java VO中的字段名需与SQL中的别名一致

4. NULL处理：默认会包含NULL值，可用NULL ON NULL控制

这两个函数配合使用，完美解决了SQL查询中"一对多"关系的结构化输出需求。