MySQL按指定字段分组并聚合嵌套数据的实现咨询

问题背景 #

原始数据 #

[
    {
        "id": 1254,
        "receive_date": "2022-11-03T18:30:00.000Z",
        "receive_time": "19:29:00",
        "machineId": "TEST",
        "attributeId": "connection_status",
        "hardwareId": "5"
    },
    {
        "id": 1255,
        "receive_date": "2022-11-03T18:30:00.000Z",
        "receive_time": "19:29:00",
        "machineId": "TEST",
        "attributeId": "connection_status",
        "hardwareId": "6"
    },
    {
        "id": 1256,
        "receive_date": "2022-11-03T18:30:00.000Z",
        "receive_time": "19:30:00",
        "machineId": "TEST",
        "attributeId": "connection_status",
        "hardwareId": "6"
    }
]

分组规则 #

• 按receive_date（DATE类型）分组
• 按receive_time（TIME类型）分组

期望输出 #

[
    {
        "receive_date": "2022-11-03T18:30:00.000Z",
        "receive_time": "19:29:00",
        "data": [
            {
                "attributeId": "connection_status",
                "hardwareId": "5",
                "value": 478
            },
            {
                "attributeId": "connection_status",
                "hardwareId": "6",
                "value": 344
            }
        ]
    },
    {
        "receive_date": "2022-11-03T18:30:00.000Z",
        "receive_time": "19:30:00",
        "data": [
            {
                "attributeId": "connection_status",
                "hardwareId": "6",
                "value": 789
            }
        ]
    }
]

解决方案 #

方案一：前端JavaScript处理 #

使用Array.reduce()实现分组聚合，构造目标结构：

const rawData = [/* 原始数据数组 */];

const groupedResult = rawData.reduce((acc, item) => {
    // 用日期+时间作为唯一分组键
    const groupKey = `${item.receive_date}_${item.receive_time}`;
    
    if (!acc[groupKey]) {
        acc[groupKey] = {
            receive_date: item.receive_date,
            receive_time: item.receive_time,
            data: []
        };
    }
    
    // 向分组的data数组添加指定字段（value字段需根据业务逻辑补充取值）
    acc[groupKey].data.push({
        attributeId: item.attributeId,
        hardwareId: item.hardwareId,
        value: /* 替换为value的实际取值逻辑，比如从其他字段提取或计算 */
    });
    
    return acc;
}, {});

// 将分组对象转为数组格式
const finalResult = Object.values(groupedResult);

注意：原始数据中无value字段，需根据实际业务场景补充该字段的取值逻辑（如后端返回、前端计算等）。

方案二：后端SQL查询（以MySQL为例） #

如果数据存储在数据库中，可通过JSON聚合函数直接生成目标结构：

SELECT
    receive_date,
    receive_time,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'attributeId', attributeId,
            'hardwareId', hardwareId,
            'value', /* 替换为value字段的实际来源，如某个数值字段 */
        )
    ) AS data
FROM your_table_name
GROUP BY receive_date, receive_time;

不同数据库的JSON聚合语法有差异：

• PostgreSQL使用json_agg()
• SQL Server使用STRING_AGG()结合JSON构造函数，需根据实际使用的数据库调整语句。

内容的提问来源于stack exchange，提问作者shubham