Murakami
Murakami

Reputation: 3770

Count unique documents in MongoDB

This is my query to count documents in collection based on fullDate & key fields :

Collection.aggregate([
  { $match: { $and: [
      { month },
      { year },
      { isBooked },
    ] }},
  { $group: { _id: "$fullDate", count: { $sum: 1 } } }
]

Let's say that this is filtered collection (after first stage i.e; $match) :

{ month: 5, year: 2012, isBooked: true, fullDate: 2020/7/5, key: 123qaz }
{ month: 5, year: 2012, isBooked: true, fullDate: 2020/7/5, key: 123qaz }
{ month: 5, year: 2012, isBooked: true, fullDate: 2020/7/5, key: 223qaz }
{ month: 5, year: 2012, isBooked: true, fullDate: 2020/7/5, key: 323qaz }

The above query would return 2020/7/5: 4. I need to modify the query in order to get 2020/7/5: 3 as there are two documents with the same key of 123qaz. I don't want to count duplicates by key.

Upvotes: 1

Views: 60

Answers (1)

whoami - fakeFaceTrueSoul
whoami - fakeFaceTrueSoul

Reputation: 17935

Try this :

Collection.aggregate([{
    $match: {
        $and: [
            { month },
            { year },
            { isBooked },
        ]
    }
},
/** group by fullDate & push unique keys to an array */
{ $group: { _id: "$fullDate", uniqueKeys: { $addToSet: '$key' } } },
/** project a new field 'count' which is size of uniqueKeys, _id is projected by default,
 *  Use $addFields in place of project if multiple fields to be projected */
{ $project: { count: { $size: '$uniqueKeys' } } }])

Test : MongoDB-Playground

Upvotes: 2

Related Questions