Hi @sandeep_bolchetwar and welcome to MongoDB community forums!!
I see that your post has been unattended in the past few days and I hope you have been able to find the solution to the above.
In which case it would be helpful if you could post the solution for the greater community.
However, in a situation where you have not been able to solve the above or for the community, you can try the code snippet below:
Based on the above aggregation pipeline, I tried to create some sample data that look like:
Atlas atlas-xp4gev-shard-0 [primary] atest> db.salesOrder.findOne()
{
_id: 2,
auditInfo: { creationTs: ISODate(‘2022-02-10T12:30:00.000Z’) },
lineItems: [ { item: ‘Product B’, quantity: 3 } ],
header: { totalAmountDue: 150 }
}
and the output for the above aggregation would look like:
Atlas atlas-xp4gev-shard-0 [primary] atest> db.salesOrder.aggregate([ { $match: { 'auditInfo.creationTs': { "$gte": ISODate("2022-01-01T00:00:00.000Z"), "$lt": ISODate("2023-01-01T00:00:00.000Z") } } }, { '$addFields': { 'lineCount': { '$size': '$lineItems' } } }, { $group: { _id: { year: { $year: "$auditInfo.creationTs" }, month: { $month: "$auditInfo.creationTs" } }, totalLineCount: { $sum: "$lineCount" }, totalSalesAmount: { $sum: "$header.totalAmountDue" }, count: { $sum: 1 } } }, { $sort: { _id: 1 } }])
[
{
_id: { year: 2022, month: 1 },
totalLineCount: 1,
totalSalesAmount: 100,
count: 1
},
{
_id: { year: 2022, month: 2 },
totalLineCount: 1,
totalSalesAmount: 150,
count: 1
},
{
_id: { year: 2022, month: 3 },
totalLineCount: 1,
totalSalesAmount: 50,
count: 1
},
{
_id: { year: 2022, month: 4 },
totalLineCount: 1,
totalSalesAmount: 200,
count: 1
},
{
_id: { year: 2022, month: 5 },
totalLineCount: 1,
totalSalesAmount: 100,
count: 1
},
{
_id: { year: 2022, month: 6 },
totalLineCount: 1,
totalSalesAmount: 180,
count: 1
},
{
_id: { year: 2022, month: 7 },
totalLineCount: 1,
totalSalesAmount: 120,
count: 1
},
{
_id: { year: 2022, month: 8 },
totalLineCount: 1,
totalSalesAmount: 90,
count: 1
},
{
_id: { year: 2022, month: 9 },
totalLineCount: 1,
totalSalesAmount: 0,
count: 1
}
]
for the above, I tried to create a rest API which would output the above output.
public List<AggregatedResult> performAggregation() {
MatchOperation matchStage = Aggregation.match(Criteria.where("auditInfo.creationTs")
.gte("2022-01-01T00:00:00.000Z")
.lt("2023-01-01T00:00:00.000Z"));
AddFieldsOperation addFields = Aggregation.addFields().addField("lineCount").withValueOf("$size").build();
GroupOperation group = Aggregation.group(Fields.from(Fields.field("auditInfo.creationTs"),
Fields.field("lineItems"),
Fields.field("header.totalAmountDue")));
ProjectionOperation project = Aggregation.project()
.and(
context -> new Document("$year", context.getReference("auditInfo.creationTs"))
).as("auditInfo.year")
.and(
context -> new Document("$month", context.getReference("auditInfo.creationTs"))
).as("auditInfo.month")
.andExpression("lineCount").as("totalLineCount").andExpression("header.totalAmountDue").as("totalSalesAmount").andExpression("1").as("count");
SortOperation sort = Aggregation.sort(Sort.Direction.ASC, "_id");
Aggregation aggregation = newAggregation(matchStage, addFields, group, project, sort);
AggregationResults<AggregatedResult> results = mongoTemplate.aggregate(aggregation, "salesOrder", AggregatedResult.class);
return results.getMappedResults();
}
Let us know if the above query works for you.
Regards
Aasawari