如何通过Sequelize将查询结果转为按日期分组的键值对象结构

我当前的Sequelize控制器代码如下：

const getSum = async(req, res) => {
    const expenses = await Expense.findAll({
        attributes: [
            'datepick',
            'itemized_expense',
            [sequelize.fn('SUM', sequelize.col('expense_amount')), 'itemized_expense_sum']],
        group: ['datepick', "itemized_expense"]
      })
    

    res.status(200).json(expenses)
}

使用Postman请求后，当前的JSON响应如下：

[
    {
        "datepick": "2022-10-03",
        "itemized_expense": "marketing",
        "itemized_expense_sum": "4000"
    },
    {
        "datepick": "2022-10-03",
        "itemized_expense": "development",
        "itemized_expense_sum": "8000"
    },
    {
        "datepick": "2022-10-03",
        "itemized_expense": "welfare",
        "itemized_expense_sum": "1111"
    },
    {
        "datepick": "2022-11-14",
        "itemized_expense": "wages",
        "itemized_expense_sum": "6000"
    },
    {
        "datepick": "2022-10-03",
        "itemized_expense": "wages",
        "itemized_expense_sum": "1111"
    },
    {
        "datepick": "2022-11-15",
        "itemized_expense": "development",
        "itemized_expense_sum": "1000"
    }
]

我需要修改Sequelize控制器，使返回的响应结构变为如下格式：

[
    {
        "key": "10/03",
        "data": {
            "marketing": 4000,
            "development": 8000,
            "welfare": 1111,
            "wages": 1111
        }
    },
    {
        "key": "11/14",
        "data": {
            "wages": 6000
        }
    },
    {
        "key": "11/15",
        "data": {
            "development": 1000
        }
    }
]

我尝试过用Object.keys()和map方法，但不确定是否正确，希望得到具体的实现指导。

无需修改Sequelize的查询逻辑，只需在拿到查询结果后对数据做分组和格式转换即可，具体实现如下：

const getSum = async(req, res) => {
    const expenses = await Expense.findAll({
        attributes: [
            'datepick',
            'itemized_expense',
            [sequelize.fn('SUM', sequelize.col('expense_amount')), 'itemized_expense_sum']
        ],
        group: ['datepick', "itemized_expense"]
    });

    // 按日期分组整理数据
    const groupedMap = {};
    expenses.forEach(item => {
        // 把YYYY-MM-DD转为MM/DD格式
        const dateKey = item.datepick.split('-').slice(1).join('/');
        // 初始化当前日期的结构
        if (!groupedMap[dateKey]) {
            groupedMap[dateKey] = {
                key: dateKey,
                data: {}
            };
        }
        // 将费用项转为键值对存入data，金额转数字类型
        groupedMap[dateKey].data[item.itemized_expense] = Number(item.itemized_expense_sum);
    });

    // 把分组对象转为目标数组
    const formattedResult = Object.values(groupedMap);

    res.status(200).json(formattedResult);
};

代码说明 #

• 用forEach遍历结果完成分组，逻辑直观高效；
• 通过split('-').slice(1).join('/')实现日期格式转换，若需其他格式可调整此处逻辑；
• 用Number()将字符串金额转为数字，匹配目标格式要求；
• 最后通过Object.values()将分组对象转为数组，直接返回即可。

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