Active and Total User Count Mongodb

Question

I want to write a group by query to written active user and total count(both active and inactive) grouped by a date column in mongodb. I am able to run them as two separate scripts but how to retrieve the same information in one script

db.user.aggregate(
 {
    "$match": { 'phoneInfo.verifiedFlag': true}
},
{
    "$project": {
           yearMonthDayUTC: { $dateToString: { format: "%Y-%m-%d", date: "$createdOn" } }

    }
},
{
    "$group": {
        "_id": {day: "$yearMonthDayUTC"},
        count: {
            "$sum": 1
        }
    }
},
{
    $sort: {
        "_id.day": 1,
    }
})

score 0 · Answer 1 · answered Feb 19 '18 at 13:48

You can use the $cond operator in your group to create a conditional count as follows (assuming the inactive/active values are in a field called status):

db.user.aggregate([
    { "$match": { 'phoneInfo.verifiedFlag': true} },
    {
        "$group": {
            "_id": { "$dateToString": { "format": "%Y-%m-%d", "date": "$createdOn" } },
            "total": { "$sum": 1 },
            "active_count": {
                "$sum": {
                    "$cond": [ { "$eq": [ "$status", "active" ] }, 1, 0 ]
                }
            },
            "inactive_count": {
                "$sum": {
                    "$cond": [ { "$eq": [ "$status", "inactive" ] }, 1, 0 ]
                }
            }
        }
    },
    { "$sort": { "_id": 1 } }
])

For different values you can adapt the following pipeline:

db.user.aggregate([
    { "$match": { 'phoneInfo.verifiedFlag': true} },
    { 
        "$group": {
            "_id": { 
                "day": { 
                    "$dateToString": { 
                        "format": "%Y-%m-%d", 
                        "date": "$createdOn" 
                    } 
                },
                "status": { "$toLower": "$status" }
            },
            "count": { "$sum": 1 }
        }
    },
    { 
        "$group": {
            "_id": "$_id.day",
            "counts": {
                "$push": {
                    "status": "$_id.status",
                    "count": "$count"
                }
            }
        }
    },
    { "$sort": { "_id": 1 } }
])

Hi @chridam, kindly check this question which I have added https://stackoverflow.com/questions/63155077/get-total-users-list-along-with-the-active-users-in-mongodb-matched-on-or-condit — Navi, Jul 29 '20 at 13:46

Active and Total User Count Mongodb

1 Answers1