Spring Data & MongoDB — Pagination+Sorting w/ Aggregation and Lookup
Tags: springboot, mongodb, springdata
Published: 2023-12-12 15:56:08

**Problem:** Need to perform pagination+sorting with nullable filters and properties from two collections.

One collection holds the ID of the document from another collection that has the property we need to retrieve.

For those using reactive mongodb, the ReactiveMongoRepository only extends from ReactiveSortingRepository and we also want the following behavior:

```
a: { otherCollectionId: 1, info: 2}
b: { otherCollectionId: 1, info: 3}
c: { otherCollectionId: 2, info: 1}
findByOtherCollectionIdAndInfo(1, 2) //returns document a
findByOtherCollectionIdAndInfo(1, null) //returns documents a and b
```

**Solution**

Consider you have the following two collections where collection A has the id from collection B through otherCollectionId property.

```
Collection A
{
  "_id": ObjectId("..."),
  "otherCollectionId": "...",
  "info": "Some info",
  "anotherInfo": "Some other info"
}
```

```
Collection B
{
  "_id": ObjectId("..."),
  "label": "Some label"
}
```
Your API needs to respond to the client containingcollectionA infos + the label property from collectionB, and it must be paginated with the possibility of sorting through the label property.

To get the chunk of data, we can use the following code

```
mongoTemplate.aggregate(aggregation, "collectionA", Result.class)
return Aggregation.newAggregation(
  Aggregation.match(getCriteria(filter)),
  projectionOperation,
  getLookupOperation(),
  getUnwindOperation(),
  addLabelField(),
  projectionOperationWithLabel,
  Aggregation.sort(pageable.getSort()),
  Aggregation.skip((long) pageable.getPageNumber() * pageable.getPageSize()),
  Aggregation.limit(pageable.getPageSize())
);
```
The getCriteria(filter) method may be

```
private Criteria getCriteria(BusinessObject.Filter filter) {
  List<Criteria> criterias = new ArrayList<>();

  Optional.ofNullable(filter.getInfo())
    .ifPresent(info-> criterias.add(Criteria.where("info").is(info)));

  Optional.ofNullable(filter.getOtherCollectionId())
    .ifPresent(otherCollectionId -> criterias.add(Criteria.where("otherCollectionId").is(otherCollectionId)));

  return new Criteria().andOperator(
    criterias.toArray(Criteria[]::new)
  );
}
```
The projectionOperation may be

```
ProjectionOperation projectionOperation = Aggregation.project(
  "info", "anotherInfo"
).and(ConvertOperators.ToObjectId.toObjectId("$otherCollectionId")).as("convertedId");
```
In the project operation above, we need to convert otherCollectionId property to ObjectId before the lookup, so we can compare same data type.

Then, getLookupOperation() method may be

```
private LookupOperation getLookupOperation() {
  return LookupOperation.newLookup()
          .from("collectionB")
          .localField("convertedId")
          .foreignField("_id")
          .as("joinedData");
}
```
The convertedId property is the otherCollectionId but as ObjectId.

Then, depending on the situation, you can flat the resulting array from the lookup operation like this

```
private UnwindOperation getUnwindOperation() {
  return Aggregation.unwind("$joinedData");
}
```
Now, we add the label property from the collectionB

```
private AddFieldsOperation addLabelField() {
  return AddFieldsOperation.addField("label").withValue("$joinedData.label").build();
}
```
Then, we can perform another project operation containing the label property and remove the joinedData to get only the data we need. We also perform another conversion to get otherCollectionId property.

```
ProjectionOperation projectionOperation = Aggregation.project(
  "info", "anotherInfo", "label"
).and(ConvertOperators.ToObjectId.toObjectId("$convertedId")).as("otherCollectionId");
```
Finally, if you need the total count to return something like PageImpl<Item> from the API, you can perform a count query on the collectionA.

Then, you’ll have something like this to return from the repository, where it.getT1() is the list of items and it.getT2() is the total of elements:

```
return new PageImpl<>(it.getT1(), pageable, it.getT2())
```
Hope this article helps!