CODEWITHSUNDEEP
javamongodbaggregation-frameworkrename
mykola
mykola

Reputation: 1808

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

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?

Upvotes: 68

Views: 50059

Answers (8)

Tal Jacob - Sir Jacques
Tal Jacob - Sir Jacques

Reputation: 655

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!

Upvotes: 0

Sandeep Patel
Sandeep Patel

Reputation: 5148

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

Upvotes: 1

Xavier Guihot
Xavier Guihot

Reputation: 61774

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.

Upvotes: 4

Prasanth Rajendran
Prasanth Rajendran

Reputation: 5542

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

Upvotes: 1

Prasad
Prasad

Reputation: 1157

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

Upvotes: 5

felix
felix

Reputation: 9295

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

Upvotes: 114

andranikasl
andranikasl

Reputation: 1342

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

Upvotes: 1

Manuel Spigolon
Manuel Spigolon

Reputation: 13150

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

Upvotes: 38

Related Questions