I have a document like,
{
a : "123",
b : [
{
"start" : "2015-01-03",
"end" : "2015-01-05",
"name" : "xyz"
},
{
"start" : "2015-01-15",
"end" : "2015-01-17",
"name" : "abc"
}
]
},
{
a : "456",
b : [
{
"start" : "2015-01-04",
"end" : "2015-01-05",
"name" : "xyzd"
}
]
}
I'm trying to get the count of b for each day like,
2015-01-03 count: 1
2015-01-04 count: 2
2015-01-05 count: 2
2015-01-15 count: 1
2015-01-16 count: 1
2015-01-17 count: 1
This can be done by a simple aggregation if the dates were expanded. Is is it possible to expand the date range for an aggregation?
Edit: basically for a given range I want to expand that range and get the count for each day, which will be one unless it overlaps with another range(s).
Let's check following scenario, If your documents dates like ISODate
format as given below
[
{
"_id": ObjectId("552e71ec3420d7797e5ae682"),
"a": "123",
"b": [
{
"start": ISODate("2015-01-03T00:00:00Z"),
"end": ISODate("2015-01-05T00:00:00Z"),
"name": "xyz"
},
{
"start": ISODate("2015-01-15T00:00:00Z"),
"end": ISODate("2015-01-17T00:00:00Z"),
"name": "abc"
}
]
},
{
"_id": ObjectId("552e72063420d7797e5ae683"),
"a": "456",
"b": [
{
"start": ISODate("2015-01-04T00:00:00Z"),
"end": ISODate("2015-01-05T00:00:00Z"),
"name": "xyzd"
}
]
}
]
Now If you want count all start
date then simple use following query
db.collectionName.aggregate([
{
"$unwind": "$b"
},
{
"$group": {
"_id": "$b.start",
"count": {
"$sum": 1
}
}
},
{
"$project": {
"startDate": "$_id",
"count": "$count",
"_id": 0
}
}
])
same above used for end date simply replace in group $b.start to $b.end
then it shows end dates count
Now another problem if you want to find out start
date count from given range then use below query, suppose start
date from ISODate("2015-01-03T00:00:00Z")
and ISODate("2015-01-04T00:00:00Z")
range
db.collectionName.aggregate([
{
"$unwind": "$b"
},
{
"$match": {
// check here date range matching documents
"$and": [
{
"b.start": {
"$gte": ISODate("2015-01-03T00:00:00Z")
}
},
{
"b.start": {
"$lte": ISODate("2015-01-04T00:00:00Z")
}
}
]
}
},
{
"$group": {
"_id": "$b.start",
"count": {
"$sum": 1
}
}
},
{
"$project": {
"startDate": "$_id",
"count": "$count",
"_id": 0
}
}
])
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With