I have an ip address collection:
{
"_id" : "uezyuLx4jjfvcqN",
"CVE" : ["CVE2020-123", "CVE2022-789", "CVE2019-456"],
"ip" : "1.2.3.4"
}
{
"_id" : "dCC8GrNdEjym3ryua",
"CVE" : ["CVE2020-123", "CVE2021-469"],
"ip" : "5.6.7.8"
}
{
"_id" : "dCC8GrNdEjym3ryua",
"CVE" : ["CVE2020-123", "CVE2021-469"],
"ip" : "7.6.7.6"
}
I’m trying to calculate the distinct sum of the CVE field, where IPs are in ["5.6.7.8", "1.2.3.4"]
.
Expected output:
{
ip: ['1.2.3.4', '5.6.7.8'],
sum_distinct_cve:4,
CVES: ["CVE2020-123", "CVE2022-789", "CVE2019-456", "CVE2021-469"]
}
So I’m doing the following:
db = db.getSiblingDB("test");
hosts = db.getCollection("test-collection")
hosts.aggregate([
{$match:
{"ip": {$in: ["1.2.3.4", "5.6.7.8"]}}},
{$group:
{_id: "$CVE",
totals: {$sum: "$CVE"}}}
]);
The sum is returning 0, which I’ve realised is because of MongoDb’s behaviour when trying to sum a string field. This is detailed here: mongodb sum query returning zero
What I would like to know though is how I can sum the number of elements, and also find the distinct sum.`
2
Answers
Simple option:
Explained:
Playground
I agree with @R2D2. One more option to avoid
$unwind
(which considered costly in terms of performance) is to use$reduce
instead:See how it works on the playground example