Wrong reference in Spring MongoDB data aggregation

Question

I have mongodb documents of following kind:

{
"_id" : {
    "refId" : ObjectId("55e44dd70a975a6fec7ae66e"),
    "someString" : "foo"
},
"someValue" : 1,
"date" : NumberLong("1441025536869"),
"subdoc" : {
    "someRef" : ObjectId("xf2h55e44dd70a975a6fec7a"),
    "count" : 99
}
}

Want to get list of documents with unique "subdoc.someRef" with earliest "date" (if there are any documents with same "subdoc.someRef") and sorted by any field. The issue is in sorting by field "subdoc.count". I use following spring data aggregation:

Aggregation agg = Aggregation.newAggregation(
            Aggregation.match(match),
            Aggregation.project("date", "someValue")
                    .and("subdoc.someRef").as("someRef")
                    .and("subdoc.count").as("count")
                    .and("_id.someString").as("someString")
                    .and("_id.refId").as("refId"),
            Aggregation.sort(Sort.Direction.ASC, "date"),
            Aggregation.group("someRef")
                    .min("date").as("date")
                    .first("refId").as("refId")
                    .first("someValue").as("someValue")
                    .first("count").as("count"),
            Aggregation.project("someValue", "date", "refId", "count")
                    .and("_id").as("someRef"),
            Aggregation.sort(pageable.getSort()),
            Aggregation.skip(pageable.getOffset()),
            Aggregation.limit(pageable.getPageSize())
    );

Everething is fine except how spring data converts: .first("count").as("count") I always got "count" : null in aggregation result. DBObject created by spring is not what I've expected. That line in log concerns me:

"$group" : { "_id" : "$someRef" , "date" : { "$min" : "$date"} , "refId" : { "$first" : "$_id.refId"} , "someValue" : { "$first" : "$someValue"} , "count" : { "$first" : "$subdoc.count"}}}

I cannot understand why it always puts "$subdoc.count" instead of putting "$count" as result of previous step of aggreagation pipeline. So "count" in result is always null as "$subdoc.count" is always null in last grop step. How to make Spring use the value that I want instead of putting reference?

What is killing you here are the `$project` stages, of which neither you actually need. Each aggregation pipeline stage actually has to pass through all of the data available to that stage, and that means it takes up time and resources. So rather your pipeline should be `[match,sort,group,sort,skip,limit]` without any renaming of keys. Just reference `"subdoc.field"` where required in `group`, of course using `.as()` for "non-dotted" names ( since that is not allowed in `$group` ). It's better an faster. Also, not much point in `$min` after sorting and when other fields are all `$first`. — Blakes Seven, Mar 15 '16 at 04:26
Thanks @BlakesSeven, I missed that it is possible to use dotted names in `first()` operator — user1376983, Mar 15 '16 at 13:05
The point is really that you **shouldn't**. There is no need to do so and therefore the "correct" way to do it is to remove the `$project` stages altogether. The problem here is that `spring-mongo` "remembers" the original document structure, and therefore makes reference back to what that was. But it is more efficient to not have the additional stages, and therefore it should not apply here. — Blakes Seven, Mar 15 '16 at 22:17

user1376983 · Answer 1 · 2016-03-15T13:15:45.570

Thanks to comment, the solution is to eliminate projections and updating group stage. Grouping work well with subdocument references, that I didn't expected:

    Aggregation agg = Aggregation.newAggregation(
            Aggregation.match(match),
            Aggregation.sort(Sort.Direction.ASC, "date"),
            Aggregation.group("subdoc.someRef")
                    .first("subdoc.count").as("count")
                    .first("_id.refId").as("refId")
                    .first("_id.someString").as("someString")
                    .first("date").as("date")
                    .first("someValue").as("someValue"),
            Aggregation.sort(pageable.getSort()),
            Aggregation.skip(pageable.getOffset()),
            Aggregation.limit(pageable.getPageSize())
    );

Wrong reference in Spring MongoDB data aggregation

1 Answers1