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
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