MongoDB : group and count users by gender, civilStatus and professionalCategory-CodePudding

I have a collection of users, each user has a profile. I want to implement a query to make statistics on users.

This is my collection.

[
    {
        "_id": ObjectId("61d2db0d273a9076d630697b"),
        "state": "VALIDATED",
        "phone": "xxx",
        "civilStatus": "SINGLE",
        "gender": "MALE",
        "professionalCategory": "STUDENT"
    }
]

I want the result to contain an array of all genders of users in the database, and the number of users with each gender. same for civilStatus and professionalCategories

This is the result i am looking for :

{
    "total": 2000
    "validated": 1800,
    "genders": [
        {
            "value": "MALE",
            "count": 1200
        },
        {
            "value": "FEMALE",
            "count": 600
        }
    ],
    "civilStatus": [
        {
            "value": "SINGLE",
            "count": "300"
        }
        ...
    ],
    "professionalCategories": [
        {
            "value": "STUDENT",
            "count": "250"
        }
        ...
    ]
}

I implemented the query, but I still have a few things that I don't know how to do.

db.getCollection("users").aggregate([
    {
        $group: { 
            _id: null,
            validated: {
                $sum: {
                    $cond: { 
                        if:  { $eq: ["$state", "VALIDATED"] },
                        then: 1,
                        else: 0
                    }
                }
            },
            genders: { 
                $push: "$gender"
            },
            civilStatus: { 
                $push: "$civilStatus"
            },
            professionalCategories: { 
                $push: "$professionalCategory"
            }
        }
    }
])

This is the result of this query :

{
    "total": 2000
    "validated": 1800,
    "genders": [
        "MALE",
        "MALE",
        "FEMALE",
        "MALE",
        "FEMALE",
        "FEMALE"
        ...
    ],
    "civilStatus": [
        "SINGLE",
        "MARIED",
        "SINGLE",
        ...
    ],
    "professionalCategories": [
        "STUDENT",
        "WORKER",
        "RETIRED"
        ...
    ]
}

I miss how to group each gender, civil Status and professional Category and calculate the number of users for each one.

I also tried this query, but I don't know how to complete the "count" field for each item of the array :

db.getCollection("users").aggregate([
    {
        $group: { 
            _id: null,
            validated: {
                $sum: {
                    $cond: { 
                        if:  { $eq: ["$state", "VALIDATED"] },
                        then: 1,
                        else: 0
                    }
                }
            },
            genders: { 
                $addToSet: {
                    value: "$gender",
                    count: {
                        //
                    }
                }
            },
            civilStatus: { 
                $addToSet: {
                    value: "$civilStatus",
                    count: {
                        //
                    }
                }
            },
            professionalCategories: { 
                $addToSet: {
                    value: "$professionalCategory",
                    count: {
                        //
                    }
                }
            },
        }
    }
])

if the query was to treat only one field, for example gender. it would have been easier with "unwind". but here I have 3 fields.

can someone help me please?

CodePudding user response：

You can use following aggregation

Here is the code

db.collection.aggregate([
  {
    "$facet": {
      "genders": [
        {
          "$group": {
            "_id": "$gender",
            "total": { $sum: 1 }
          }
        }
      ],
      "civilStatus": [
        {
          "$group": {
            "_id": "$civilStatus",
            "total": { $sum: 1 }
          }
        }
      ],
      "professionalCategory": [
        {
          "$group": {
            "_id": "$professionalCategory",
            "total": {  $sum: 1 }
          }
        }
      ],
      "validated": [
        {
          "$group": {
            "_id": "$state",
            "total": {  "$sum": 1 }
          }
        }
      ]
    }
  },
  {
    $set: {
      validated: {
        "$filter": {
          "input": "$validated",
          "cond": {
            "$eq": [ "$$this._id", "VALIDATED" ]
          }
        }
      }
    }
  },
  {
    $set: {
      validated: {
        "$ifNull": [
          {
            "$arrayElemAt": [ "$validated", 0 ]
          },
          0
        ]
      }
    }
  },
  {
    $set: { validated: "$validated.total" }
  }
])

Working Mongo playground