CODEWITHSUNDEEP
mongodbmongoosesails-mongo
Sambhav Sharma
Sambhav Sharma

Reputation: 5860

MongoDB Query group and distinct together

Consider the following set of documents: 

[
  {
    "name" : "nameA",
    "class" : "classA",
    "age" : 24,
    "marks" : 45
  },
  {
    "name" : "nameB",
    "class" : "classB",
    "age" : 22,
    "marks" : 65
  },
  {
    "name" : "nameC",
    "class" : "classA",
    "age" : 14,
    "marks" : 55
  }
]

I need to fetch the min and max values for age and marks as well as the distinct values for name and class.

I know that I can use aggregate and group to get the max and min values of age and marks with one query, and I can get distinct values of name and class using distinct query.

But I don't want to do separate queries to fetch that information. Is there a way in which I can get the result with one query? Let's say if I can merge the aggregate and distinct somehow.

Upvotes: 2

Views: 1956

Answers (1)

Christian P
Christian P

Reputation: 12240

Sure, you can do it with one aggregation command. You need to use $group with $addToSet operator:

db.collection.aggregate([{ 
    $group : {
        _id : null,
        name : { $addToSet : "$name" },
        class : { $addToSet : "$class" },
        ageMin : { $min : "$age" },
        ageMax : { $max : "$age" },
        marksMin : { $min : "$marks" },
        marksMax : { $max : "$marks" }
    }
}]);

$addToSet will create an array with unique values for the selected field.

This aggregation will return the following response for your example docs:

{
    "_id" : null,
    "name" : [
        "nameC",
        "nameB",
        "nameA"
    ],
    "class" : [
        "classB",
        "classA"
    ],
    "ageMin" : 14,
    "ageMax" : 24,
    "marksMin" : 45,
    "marksMax" : 65
}

Upvotes: 3

Related Questions