Spring Data & MongoDB — Pagination+Sorting w/ Aggregation and Lookup

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 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!