I tried to use aggregate to find out each product's monthly sales in my order , but I ran into a problem.
Here's my data structures.
Order.model.ts
const OrderSchema: Schema = new Schema(
{
userId: {
type: String,
require: true,
},
products: [
{
product: {
_id: {
type: String,
},
title: {
type: String,
},
desc: {
type: String,
},
img: {
type: String,
},
categories: {
type: Array,
},
price: {
type: Number,
},
createdAt: {
type: String,
},
updatedAt: {
type: String,
},
size: {
type: String,
},
color: {
type: String,
},
},
quantity: {
type: Number,
default: 1,
},
},
],
quantity: {
type: Number,
},
total: {
type: Number,
},
address: {
type: String,
require: true,
},
status: {
type: String,
default: 'pending',
},
},
{ timestamps: true },
);
Order-service.ts
public async getIncome(productId?: string) {
const date = new Date();
const lastMonth = new Date(date.setMonth(date.getMonth() - 1));
const previousMonth = new Date(new Date().setMonth(lastMonth.getMonth() - 1));
//const lastYear = new Date(date.setFullYear(date.getFullYear() - 1));
const income = await this.order.aggregate([
{
$match: {
createdAt: { $gte: lastMonth },
...(productId && {
products: { $elemMatch: { product: { _id: productId } } },
}),
},
},
{
$project: {
month: { $month: '$createdAt' },
sales: '$total',
},
},
{
$group: {
_id: '$month',
total: { $sum: '$sales' },
},
},
]);
return income;
}
When I calculate whole sales without productId , it went well , I tried to use elemMatch to find productId , but it won't work , did I miss something ?
Try $unwind "products" array first, then apply $match:
const income = await this.order.aggregate([
{
$unwind: '$products'
},
{
$match: {
createdAt: { $gte: lastMonth },
product: { _id: productId },
},
},
{
$project: {
month: { $month: '$createdAt' },
sales: '$total',
},
},
{
$group: {
_id: '$month',
total: { $sum: '$sales' },
},
},
]);