CODEWITHSUNDEEP
javamongodbaggregation-framework
Ninad
Ninad

Reputation: 484

MongoDB group by duration span

I have aggregation query for representing line chart data.

It is as follows :

[
{
    "$match": {
        "Category": {
            "$in": ["Mobile"]
        },
        "StartTime": {
            "$gte": {"$date": "2014-01-29T00:00:00.000Z"},
            "$lt": {"$date": "2014-09-29T00:00:00.000Z"}
        }
    }
},
{
    "$group": {
        "_id": {
            "Category": "$Category", 
            "Country":"$Country",
            "City":"$City",
            "day": {
                "day":{"$dayOfMonth": "$StartTime"},
                "month":{"$month":"$month"},
                "year":{"$year":"$year"}
            }
        },
        "UniqueVisits": {
            "$sum": 1
        },
        "Date": {
            "$first": "$StartTime"
        }
    }
},
{
    "$project": {
        "_id": "$_id.Category",
        "Header": {
            "$concat": [{"$substr": [{"$month": "$Date"}, 0,2]},
                "/",
                {"$substr": [{"$dayOfMonth": "$Date"}, 0, 2]},
                "/",
                {"$substr": [{"$year": "$Date"}, 0, 4]}]
        },
        "Name": {
            "$concat": [
                {
                    "$ifNull": ["$_id.Country","notset"]
                },
                "~",
                {
                    "$ifNull": ["$_id.City","notset"]
                }
            ]
        },
        "UniqueVisits": "$UniqueVisits",            
    }
}
]

It works fine. Similarly i have line chart by hour,week and month by grouping using $hour,$week,$month.

Now i want to add line chart by AM/PM i.e hour duration from 0-12 and 12-24 and even by Morning(6-12), Afternoon(12-18), Evening(18-24), Night(0-6)

Any help on how can i achieve grouping for above duration's. Thanks in advance.

For example :

Data :

{ "_id" : 1, "Date" : ISODate("2014-10-02T19:44:09Z") }
{ "_id" : 2, "Date" : ISODate("2014-10-02T20:44:09Z") }
{ "_id" : 3, "Date" : ISODate("2014-09-03T20:44:09Z") }
{ "_id" : 4, "Date" : ISODate("2014-09-02T20:44:09Z") }
{ "_id" : 5, "Date" : ISODate("2014-09-03T00:00:00Z") }
{ "_id" : 6, "Date" : ISODate("2014-09-03T07:00:00Z") }
{ "_id" : 7, "Date" : ISODate("2014-09-03T14:00:00Z") }
{ "_id" : 8, "Date" : ISODate("2014-10-02T20:47:09Z") }

What i want to achieve for AM/PM for grouping each day is as following :

{ "_id" : "PM", "Count" : 3, "Date" : ISODate("2014-10-02T19:44:09Z") }
{ "_id" : "AM", "Count" : 1, "Date" : ISODate("2014-09-03T00:00:00Z") }
{ "_id" : "AM", "Count" : 4, "Date" : ISODate("2014-09-03T00:00:00Z") }

And for Morning, Afternoon, Evening, Night is as follows :

{ "_id" : "Evening", "Count" : 3, "Date" : ISODate("2014-10-02T19:44:09Z") }
{ "_id" : "Evening", "Count" : 1, "Date" : ISODate("2014-09-02T20:44:09Z") }
{ "_id" : "Night", "Count" : 1, "Date" : ISODate("2014-09-03T00:00:00Z") }
{ "_id" : "Morning", "Count" : 1, "Date" : ISODate("2014-09-03T07:00:00Z") }
{ "_id" : "Afternoon", "Count" : 1, "Date" : ISODate("2014-09-03T14:00:00Z") }
{ "_id" : "Evening", "Count" : 1, "Date" : ISODate("2014-09-03T20:44:09Z") }

Upvotes: 0

Views: 320

Answers (1)

Wizard
Wizard

Reputation: 4421

Inserting these parts into the proper position of your $group._id may accomplish your target. 

ampm : {
    $cond : {
        "if" : {
            $lt : [ {
                $hour : "$StartTime"
            }, 12 ]
        },
        "then" : "AM", 
        "else" : "PM"
    }
},
segment : {
    $let : {
        "vars" : {
            h : {
                $hour : "$StartTime"
            }
        },
        "in" : {
            $cond : {
                "if" : {
                    $lt: [ "$$h", 6 ]
                },
                "then" : "Night",
                "else" : {
                    $cond : {
                        "if" : {
                            $lt : [ "$$h", 12 ]
                        },
                        "then" : "Morning",
                        "else" : {
                            $cond : {
                                "if" : {
                                    $lt : [ "$$h", 18 ]
                                },
                                "then" : "Afternoon",
                                "else" : "Evening"
                            }
                        }
                    }
                }
            }
        }
    }
}

To make it easier to understand, I paste the whole one here 

[
{
    "$match": {
        "Category": {
            "$in": ["Mobile"]
        },
        "StartTime": {
            "$gte": {"$date": "2014-01-29T00:00:00.000Z"},
            "$lt": {"$date": "2014-09-29T00:00:00.000Z"}
        }
    }
},
{
    "$group": {
        "_id": {
            "Category": "$Category", 
            "Country":"$Country",
            "City":"$City",
            "day": {
                "day":{"$dayOfMonth": "$StartTime"},
                "month":{"$month":"$month"},
                "year":{"$year":"$year"},

                // add this part to group by for each day of each month of each year
                ampm : {
                    $cond : {
                        "if" : {
                            $lt : [ {
                                $hour : "$StartTime"
                            }, 12 ]
                        },
                        "then" : "AM", 
                        "else" : "PM"
                    }
                },
                segment : {
                    $let : {
                        "vars" : {
                            h : {
                                $hour : "$StartTime"
                            }
                        },
                        "in" : {
                            $cond : {
                                "if" : {
                                    $lt: [ "$$h", 6 ]
                                },
                                "then" : "Night",
                                "else" : {
                                    $cond : {
                                        "if" : {
                                            $lt : [ "$$h", 12 ]
                                        },
                                        "then" : "Morning",
                                        "else" : {
                                            $cond : {
                                                "if" : {
                                                    $lt : [ "$$h", 18 ]
                                                },
                                                "then" : "Afternoon",
                                                "else" : "Evening"
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }


            }
        },
        "UniqueVisits": {
            "$sum": 1
        },
        "Date": {
            "$first": "$StartTime"
        }
    }
},
{
    "$project": {
        "_id": "$_id.Category",
        "Header": {
            "$concat": [{"$substr": [{"$month": "$Date"}, 0,2]},
                "/",
                {"$substr": [{"$dayOfMonth": "$Date"}, 0, 2]},
                "/",
                {"$substr": [{"$year": "$Date"}, 0, 4]}]
        },
        "Name": {
            "$concat": [
                {
                    "$ifNull": ["$_id.Country","notset"]
                },
                "~",
                {
                    "$ifNull": ["$_id.City","notset"]
                }
            ]
        },
        "UniqueVisits": "$UniqueVisits",            
    }
}
]

Upvotes: 1

Related Questions