一、業務場景

在投注記錄查詢功能中，我們需要展示每個彩票期號(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">SELECTuser_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_statusWHEN 0 THEN '待開'WHEN 1 THEN '中獎'WHEN 2 THEN '未中'ELSE '未知'END,'winningAmount', winning_amount)) AS childrenFROMls_fast_three_user_bettingwhereuser_id = #{userId}group byuser_id,lottery_issueorder bylottery_issue desclimit 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對象（鍵值對集合）

語法

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

代碼示例

sqlJSON_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數組

語法

sqlJSON_ARRAYAGG(expression)

代碼示例

sqlJSON_ARRAYAGG(JSON_OBJECT(...)  -- 將每行的JSON對象聚合成數組
) AS children

運行效果

將分組內的所有明細記錄轉換為：

json[{第一條明細記錄},{第二條明細記錄},...
]

四. 注意事項

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

2. 性能考慮：大數據量時可能影響性能

3. 字段別名：Java VO中的字段名需與SQL中的別名一致

4. NULL處理：默認會包含NULL值，可用NULL ON NULL控制

這兩個函數配合使用，完美解決了SQL查詢中"一對多"關系的結構化輸出需求。