CODEWITHSUNDEEP
mongodbdistinct
LemonMan
LemonMan

Reputation: 3143

MongoDB distinct aggregation

I'm working on a query to find cities with most zips for each state:

db.zips.distinct("state", db.zips.aggregate([ 
    { $group:
      { _id: {
           state: "$state", 
           city: "$city" 
         },
        numberOfzipcodes: { 
           $sum: 1
         }
      }
    }, 
    { $sort: {
        numberOfzipcodes: -1
         }
      }
  ])
)

The aggregate part of the query seems to work fine, but when I add the distinct I get an empty result.

Is this because I have state in the id? Can I do something like distinct("_id.state ?

Upvotes: 97

Views: 243691

Answers (4)

dam1
dam1

Reputation: 3652

You can use $addToSet with the aggregation framework to count distinct objects.

For example:

db.collectionName.aggregate([{
    $group: {_id: null, uniqueValues: {$addToSet: "$fieldName"}}
}])

Or extended to get your unique values into a proper list rather than a sub-document inside a null _id record:

db.collectionName.aggregate([
    { $group: {_id: null, myFieldName: {$addToSet: "$myFieldName"}}},
    { $unwind: "$myFieldName" },
    { $project: { _id: 0 }},
])

Upvotes: 174

Jeroen Ooms
Jeroen Ooms

Reputation: 32978

You can call $setUnion on a single array, which also filters dupes:

{ $project: {Package: 1, deps: {'$setUnion': '$deps.Package'}}}

Upvotes: 6

Surendranath Reddy K
Surendranath Reddy K

Reputation: 681

SQL Query: (group by & count of distinct)

select city,count(distinct(emailId)) from TransactionDetails group by city;

Equivalent mongo query would look like this:

db.TransactionDetails.aggregate([ 
{$group:{_id:{"CITY" : "$cityName"},uniqueCount: {$addToSet: "$emailId"}}},
{$project:{"CITY":1,uniqueCustomerCount:{$size:"$uniqueCount"}} } 
]);

Upvotes: 37

Sammaye
Sammaye

Reputation: 43884

Distinct and the aggregation framework are not inter-operable.

Instead you just want:

db.zips.aggregate([ 
    {$group:{_id:{city:'$city', state:'$state'}, numberOfzipcodes:{$sum:1}}}, 
    {$sort:{numberOfzipcodes:-1}},
    {$group:{_id:'$_id.state', city:{$first:'$_id.city'}, 
              numberOfzipcode:{$first:'$numberOfzipcodes'}}}
]);

Upvotes: 70

Related Questions