how to count/sum array on mongodb?

Question

I have a collection where every documents has an array named "contacts".

Sample Docs :

{
    "_id" : ObjectId("5660c2dfdfdfcba2d47baa2d9"),
    "name" : john,
    "contacts" : [ 
        {
            "name" : "ari",
            "phone" : "12341234"
        }, 
         {
            "name" : "dea",
            "phone" : "34234234"
        }
    ]
},

{
    "_id" : ObjectId("5660c2dfdfdfcba2d47baa2d9"),
    "name" : joni,
    "contacts" : [ 
        {
            "name" : "budi",
            "phone" : "13341234"
        }, 
         {
            "name" : "ade",
            "phone" : "3242343"
        },
{
            "name" : "are",
            "phone" : "64545345"
        }

    ]
}

I want to know get total count of contacts for all docs. From sample docs output should be 5 contacts

Thank you for helping me.

whoami - fakeFaceTrueSoul · Accepted Answer · 2020-03-07 09:04:59Z

You can try below query :

1) If contacts array exists in every doc :

db.collection.aggregate([
    /** project only needed field contacts with size of array in each doc */
    {
        $project: {
            _id: 0,
            contacts: {
                $size: "$contacts"
            }
        }
    },
    /** group on empty(without any filter) & sum contacts field */
    {
        $group: {
            _id: "",
            TotalContacts: {
                $sum: "$contacts"
            }
        }
    },
    /** Optional projection */
    {
        $project: {
            _id: 0
        }
    }
])

2) If contacts array field might not exist in every doc, if it exists & not an array then an additional type check has to be done in below $cond :

db.collection.aggregate([
    /** group on empty(without any filter) & sum contacts field's size if contacts exists else sum 0, You've can have optional projection as first stage like above query */
    {
        $group: {
            _id: "",
            TotalContacts: {
                $sum: {
                    $cond: [
                        {
                            "$ifNull": [
                                "$contacts",
                                false
                            ]
                        },
                        {
                            $size: "$contacts"
                        },
                        0
                    ]
                }
            }
        }
    },
    /** Optional projection */
    {
        $project: {
            _id: 0
        }
    }
])

Test : MongoDB-Playground

Collectives™ on Stack Overflow

how to count/sum array on mongodb?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related