MongoDB Count and group subdocument
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 it with mongo?
source to share
Use an aggregation framework to get the desired result. The aggregation pipeline will have as the first step, which deconstructs the array field from the input documents to output the document for each element. Each output replaces the array with the element's value. The next stage of the pipeline then groups the documents into a field , calculates the field for each group, and outputs the document for each unique state. New per-ip documents have two fields: field and field . The field contains the value of the unique IP address; those. groups across the field. Field is a calculated field that contains the total ip count for each unique IP. To compute the value uses $unwind
views
$group
"views.ip"
count
_id
count
_id
count
$group
$sum
to calculate the total number of IP addresses. So your final aggregation pipeline will 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 IPs you will need another step, this time _id is null, meaning you are grouping all documents from the previous pipeline stream into one, then use the same in that group to get the total counts ... In the end, the aggregation pipeline will look like this: $group
$sum
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
}
source to share