How to count from two fields in mongoDB

Question

{
    "_id" : ObjectId("56bd8e9de517259412a743ab"),
    "user_token" : "mzXhdbCu",
    "sender_details" : {
        "location" : "XYZ",
        "zipcode" : "610208"
    },
    "shipping_address" : {
        "location" : "ABC",
        "zipcode" : "602578
    }
}

I have been trying to count the number of instances of each unique zipcode from both

$sender_details.zipcode

and

$shipping_address.zipcode

I tried to use the following code

db.ac_consignments.aggregate({
    $group: {
        _id: {
            "zipcode":"$sender_details.zipcode", 
            "szipcode":"$shipping_address.zipcode"
        }, 
        count: {"$sum":1}
    }
})

The output I receive is this

{
    "result" : [ 
        {
            "_id" : {
                "zipcode" : "610208",
                "szipcode" : "602578"
            },
            "count" : 7
        }, 
        {
            "_id" : {
                "zipcode" : "602578",
                "szipcode" : "678705"
            },
            "count" : 51
        }
    ],
    "ok" : 1
}

But what I require is the count of each zipcode present in $sender_details.zipcode and $shipping_address.zipcode totally. So an output like this

{
    "result" : [ 
        {
            "_id" : {
                "zipcode" : "610208",
            },
            "count" : 7
        }, 
        {
            "_id" : {
                "zipcode" : "602578"
            },
            "count" : 51
        }
        {
            "_id" : {
                "zipcode" : "678705"
            },
            "count" : 51
        }
    ],
    "ok" : 1
}

Answer 1

The following pipeline should work for you

db.getCollection('ac_consignments').aggregate([
    {       
        $project: {
            zipcode: [ "$sender_details.zipcode", "$shipping_address.zipcode" ]
        }
    },
    {
        $unwind: "$zipcode"
    },
    {
        $group: {
            _id: "$zipcode",
            count: { $sum: 1 }
        }
    }
])

which produces output like this

/* 1 */
{
    "_id" : "610208",
    "count" : 1.0
}

/* 2 */
{
    "_id" : "610209",
    "count" : 2.0
}

/* 3 */
{
    "_id" : "602578",
    "count" : 1.0
}

/* 4 */
{
    "_id" : "602579",
    "count" : 2.0
}

when using the following as sample data

/* 1 */
{
    "_id" : ObjectId("56bd8e9de517259412a743ab"),
    "user_token" : "mzXhdbCu",
    "sender_details" : {
        "location" : "XYZ",
        "zipcode" : "610208"
    },
    "shipping_address" : {
        "location" : "ABC",
        "zipcode" : "602578"
    }
}

/* 2 */
{
    "_id" : ObjectId("56bd8e9de517259412a743ac"),
    "user_token" : "mzXhdbCu",
    "sender_details" : {
        "location" : "XYZ",
        "zipcode" : "610209"
    },
    "shipping_address" : {
        "location" : "ABC",
        "zipcode" : "602579"
    }
}

/* 3 */
{
    "_id" : ObjectId("56bd8e9de517259412a753ac"),
    "user_token" : "mzXhdbCu",
    "sender_details" : {
        "location" : "XYZ",
        "zipcode" : "610209"
    },
    "shipping_address" : {
        "location" : "ABC",
        "zipcode" : "602579"
    }
}  

---

See the following GIF

---

Update for older versions

db.getCollection('ac_consignments').aggregate([
    {
        $project: {
            sender_zip: "$sender_details.zipcode",
            shipping_zip: "$shipping_address.zipcode",
            party: { $literal: ["sender_zip", "shipping_zip"] }
        }
    },
    {
        $unwind: "$party"
    },
    {
        $group: {
            _id: "$_id",
            zipcode: {
                $push: {
                    $cond: [
                        { $eq: ["$party", "sender_zip"] },
                        "$sender_zip",
                        "$shipping_zip"
                    ]
                }
            }
        }
    },
    {
        $unwind: "$zipcode"
    },
    {
        $group: {
            _id: "$zipcode",
            count: { $sum: 1 }
        }
    }
])