Spring Boot Mongo DB Lookup AggregationOperation

Question

First of I want to say am new to MongoDB, especially MongoData with Springs. I have the following collections

Category

@Data
@Document(collection = "categories")
public class Category {
    @Id
    private ObjectId id;
    @CreatedBy
    private ObjectId creatorId;
    @CreatedDate
    private LocalDateTime createdAt;
    @LastModifiedDate
    private LocalDateTime modifiedAt;
    @LastModifiedBy
    private ObjectId modifiedBy;
    private String title;
    private String notes;
    private boolean enabled = true;
}

Activity

@Data
@Document(collection = "activities")
public class Activity {

    @Id
    private ObjectId id;
    @CreatedBy
    private ObjectId creatorId;
    @CreatedDate
    private LocalDateTime createdAt;
    @LastModifiedDate
    private LocalDateTime modifiedAt;
    @LastModifiedBy
    private ObjectId modifiedBy;

    private ObjectId budgetId;
    private ObjectId categoryId;
    private String title;
    private String notes;
    private Double estimatedBudget;
    private boolean enabled;
    private boolean recurring;
}

I have come up with a simple query that joins the two collections which works to my expectation as below (... might also not be super cool query). The idea is i want to get all the activities grouped inside Category based on categoryId. Then afterwards, i only want to retrieve activities having a given budgetId. I should be able to retrieve all Categories irregardless of whether activities within it exists or not, Like LEFT JOIN in SQL

db.categories.aggregate([
{

$lookup: {
        from: "activities",
        localField : "_id",
        foreignField: "categoryId",
        pipeline: [
              {$match: { $and:[{"budgetId": ObjectId("60f8ef1ed7056b730cdce1db") }] }}
        ],
        as: "activities"
    },
},
 {
        $match: {
            $and:[{"enabled": true}]
            }
        }

])

This works somehow. So the question is how to I translate the above query to AggregationOperation in springs?

I have the following, but it excludes the Categories that dont have Activities within it.

  List<AggregationOperation> operations = new ArrayList<>();


        Criteria criteria = Criteria.where("enabled").is(true);

        operations.add(match(criteria));


        Criteria forBudgetActivitiesCriteria =  Criteria.where("activities.budgetId").is(budgetId);


        operations.add(lookup("activities", "_id", "categoryId", "activities"));
        operations.add(match(forBudgetActivitiesCriteria));

        TypedAggregation<Category> aggregation = newAggregation(Category.class, operations);
        AggregationResults<Category> results = mongoTemplate.aggregate(aggregation, Category.class);

Am guessing the problem is here

Criteria.where("activities.budgetId").is(budgetId);

Help. And thanks in advance.

Answer 1

The aggregation is

db.categories.aggregate([
  {
    $lookup: {
      from: "activities",
      let: { "cId": "$_id" },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                { $eq: [ "$budgetId", "abc" ] },
                { $eq: [ "$$cId", "$categoryId" ] },
                { $eq: [ "$enabled", true ] }
              ]
            }
          }
        }
      ],
      as: "join_activities"
    },
    
  }
])

Working Mongo playground

There are two $lookups in mongodb. One is standard lookup which is supported by spring data (LookupOperation class) and the second one is co-related subqueries lookup. Unfortunately spring data doesn't support it. But there is a Trick to convert

We can write it like Bson objects

@Autowired
private MongoTemplate mongoTemplate;

public List<YOUR_CONVERTER_CLASS> test(String value) {

    Aggregation aggregation = Aggregation.newAggregation(
        l-> new Document("$lookup",
                new Document("from","activities")
                .append("let",new Document("cId","$_id"))
                .append("pipeline",
                    Arrays.asList(
                        new Document("$match",
                            new Document("$expr",
                                new Document(
                                    "$and",
                                    Arrays.asList(
                                        new Document("$eq",Arrays.asList("$budgetId","abc"))
                                        .append("$eq",Arrays.asList("$$cId","$categoryId"))
                                        .append("$eq",Arrays.asList("$enabled",true))
                                    )
                                )
                            )
                        )
                    )
                ).append("as","join_activities")
        )
    ).withOptions(AggregationOptions.builder().allowDiskUse(Boolean.TRUE).build());

    return mongoTemplate.aggregate(aggregation, mongoTemplate.getCollectionName(YOUR_COLLECTION_CLASS.class), YOUR_CONVERTER_CLASS.class).getMappedResults();

}

Note : This is not tested, but was written based on working mongo playground. Mongodb 5.0 has the same signature as you posted in post. If you need to convert your query, you can use the trick and convert, Its just a key value pair