MongoDB - Slicing & Dicing - Get Daily Activity - Series #15
Tags: database, mongodb, javascript, node
Published: 2021-02-19 04:59:50


## Intro
We can easily perform data analysis on our data by slicing our data into buckets based on dates.

This query retrieves a report on how many "likes" per day were performed by users over the last 30 days.
```js
  mgArr(dbEnum.nlpdb, collEnum.users_actions,
    matchExact("actionCateg", "fav"),
    matchExact("isFav", true),
    {
      $group: {
        _id: {
          year: { $year: "$_id" },
          month: { $month: "$_id" },
          day: { $dayOfMonth: "$_id" }
        },
        count: { $sum: 1 },
   }
    },
   lastInserted(30),
   )
```

The Output:
```js
/*
[
  { _id: { year: 2021, month: 2, day: 18 }, count: 123 },
  { _id: { year: 2021, month: 2, day: 17 }, count: 2214 },
  { _id: { year: 2021, month: 2, day: 15 }, count: 369 },
  { _id: { year: 2021, month: 2, day: 14 }, count: 1353 },
  { _id: { year: 2021, month: 2, day: 12 }, count: 492 },
  { _id: { year: 2021, month: 2, day: 11 }, count: 1230 },
  { _id: { year: 2021, month: 2, day: 10 }, count: 1476 },
  { _id: { year: 2021, month: 2, day: 9 }, count: 4305 },
  { _id: { year: 2021, month: 2, day: 8 }, count: 1722 },
  { _id: { year: 2021, month: 2, day: 6 }, count: 984 },
  { _id: { year: 2021, month: 2, day: 5 }, count: 2706 },
  { _id: { year: 2021, month: 2, day: 4 }, count: 861 },
  { _id: { year: 2021, month: 2, day: 3 }, count: 369 },
  { _id: { year: 2021, month: 2, day: 2 }, count: 2829 },
  { _id: { year: 2021, month: 2, day: 1 }, count: 1230 },
  { _id: { year: 2021, month: 1, day: 29 }, count: 615 },
  { _id: { year: 2021, month: 1, day: 28 }, count: 3444 },
  { _id: { year: 2021, month: 1, day: 27 }, count: 369 },
  { _id: { year: 2021, month: 1, day: 26 }, count: 2706 },
  { _id: { year: 2021, month: 1, day: 25 }, count: 123 },
  { _id: { year: 2021, month: 1, day: 24 }, count: 1599 },
  { _id: { year: 2021, month: 1, day: 23 }, count: 246 },
  { _id: { year: 2021, month: 1, day: 22 }, count: 3567 },
  { _id: { year: 2021, month: 1, day: 21 }, count: 1353 },
  { _id: { year: 2021, month: 1, day: 20 }, count: 1722 },
  { _id: { year: 2021, month: 1, day: 19 }, count: 3813 },
  { _id: { year: 2021, month: 1, day: 18 }, count: 2091 },
  { _id: { year: 2021, month: 1, day: 17 }, count: 3321 },
  { _id: { year: 2021, month: 1, day: 16 }, count: 123 },
  { _id: { year: 2021, month: 1, day: 14 }, count: 246 }
]
*/
```

## Notes
The query is using the MongoDB Aggregation Framework.

The query pipeline is just an array of query stages.

The stages are query syntax that is in a JavaScript object format.

So each stage is a JS object that describes that portion of the query that's being composed.

Thus we compose a query together by adding stages.

As an analogy to SQL syntax, you can think of stages as SQL clauses like the SELECT clause, the WHERE clause, or the GROUP BY clause.

The matchExact stage is my wrapper func, which returns the raw stage object syntax.
The raw syntax would look like....
```js
{ $match: { actionCateg: "fav", isFav: true  } }
```

The $group stage has an _id key. This DOES NOT represent the Primary Key (PK) of the Collection. It represents the uniquely grouped item in the grouping. In this case it's the combination of "year, month and day" that represents the unique record being summed.

The "$_id" value actually represents the PK of the Collection. As you remember from the last article, the PK contains the timestamp information. The $year operator (think of these Mongodb operators as built-in utility functions) extracts the year part of the timestamp which is stored in the "$_id" (PK).

In the last stage (lastInserted, my utility func) we retrieve the last 30 days.

## What's Next

More magic with MongoDB coming up in future series articles.

As always, if you have an questions or input, let me know.