How can I group by a string instead of ObjectId in MongoDB aggregate?-CodePudding

I have two collections and a many-to-one relationship between them:

Product:

 "_id" : ObjectId("61cc81c9585946c3b44f24411"),
        "name" : "some random name",
        "price" : 100,
        "description" : "description",
        "category_id" : ObjectId("61cc8100585946c3b44f2317d")

Category:

{
        "_id" : ObjectId("61cc8100585946c3b44f2317d"),
        "description" : "Category description",
        "name" : "Electronics"
}

I would like to output the maximum product price for each category:

db.product.aggregate([ 
    { "$group": { 
        "_id": "$category_id",
        "max": { "$max": "$price"} 
    }}
])

This works just fine as it prints me the following:

{ "_id" : ObjectId("61cc80fb585946c3b44f697c"), "max" : 62}
{ "_id" : ObjectId("61cc8100585946c3b44f697d"), "max" : 100}

But is there a way to get the "name" from the Category instead of its object id?

I know in SQL you would group by category_name but it does not seem to work here.

CodePudding user response：

As suggested by @prasad, you should make use of $lookup stage after your $group stage.

db.product.aggregate([
  {
    "$group": {
      "_id": "$category_id",
      "max": {
        "$max": "$price"
      }
    }
  },
  {
    "$lookup": {
      "from": "category",
      "localField": "_id",
      "foreignField": "_id",
      "as": "categoryName",
      
    }
  },
  {
    "$set": {
      "categoryName": {
        "$arrayElemAt": [
          "$categoryName.name",
          0
        ]
      }
    }
  }
])

Mongo Playground Sample