MongoDB aggregation Query;: find documents having count more than 1

Question

Scenario

Find documents and return specific fields if there are uniquekey field that has more than 1 occurrences in the collection. in the sample data uniqueKey 100 and 800 are having more than 1 occurrences.

Sample data and query can be found here:

https://mongoplayground.net/p/FZ15zpXffUh

JSON documents:

[
  {
    "cust_id": 1,
    "cust_name": "cust_1",
    "uniqueKey": 100
  },
  {
    "cust_id": 2,
    "cust_name": "cust_2",
    "uniqueKey": 400
  },
  {
    "cust_id": 3,
    "cust_name": "cust_3",
    "uniqueKey": 100
  },
  {
    "cust_id": 4,
    "cust_name": "cust_4",
    "uniqueKey": 800
  },
  {
    "cust_id": 5,
    "cust_name": "cust_5",
    "uniqueKey": 800
  }
]

Current attempt:

db.collection.aggregate([
  {
    "$group": {
      "_id": "$uniqueKey",
      "count": {
        "$sum": 1
      }
    }
  },
  {
    "$match": {
      count: {
        "$gt": 1
      }
    }
  },
  {
    "$project": {
      "count": 1,
      "cust_name": 1
    }
  }
])

This returns below output and count match works correctly but not projection

{
    "_id": 100,
    "count": 2
},
{
    "_id": 800,
    "count": 2
}

Expected output:

{ 
    cust_name: cust_1,
    uniqueKey: 100
}
{
    cust_name: cust_3,
    uniqueKey: 100
}
{  
    cust_name: cust_4,
    uniqueKey: 800
}
{  
    cust_name: cust_5,
    uniqueKey: 800
}

Any help appreciated for the correct aggregation query to get the expected output. I tried various aggregation samples yet to arrive at a working solution

Answer 1

Since mongoDB version 5.0, one option is to use $setWindowFields:

db.collection.aggregate([
  {$setWindowFields: {
      "partitionBy": "$uniqueKey",
      "sortBy": {"cust_id": 1},
      "output": {
        "count": {
          "$sum": 1,
          "window": {documents: ["unbounded", "unbounded"]
          }
        }
      }
  }},
  {"$match": {count: {"$gt": 1}}},
  {"$project": {_id: 0, "uniqueKey": 1, "cust_name": 1}}
])

See how it works on the playground example

This will prevent pushing all the documents with the same uniqueKey into one big document.

Before mongoDB 5.0:

One option is to use $group, but you need to use $push on your $group and $unwind afterwards. This option may be problematic if you have many documents with the same uniqueKey