My MongoDB database has the 'interviews' collection whose document structure is similar to this:

{
    "_id" : ObjectId("632b97b0f2bd3f64bbc30ec8"),
    "agency" : "AG1",
    "year" : "2022",
    "month" : "9",
    "residents" : [
        {
            "sequential" : 1,
            "name" : "Resident 1",
            "statusResident" : "pending",
        },
        {
            "sequential" : 2,
            "name" : "Resident 2",
            "statusResident" : "not analyzed"
        },
        {
            "sequential" : 3,
            "name" : "Resident 3",
            "statusResident" : "not analyzed"
        },
        {
            "sequential" : 4,
            "name" : "Resident 4",
            "statusResident" : "finished"
        }
    ]
}

{
    "_id" : ObjectId("882b99b0f2bd3f64xxc30ec8"),
    "agency" : "AG2",
    "year" : "2022",
    "month" : "9",
    "residents" : [
        {
            "sequential" : 1,
            "name" : "Resident 10",
            "statusResident" : "pending",
        },
        {
            "sequential" : 2,
            "name" : "Resident 20",
            "statusResident" : "not analyzed"
        }
    ]
}

I would like to make a query that returns something similar to SQL SELECT agency, statusResident, COUNT(*) FROM interviews GROUP BY agency, statusResident.

For these documents above, that would return something like

AG1     pending        1
AG1     not analyzed   2
AG1     finished       1
AG2     pending        1
AG2     not analyzed   1

I ran the following queries but they didn't return what I need:

db.interviews.aggregate([
                 { $group: { _id: { agency: "$agency", statusResident: "$residents.statusResident", total: { $sum: "$residents.statusResident" } } } },
                 { $sort: { agency: 1 } } 
               


db.interviews.group({
    key:{agency:1, "residents.statusResident":1},
    cond:{year:2022},
    reduce:function(current, result)
       {
         result.total  = 1;
       },
         initial:{total:0}
       })

I've consulted post "MongoDB SELECT COUNT GROUP BY" and "Select count group by mongodb" as well as the MongoDB documentation but to no avail. What query should I run to get a result similar to the one I want?

CodePudding user response：

Try this one

db.collection.aggregate([
  { $unwind: "$residents" },
  {
    $group: {
      _id: {
        agency: "$agency",
        statusResident: "$residents.statusResident",
        total: { $sum: 1 }
      }
    }
  },
  { $sort: { agency: 1 } }
])

Mongo Playground

CodePudding user response：

You can try this query:

• First $unwind to deconstruct the array and can group by statusResident too.
• Then $group by two values, agency and statusResident.
• And the last stage is $project to get an easier to read output.

db.collection.aggregate([
  {
    "$unwind": "$residents"
  },
  {
    "$group": {
      "_id": {
        "agency": "$agency",
        "statusResident": "$residents.statusResident"
      },
      "total": {
        "$sum": 1
      }
    }
  },
  {
    "$project": {
      "_id": 0,
      "agency": "$_id.agency",
      "statusResident": "$_id.statusResident",
      "total": 1
    }
  }
])

Example here