DEV Community

William Spader
William Spader

Posted on • Edited on

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
Enter fullscreen mode Exit fullscreen mode

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"
}
Enter fullscreen mode Exit fullscreen mode
Collection B
{
  "_id": ObjectId("..."),
  "label": "Some label"
}
Enter fullscreen mode Exit fullscreen mode

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())
);
Enter fullscreen mode Exit fullscreen mode

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)
  );
}
Enter fullscreen mode Exit fullscreen mode

The projectionOperation may be

ProjectionOperation projectionOperation = Aggregation.project(
  "info", "anotherInfo"
).and(ConvertOperators.ToObjectId.toObjectId("$otherCollectionId")).as("convertedId");
Enter fullscreen mode Exit fullscreen mode

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");
}
Enter fullscreen mode Exit fullscreen mode

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");
}
Enter fullscreen mode Exit fullscreen mode

Now, we add the label property from the collectionB

private AddFieldsOperation addLabelField() {
  return AddFieldsOperation.addField("label").withValue("$joinedData.label").build();
}
Enter fullscreen mode Exit fullscreen mode

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");
Enter fullscreen mode Exit fullscreen mode

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())
Enter fullscreen mode Exit fullscreen mode

Hope this article helps!

Top comments (0)