Count no.of instances of string in a field across

Question

I've got a specific use case and I'm trying to find a way to do it in one aggregation pipeline and preferably without the need to hardcode any data values. I want to group documents based on one property and see a count of values for a particular field within the document.

Example data:

{
    "flightNum": "DL1002",
    "status": "On time",
    "date": 20191001
},
{
    "flightNum": "DL1002",
    "status": "Delayed",
    "date": 20191002
},
{
    "flightNum": "DL1002",
    "status": "On time",
    "date": 20191003
},
{
    "flightNum": "DL1002",
    "status": "Cancelled",
    "date": 20191004
},
{
    "flightNum": "DL952",
    "status": "On time",
    "date": 20191003
},
{
    "flightNum": "DL952",
    "status": "On time",
    "date": 20191004
}

I want an aggregation pipeline that can tell me, for each flight (group by flightNum) how many flights were "On time", "Delayed", or "Cancelled".

Desired response:

{
    "flightNum": "DL1002",
    "numOnTime": 2,
    "numCancelled": 1,
    "numDelayed": 1
},
{
    "flightNum": "DL952",
    "numOnTime": 2
}

It doesn't really matter the naming of the fields, so much as they are there in one document. I found that I could do this with the $cond operator, but that would require me to hard code the expected values of "status" field. For this arbitrary example, there aren't many values, but if another status is added, I would like to not have to update the query. Since there are so many nifty tricks in Mongo, I feel there is likely a way to achieve this.

Answer 1

You can try below query :

db.collection.aggregate([
    /** Group all docs based on flightNum & status & count no.of occurances */
    {
        $group: {
            _id: {
                flightNum: "$flightNum",
                status: "$status"
            },
            count: {
                $sum: 1
            }
        }
    },
    /** Group on flightNum & push an objects with key as status & value as count */
    {
        $group: {
            _id: "$_id.flightNum",
            status: {
                $push: {
                    k: "$_id.status",
                    v: "$count"
                }
            }
        }
    },
    /** Recreate status field as an object of key:value pairs from an array of objects */
    {
        $addFields: {
            status: {
                $arrayToObject: "$status"
            }
        }
    },
    /** add flightNum inside status object */
    {
        $addFields: {
            "status.flightNum": "$_id"
        }
    },
    /** Replace status field as new root for each doc in coll */
    {
        $replaceRoot: {
            newRoot: "$status"
        }
    }
])

Test : MongoDB-Playground