Mongoose aggregate the property of subdocument and display the result

Question

I have a document with a subdocument (not referenced). I want to apply the aggregation on the field of the subdocument.

Schema

const MFileSchema = new Schema({
  path: String,
  malwareNames: [String],
  title: String,
  severity: String // i want to aggregate bases on this field
});

const ScanSchema = new Schema({
  agent: { type: Schema.Types.ObjectId, ref: "Agent" },
  completedAt: Date,
  startedAt: { type: Date, default: Date.now() },
  mFiles: [MFileSchema] // array of malicious files schema
});

Model

let Scan = model("Scan", ScanSchema);

Task

Find the sum of severity in all scan documents of particular agents.

// agents is an array Agents (the schema is not important to show, consider the _id)

The Aggregation Query I am using

let c = await Scan.aggregate([
        { $match: { agent: agents } },
        { $project: { "mFiles.severity": true } },
        { $group: { _id: "$mFiles.severity", count: { $sum: 1 } } }
]);

console.log(c);

Actual Output

[]

Expected Output

// The value of count in this question is arbitrary

[
    { _id: "Critical", count: 30 },
    { _id: "Moderate", count: 33 },
    { _id: "Clean", count: 500 }
]

PS: Also I would appreciate if you could suggest me the best resources to learn MongoDB aggregations

Answer 1

You need to use $in query operator in the $match stage, and add $unwind stage before $group stage.

db.collection.aggregate([
  {
    $match: {
      agent: {
        $in: [
          "5e2c98fc3d785252ce5b5693",
          "5e2c98fc3d785252ce5b5694"
        ]
      }
    }
  },
  {
    $project: {
      "mFiles.severity": true
    }
  },
  {
    $unwind: "$mFiles"
  },
  {
    $group: {
      _id: "$mFiles.severity",
      count: {
        $sum: 1
      }
    }
  }
])

Playground

Sample data:

[
  {
    "agent": "5e2c98fc3d785252ce5b5693",
    "mFiles": [
      {
        "title": "t1",
        "severity": "Critical"
      },
      {
        "title": "t2",
        "severity": "Critical"
      },
      {
        "title": "t3",
        "severity": "Moderate"
      },
      {
        "title": "t4",
        "severity": "Clean"
      }
    ]
  },
  {
    "agent": "5e2c98fc3d785252ce5b5694",
    "mFiles": [
      {
        "title": "t5",
        "severity": "Critical"
      },
      {
        "title": "t6",
        "severity": "Critical"
      },
      {
        "title": "t7",
        "severity": "Moderate"
      }
    ]
  }
]

Output:

[
  {
    "_id": "Moderate",
    "count": 2
  },
  {
    "_id": "Critical",
    "count": 4
  },
  {
    "_id": "Clean",
    "count": 1
  }
]

For mongoose integration:

//agents must be an array of objectIds like this 
//   [ObjectId("5e2c98fc3d785252ce5b5693"), ObjectId("5e2c98fc3d785252ce5b5694")]
//or ["5e2c98fc3d785252ce5b5693","5e2c98fc3d785252ce5b5694"]


const ObjectId = require("mongoose").Types.ObjectId;

let c = await Scan.aggregate([
  {
    $match: {
      agent: {
        $in: agents 
      }
    }
  },
  {
    $project: {
      "mFiles.severity": true
    }
  },
  {
    $unwind: "$mFiles"
  },
  {
    $group: {
      _id: "$mFiles.severity",
      count: {
        $sum: 1
      }
    }
  }
]);

Best place for learning mongodb aggregation is the official docs.