As a SQL DBA, I have some common queries I use to get by certain information, organized by date. I’m starting to do a bit more work with Mongo, and it can be a bit frustrating trying to translate what I can do with SQL into Mongo.

Here is one I figured out recently, I thought it may be useful for others. I want to get a count of items for a certain period of time, grouped & ordered by date.

Take this example in MySQL:

• I’m formatting the CreationDate in the select, which will be used by the Grouping & Ordering. This parses out the additional date fields that would affect the group by (e.g. 7/1/15 10:51 and 7/1/15 11:51 can now be grouped)
• Count: This is the data to be grouped by date
• Where clause: Just get me the date since 7/1/15
• Group by: Use the OrderDate for grouping
• Order By: Use the OrderDate for ordering, use Ascending order (can use DESC for reverse order)

SELECT DATE_FORMAT(creationdate, '%m-%d-%y') AS 'OrderDate',
COUNT(DISTINCT O.ORDER_ID) AS OrderCount,
COUNT(OL.ORDERLINE_ID) AS 'OrderLines'
FROM ORDERLINE OL
JOIN ORDER O ON OL.ORDERID = O.ORDER_ID
WHERE O.creationdate > '2015-07-01'
AND O.DELETED = 0
AND OL.DELETED = 0
GROUP BY OrderDate
ORDER BY OrderDate ASC


Sample Data:

On Mongo, I can do something similar – In this instance I’m counting the documents, but there are additional functions to sum/avg/count etc date within documents.

• Match: In the aggregate, it’s like the Where clause: just get me the documents since 7/1/15
• Group: This is where I’m defining the grouping criteria.  There is probably a prettier way to do this, but I’m breaking out the Year, Month & Day for each timestamp, similar to where it’s seen in the SQL Select portion.
• Count: the data I’m grouping by date
• Sort: Order by the _id group, 1 is ascending order, -1 is descending order.

db.Orders.aggregate(
[
{ $match :
{ timestamp :{ $gt: ISODate("2015-07-01T00:00:00.000Z")}}
},
{ $group : {
_id : { year: { $year: "$timestamp" } , month: { $month: "$timestamp" }, day: { $dayOfMonth: "$timestamp" }},
count: { $sum: 1 }
}
},
{ $sort: { _id: 1 } }
]
)

Sample data:

{ "_id" : { "year" : 2015, "month" : 7, "day" : 1 }, "count" : 956 }
{ "_id" : { "year" : 2015, "month" : 7, "day" : 2 }, "count" : 107 }
{ "_id" : { "year" : 2015, "month" : 7, "day" : 3 }, "count" : 73 }
{ "_id" : { "year" : 2015, "month" : 7, "day" : 4 }, "count" : 73 }
{ "_id" : { "year" : 2015, "month" : 7, "day" : 5 }, "count" : 73 }
{ "_id" : { "year" : 2015, "month" : 7, "day" : 6 }, "count" : 6385 }
{ "_id" : { "year" : 2015, "month" : 7, "day" : 7 }, "count" : 106 }
{ "_id" : { "year" : 2015, "month" : 7, "day" : 8 }, "count" : 3 }
{ "_id" : { "year" : 2015, "month" : 7, "day" : 9 }, "count" : 2 }