Aggregate and paginate over inner array with SpringData MongoDB

Question

I need to paginate over documents contained in arrays of the root documents. For example, to view the first 10 orders with an open status across customers.

Customer structure

{
 "_id": ObjectId("cust-1"),
 "name": "John Doe",
 "orders": [
 {
 "_id": ObjectId("order-1"),
 "status": "open" 
 },
 {
 "_id": ObjectId("order-2"),
 "status": "closed" 
 }
 ]
},
{
 "_id": ObjectId("cust-2"),
 "name": "Jane Doe",
 "orders": [
 {
 "_id": ObjectId("order-3"),
 "status": "open" 
 },
 {
 "_id": ObjectId("order-4"),
 "status": "open" 
 },
 {
 "_id": ObjectId("order-5"),
 "status": "open" 
 },
 ]
}

A page request for page 0, limit 3 should result in John Doe's customer record containing order-1 and Jane Doe's record containing order-3 and order-4. For example

[{
 "_id": ObjectId("cust-1"),
 "name": "John Doe",
 "orders": [
 {
 "_id": ObjectId("order-1"),
 "status": "open" 
 }
 ]
},
{
 "_id": ObjectId("cust-2"),
 "name": "Jane Doe",
 "orders": [
 {
 "_id": ObjectId("order-3"),
 "status": "open" 
 },
 {
 "_id": ObjectId("order-4"),
 "status": "open" 
 },
 ]
}]

To do this, I unwind orders and count, apply page request, group results by _id and project orders and name.

My code

// Operations
UnwindOperation unwindOperation = Aggregation.unwind("orders");
CountOperation countOperation = Aggregation.count().as("total");
SkipOperation skipOperation = Aggregation.skip(pageable.getOffset());
LimitOperation limitOperation = Aggregation.limit(pageable.getPageSize());
GroupOperation groupOperation = Aggregation.group("_id")
 .push("orders").as("orders")
 .push("name").as("name");
ProjectionOperation projectionOperation = Aggregation.project()
 .andInclude("orders")
 .andInclude("name");
// Facet
FacetOperation facetOperation = Aggregation
 .facet(
 unwindOperation,
 countOperation)
 .as("metadata")
 .and(
 unwindOperation,
 skipOperation, 
 limitOperation,
 groupOperation,
 projectionOperation)
 .as("data");
// Aggregation
Aggregation aggregation = Aggregation.newAggregation(
 Aggregation.match(criteria),
 facetOperation
);
AggregationResults<CaseReportFacetResult> result = mongoTemplate.aggregate(aggregation, Customer.class, CustomerFacetResult.class);
CustomerFacetResult fetchResult = result.getUniqueMappedResult();

Problem This works, but in the result set, the name field of the customer is duplicated for each open order. For example Jane Doe's name is returned as "name": "Jane Doe, Jane Doe".

I have removed name from the grouping, but then it is not able to be projected. If removed from both, then the data is not returned at all.

Answer 1

Instead of push("name") ({ $push: "$name" }) which adding the name value into an array, you should use first("name") to get the first/single name value only.

GroupOperation groupOperation = Aggregation.group("_id")
 .push("orders").as("orders")
 .first("name").as("name");

• Half_Duplex: Thanks, I see now that it was coming back as an array, didn't notice that before.