Spring Data MongoDB Aggregation - rename _id in projection

Question

In the mongo (3.6) shell I can execute the following aggregation to project and rename "_id" to something else:

db.collection.aggregate([
  { $group : { _id : ... },
  { $project : {   '_id': 0,        // exclude the id field
                   'name' : '$_id', // project the "old" id value to the "name" field
                   ...
               }
  }
])

I need to translate this using spring-data-mongo (via Spring Boot 2.3.1). I can exclude the id field with the following ProjectionOperation:

project().andExclude("_id")

But so far I've been unabled to rename the id field. None of the following works:

my first guess
```
  project().and("_id").as("name")
```

as suggested here

  project().andExpression("_id").as("name")

It shouldn't be that hard, but I can't figure out what I'm missing.

EDIT: data sample and full aggregation pipeline to reproduce failure

With the following documents:

{ 
    "_id" : ObjectId("5a0c7a3135587511c9247db4"), 
    "_class" : "task", 
    "category" : "green", 
    "status" : "OK"
}
{ 
    "_id" : ObjectId("5a0cd21d35587511c9247db8"), 
    "_class" : "task", 
    "category" : "red", 
    "status" : "KO"
}

Domain object:

@Document(collection = "tasks")
@TypeAlias("task")
public class Task {

    @Id
    private String id;
    private String status;
    private String category;

    // getters/setters omitted
}

And the following aggregation:

Aggregation a = Aggregation.newAggregation(
                group("status", "category").count().as("count"),
                group("_id.category").push(
                        new Document("k", "$_id.status").append("v", "$count"))
                        .as("counts"),
                project().and(arrayToObject("$counts")).as("counts"),
                // this final stage fails with: a java.lang.IllegalArgumentException: Invalid reference '_id'!
                project().and("_id").as("name").andExclude("_id")
        );

mongotemplate.aggregate(a, "tasks", org.bson.Document.class)

Before the last pipeline stage, the aggregation gives :

[ { "_id" : "green", "counts" : { "OK" : 1 } },
  { "_id" : "red", "counts" : { "KO" : 1 } } ]

So we should able to project "_id" to "name", but instead we get this:

java.lang.IllegalArgumentException: Invalid reference '_id'!
    at org.springframework.data.mongodb.core.aggregation.ExposedFieldsAggregationOperationContext.getReference(ExposedFieldsAggregationOperationContext.java:114) ~[spring-data-mongodb-3.0.1.RELEASE.jar:3.0.1.RELEASE]
    at org.springframework.data.mongodb.core.aggregation.ExposedFieldsAggregationOperationContext.getReference(ExposedFieldsAggregationOperationContext.java:77) ~[spring-data-mongodb-3.0.1.RELEASE.jar:3.0.1.RELEASE]
    at org.springframework.data.mongodb.core.aggregation.ProjectionOperation$ProjectionOperationBuilder$FieldProjection.renderFieldValue(ProjectionOperation.java:1445) ~[spring-data-mongodb-3.0.1.RELEASE.jar:3.0.1.RELEASE]
    at org.springframework.data.mongodb.core.aggregation.ProjectionOperation$ProjectionOperationBuilder$FieldProjection.toDocument(ProjectionOperation.java:1432) ~[spring-data-mongodb-3.0.1.RELEASE.jar:3.0.1.RELEASE]
    at org.springframework.data.mongodb.core.aggregation.ProjectionOperation.toDocument(ProjectionOperation.java:261) ~[spring-data-mongodb-3.0.1.RELEASE.jar:3.0.1.RELEASE]
    at org.springframework.data.mongodb.core.aggregation.AggregationOperation.toPipelineStages(AggregationOperation.java:55) ~[spring-data-mongodb-3.0.1.RELEASE.jar:3.0.1.RELEASE]
    at org.springframework.data.mongodb.core.aggregation.AggregationOperationRenderer.toDocument(AggregationOperationRenderer.java:56) ~[spring-data-mongodb-3.0.1.RELEASE.jar:3.0.1.RELEASE]
    at org.springframework.data.mongodb.core.aggregation.Aggregation.toPipeline(Aggregation.java:721) ~[spring-data-mongodb-3.0.1.RELEASE.jar:3.0.1.RELEASE]
    at org.springframework.data.mongodb.core.AggregationUtil.createPipeline(AggregationUtil.java:95) ~[spring-data-mongodb-3.0.1.RELEASE.jar:3.0.1.RELEASE]
    at org.springframework.data.mongodb.core.MongoTemplate.doAggregate(MongoTemplate.java:2118) ~[spring-data-mongodb-3.0.1.RELEASE.jar:3.0.1.RELEASE]
    at org.springframework.data.mongodb.core.MongoTemplate.aggregate(MongoTemplate.java:2093) ~[spring-data-mongodb-3.0.1.RELEASE.jar:3.0.1.RELEASE]
    at org.springframework.data.mongodb.core.MongoTemplate.aggregate(MongoTemplate.java:1992) ~[spring-data-mongodb-3.0.1.RELEASE.jar:3.0.1.RELEASE]

I already found a workaround (see below), but I'm still curious why the aggregation shown above fails. I don't get why the "_id" reference is not exposed properly.

The successful workaround:

Aggregation a = Aggregation.newAggregation(
            group("status", "category").count().as("count"),
            group("_id.category").push(
                    new Document("k", "$_id.status").append("v", "$count"))
                    .as("counts"),
            project().and(arrayToObject("$counts")).as("counts"),
            // the following works
            addFields().addFieldWithValue("name", "$_id").build(),
            project().andExclude("_id").andExclude("counts")
    );

For anyone needing a workaround when they can't figure out who to convert a MongoDB aggregation to a Spring Data Mongo aggregation: https://stackoverflow.com/a/59726492/5873923

Valijon · Accepted Answer · 2020-06-23T07:41:56.053

5

Try this one:

project("_id").and(arrayToObject("$counts")).as("counts"),
project().andExclude("_id").and("_id").as("name")

EDIT:

Spring-Mongo does some validations of your pipeline and expects _id is present in your previous stage, so that's the reason of java.lang.IllegalArgumentException: Invalid reference '_id'!

Aggregation agg = Aggregation.newAggregation(
        group("status", "category").count().as("count"),
        group("_id.category").push(new Document("k", "$_id.status").append("v", "$count")).as("counts"),
        project("_id").and(ArrayOperators.arrayOf("$counts").toObject()).as("counts"),
        project().and("_id").as("name").andExclude("_id"));

AggregationResults<Document> results = mongoTemplate.aggregate(agg, "tasks", Document.class);
results.getMappedResults().forEach(System.out::println);

---Output---

Document{{name=green}}
Document{{name=red}}

edited Jun 23 '20 at 07:41

answered Jun 20 '20 at 09:57

Valijon

12,667
4
34
67

That's what I tested first. But as I said in my post, the `and("_id").as("name")` part does not provide the expected result. – Marc Tarin Jun 20 '20 at 12:07
Something crashes along the way when I use `project().andExclude("_id").and("_id").as("name")`... I will dig into it after a good night sleep ;) – Marc Tarin Jun 20 '20 at 22:21
Your example (and many others) indeed works as expected, but check out my update for a tricky one that fails. – Marc Tarin Jun 22 '20 at 22:33
@MarcTarin How do you perform aggregation (`mongotemplate.aggregate(what parameters here?)`)? – Valijon Jun 23 '20 at 00:11
I updated my post with the domain object and the call to mongotemplate.aggregate(...): `mongotemplate.aggregate(a, "tasks", org.bson.Document.class)`. – Marc Tarin Jun 23 '20 at 07:30
Great! I assumed the "_id" field was always projected unless explicitly excluded, because it is how it works in the mongo shell. – Marc Tarin Jun 23 '20 at 08:08
@MarcTarin Since it's Spring-Mongo, there is no any explicity, everything should be declared and used in "their" syntax :) – Valijon Jun 23 '20 at 08:13
I'll keep that in mind... or use raw aggregation :) Thanks a lot! – Marc Tarin Jun 23 '20 at 08:20
I don't get the `arrayToObject` part, but if we skip/ignore that, this single line solution works as well: `project("count").andExclude("_id").and("_id").as("name")` or if you want to make it more readable: `project().and("_id").as("name").andExclude("_id").andInclude("count")` – ktamas May 03 '23 at 10:21

Spring Data MongoDB Aggregation - rename _id in projection

1 Answers1