Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Generate date ranges in mongodb

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).

like image 544
Rnet Avatar asked Nov 09 '22 16:11

Rnet


1 Answers

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
    }
}
])
like image 92
Yogesh Avatar answered Nov 15 '22 06:11

Yogesh