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?

Electric

$project it on the next stage in the pipeline – Autarchy

{ $addFields: { newName: "$_id" } }, { $project: { _id: 1 } } – Gassaway

Answer 1

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

Replicate

Answer 2

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 }
}

Mendelsohn

Did not work for me, I get $projection requires at least one output field. (using it in an aggregate) – Ursal

Clarification: I'm using DocumentDB in AWS – Ursal

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. – Orenorenburg

Answer 3

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

Chucho

Answer 4

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.

Selfidentity

Answer 5

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"
        }
    }
]);

Philately

Answer 6

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();

Symbolist

Answer 7

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")))

Saxony

Answer 8

You can do so with the $first and $project aggregation operators.

1. For each group, create a field called name that is a duplicate of the name field in the group's first result. Explanation: Because we grouped by name, then the name value in the first result will always match the group's _id value. (See answer)
2. Remove the unnecessary _id field from each group.

db.collection.aggregate([
  {
    $group: {
      _id: "$name",
      name: { 
        $first: "$name" 
      }
    }
  },
  {
    $project: {
      _id: 0
    }
  }
])

And in your case, adding the count and sum accumulator fields:

db.collection.aggregate([
  {
    $group: {
      _id: "$name",
      name: {
        $first: "$name"
      },
      count: {
        $sum: 1
      },
      sum: {
        $sum: {
          $add: [
            "$P31",
            "$P32"
          ]
        }
      }
    }
  },
  {
    $project: {
      _id: 0
    }
  }
])

Try it online: https://mongoplayground.net/p/cJKlYyxqgSa thanks to @felix and @Ashh for their collection example!

Magnolia