Is it possible to rename _id field after mongo's group aggregation?

Question

I have a query like this (simplified):

db.collection.aggregate([
  { $match: { main_id: ObjectId("58f0f67f50c6af16709fd2c7") } }, 
  {
    $group: {
      _id: "$name",
      count: { $sum: 1 },
      sum: { $sum: { $add: ["$P31", "$P32"] } }
    }
  }
])

I do this query from Java, and I want to map it on my class, but I don't want _id to be mapped on name field. Because if I do something like this:

@JsonProperty("_id")
private String name;

then when I save this data back to mongo (after some modification) the data is saved with name as _id while I want a real Id to be generated.

So, how can I rename _id after $group operation?

[$project](https://docs.mongodb.com/manual/reference/operator/aggregation/project/) it on the next stage in the pipeline — Alex Blex, Apr 21 '17 at 08:56
{ $addFields: { newName: "$_id" } }, { $project: { _id: 1 } } — artemitSoft, Aug 24 '22 at 12:53

score 107 · Accepted Answer · edited Dec 19 '18 at 10:10

107

You can achieve this by adding a $project stage at the end of your pipeline like this :

{ $project: {  
      _id: 0,
      name: "$_id",
      count: 1,
      sum: 1
   }
}

try it online: mongoplayground.net/p/QpVyh-0I-bP

edited Dec 19 '18 at 10:10

Ashh

44,693
14
105
132

answered Apr 21 '17 at 09:18

felix

9,007
7
41
62

score 36 · Answer 2 · answered Feb 08 '18 at 10:17

36

From mongo v3.4 you could use $addFields in conjunction with $project to avoid to write all the fields in $project that could be very tedious.

This happen in $project because if you include specifically a field, the other fields will be automatically excluded.

Example:

{ 
  $addFields: { my_new_id_name: "$_id" }
},
{
  $project: { _id: 0 }
}

answered Feb 08 '18 at 10:17

Manuel Spigolon

11,003
5
50
73

Did not work for me, I get `$projection requires at least one output field.` (using it in an aggregate) – Gonzalo.- Jan 10 '20 at 20:06
Clarification: I'm using DocumentDB in AWS – Gonzalo.- Jan 10 '20 at 20:13
It works fine, but I need to convert _id to string, so `{ $addFields: { id: { $toString: '$_id' } } }` and the order of the pipeline should be respected, first `$addFields` and `$project`. – drocha87 Oct 19 '20 at 19:00

score 4 · Answer 3 · answered Jan 10 '19 at 12:47

4

 db.report.aggregate(   
{     
$group: {_id: '$name'} 
},
{
$project:{
  name:"$_id",
 _id:false} }
 )

answered Jan 10 '19 at 12:47

Prasad

1,089
13
21

score 4 · Answer 4 · answered Dec 04 '21 at 08:51

Starting in Mongo 4.2, you can use a combination of $set / $unset stages:

// { x: 1, z: "a" }
// { x: 2, z: "b" }
db.collection.aggregate([
  { $set: { y: "$x" } },
  { $unset: "x" }
])
// { y: 1, z: "a" }
// { y: 2, z: "b" }

The $set stage adds the new field to documents and the $unset stage removes/excludes the field to be renamed from documents.

score 1 · Answer 5 · answered Oct 21 '18 at 09:17

1

if you are using find method you can't do this, but if you using aggregation it is very easy like this:

db.collectionName.aggregate([
    {
        $project: {
            newName: "$existingKeyName"
        }
    }
]);

answered Oct 21 '18 at 09:17

andranikasl

1,242
9
10

score 1 · Answer 6 · answered Mar 30 '23 at 15:29

Here is working example In Java using MongoTemplate:

GroupOperation groupByMovie = Aggregation.group("movieId")
                .avg("rating").as("averageRating");

        SortOperation sortByAvgRatingDesc = Aggregation.sort(Sort.Direction.DESC, "averageRating");
        LimitOperation limitRows = Aggregation.limit(limit);
        ProjectionOperation projectionOperation = Aggregation.project()
                .and("_id").as("movieId")
                .and("averageRating").as("averageRating")
                .andExclude("_id");

        Aggregation aggregation = Aggregation.newAggregation(
                groupByMovie,
                projectionOperation,
                sortByAvgRatingDesc,
                limitRows
        );

        AggregationResults<TopRatedMovie> results = mongoTemplate.aggregate(aggregation, MovieRating.class, TopRatedMovie.class);

        return results.getMappedResults();

score 0 · Answer 7 · answered Sep 18 '21 at 17:52

As all of the answers are written the solution in MongoDB query despite the question seeks the solution in Java, posting my approach using Java for posterities.

After the grouping, we can rename the _id fieldname using Projections.computed("<expected field name>", "$_id")))

To Transform the core part of the query mentioned in the question to Java

        Bson mainIdMatch = match(eq("main_id", new ObjectId("58f0f67f50c6af16709fd2c7")));
        Bson group = Aggregates.group("$name", Accumulators.sum("count", 1L));
        Bson project = Aggregates.project(Projections.fields(Projections.excludeId(),
                Projections.computed("name", "$_id")));
        reportMongoCollection.aggregate(Arrays.asList(mainIdMatch, group, project))
                .into(new ArrayList<>());

To answer specifically, I have added an excerpt from the above code snippet, where I am renaming _id field value as name using Projections.computed("name", "$_id") which map the values of _id which we got as a result of grouping to the field called name. Also, we should exclude the id using Projections.excludeId().

Aggregates.project(Projections.fields(Projections.excludeId(),
                Projections.computed("name", "$_id")))

Is it possible to rename _id field after mongo's group aggregation?

7 Answers7

Linked