MongoDB Count and groupby subdocument

Question

I have the following document:

{
    "id":1,
    "url":"mysite.com",
    "views": 
     [
       {"ip":"1.1.1.1","date":"01-01-2015"},
       {"ip":"2.2.2.2","date":"01-01-2015"},
       {"ip":"1.1.1.1","date":"01-01-2015"},
       {"ip":"1.1.1.1","date":"01-01-2015"}
     ]
}

If i want to count how many unique ips (groupBy), how can I do that with mongo?

Answer 1

Use the aggregation framework to get the desired result. The aggregation pipeline will have a $unwind operation as the first step which deconstructs the views array field from the input documents to output a document for each element. Each output document replaces the array with an element value. The next pipeline stage $group then groups the documents by the "views.ip" field, calculates the count field for each group, and outputs a document for each unique state. The new per-ip documents have two fields: the _id field and the count field. The _id field contains the value of the unique IP address; i.e. the group by field. The count field is a calculated field that contains the total ip count per each unique IP. To calculate the value, $group uses the $sum operator to calculate the total number of IP addresses. So your final aggregation pipeline would look like this:

db.collection.aggregate([
    {
        "$unwind": "$views"
    },
    {
        "$group": {
            "_id": "$views.ip",
            "count": {
                "$sum": 1
            }
        }
    }
])

Output:

/* 1 */
{
    "result" : [ 
        {
            "_id" : "2.2.2.2",
            "count" : 1
        }, 
        {
            "_id" : "1.1.1.1",
            "count" : 3
        }
    ],
    "ok" : 1
}

---

-- UPDATE --

To get the total of all unique IP's, you need another $group pipeline stage, this time the _id is null, that is you group all the documents from the previous pipeline stream into one, then use the same $sum operation on that group to get the total of the count. The aggregation pipeline would look like this in the end:

db.collection.aggregate([
    {
        "$unwind": "$views"
    },
    {
        "$group": {
            "_id": "$views.ip",
            "count": {
                "$sum": 1
            }
        }
    },
    {
        "$group": {
            "_id": null,
            "total": {
                "$sum": "$count"
            }
        }
    }
])

Output:

/* 1 */
{
    "result" : [ 
        {
            "_id" : null,
            "total" : 4
        }
    ],
    "ok" : 1
}