MongoDB: Gathering queries and grouping them under a common key

Question

Say I had the following data:

db.warnings.insertMany([
    { "_id" : 1, "target_type": "controller", "target" : { "name": "controller_A" }, "warning_type" : "offline", "status" : "critical", "is_active": true },
    { "_id" : 2, "target_type": "controller", "target" : { "name": "controller_A" }, "warning_type" : "battery", "status" : "critical", "is_active": false },
    { "_id" : 3, "target_type": "controller", "target" : { "name": "controller_B" }, "warning_type" : "offline", "status" : "minor",  "is_active": false },
    { "_id" : 4, "target_type": "controller", "target" : { "name": "controller_B" }, "warning_type" : "battery", "status" : "major",  "is_active": true },
    { "_id" : 5, "target_type": "light", "target" : { "name": "light_A" }, "warning_type" : "offline", "status" : "major",  "is_active": true },
    { "_id" : 6, "target_type": "controller", "target" : { "name": "controller_B" }, "warning_type" : "offline", "status" : "major",  "is_active": true },
    { "_id" : 7, "target_type": "controller", "target" : { "name": "controller_A" }, "warning_type" : "battery", "status" : "critical", "is_active": true },
])

Say I wanted to know every active warning controllers have, and to map these warnings to the relevant controller (so I could easily determine what active warnings controller_A has and separate these from the active warnings that controller_B has). I would like these grouped into an array.

Example of what I would hope the result to look like:

{ 
    "_id" : "controller_A", "warnings": [
        { "_id" : 1, "target_type": "controller", "target" : { "name": "controller_A" }, "warning_type" : "offline", "status" : "critical", "is_active": true },
        { "_id" : 7, "target_type": "controller", "target" : { "name": "controller_A" }, "warning_type" : "battery", "status" : "minor", "is_active": true },
    ], 
    "_id" : "controller_B", "warnings": [
        { "_id" : 4, "target_type": "controller", "target" : { "name": "controller_B" }, "warning_type" : "battery", "status" : "major",  "is_active": true },
        { "_id" : 6, "target_type": "controller", "target" : { "name": "controller_B" }, "warning_type" : "offline", "status" : "critical",  "is_active": true }
    ] 
}

For the sake of this example, say two-way referencing in the design is not possible (i.e. we cannot design the system so that targets have their corresponding warnings embedded into their documents).

How could I achieve this using MongoDB queries (my application will be using Mongoose for NodeJS, knowing how to achieve this in MongoDB itself is sufficient for now).

Answer 1

Extending Kerschbaumer's answer to include the extra matching I needed:

db.getCollection('warnings').aggregate([
      { $match : { is_active : true, target_type: "controller" } },
      {
        $group: {
          _id: '$target.name',
          warnings : {$push: '$$ROOT'}
        },
      },
        
      { $sort: { warnings: -1 } },
    ])

This returns:

/* 1 */
{
    "_id" : "controller_A",
    "warnings" : [ 
        {
            "_id" : 1.0,
            "target_type" : "controller",
            "target" : {
                "name" : "controller_A"
            },
            "warning_type" : "offline",
            "status" : "critical",
            "is_active" : true
        }, 
        {
            "_id" : 7.0,
            "target_type" : "controller",
            "target" : {
                "name" : "controller_A"
            },
            "warning_type" : "battery",
            "status" : "critical",
            "is_active" : true
        }
    ]
}

/* 2 */
{
    "_id" : "controller_B",
    "warnings" : [ 
        {
            "_id" : 4.0,
            "target_type" : "controller",
            "target" : {
                "name" : "controller_B"
            },
            "warning_type" : "battery",
            "status" : "major",
            "is_active" : true
        }, 
        {
            "_id" : 6.0,
            "target_type" : "controller",
            "target" : {
                "name" : "controller_B"
            },
            "warning_type" : "offline",
            "status" : "major",
            "is_active" : true
        }
    ]
}

Answer 2

You can use aggregation in this case. I wrote an example which looks like this:

db.getCollection('warnings').aggregate([
      {
        $group: {
          _id: '$target.name',
          warnings : {$push: '$$ROOT'}
        },
      },
      { $sort: { warnings: -1 } },

    ]);

If you execute this query you get the following result:

[
  {
    "_id": "controller_A",
    "warnings": [
      {
        "_id": 1,
        "target_type": "controller",
        "target": {
          "name": "controller_A"
        },
        "warning_type": "offline",
        "status": "critical",
        "is_active": true
      },
      {
        "_id": 2,
        "target_type": "controller",
        "target": {
          "name": "controller_A"
        },
        "warning_type": "battery",
        "status": "critical",
        "is_active": false
      },
      {
        "_id": 7,
        "target_type": "controller",
        "target": {
          "name": "controller_A"
        },
        "warning_type": "battery",
        "status": "critical",
        "is_active": true
      }
    ]
  },
  {
    "_id": "controller_B",
    "warnings": [
      {
        "_id": 3,
        "target_type": "controller",
        "target": {
          "name": "controller_B"
        },
        "warning_type": "offline",
        "status": "minor",
        "is_active": false
      },
      {
        "_id": 4,
        "target_type": "controller",
        "target": {
          "name": "controller_B"
        },
        "warning_type": "battery",
        "status": "major",
        "is_active": true
      },
      {
        "_id": 6,
        "target_type": "controller",
        "target": {
          "name": "controller_B"
        },
        "warning_type": "offline",
        "status": "major",
        "is_active": true
      }
    ]
  },
  {
    "_id": "light_A",
    "warnings": [
      {
        "_id": 5,
        "target_type": "light",
        "target": {
          "name": "light_A"
        },
        "warning_type": "offline",
        "status": "major",
        "is_active": true
      }
    ]
  }
]