Question 116166 in Mongodb Question posted in
The official documentation can be found here.

MongoDB query similar to SELECT COUNT GROUP BY

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?

Tags:

2

Answers


  1. 0

    Try this one

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

    Mongo Playground

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search