Retrieving item count per date on MongoDB aggregate

Question

I have a collection containing events. Each event is timestamped down to the millisecond. I would like to group the events in a count per day. e.g.:

I have

KEY       VALUE

_id       111222333444555ddd666fff
time      2023-04-23T15:35:19.631Z
type      pizza-event

_id       111222333444555ddd666fff
time      2023-04-23T01:41:20.631Z
type      TV-event

_id       111222333444555ddd666fff
time      2023-04-22T05:00:05.631Z
type      some-event

I would like

KEY        VALUE

date       04-22-2023
count      1.0

date       04-23-2023
count      2.0

The ultimate goal is to use the query in a Golang project.

So far I have

[
        {
            "$match" : {
                "$and" : [
                    {
                        "type" : "foo.bar.event"
                    },
                    {
                        "time" : {
                            "$gte" : ISODate("2023-04-23T00:00:00.000+0000")
                        }
                    },
                    {
                        "time" : {
                            "$lte" : ISODate("2023-04-25T00:00:00.000+0000")
                        }
                    }
                ]
            }
        }, 
        {
            "$group" : {
                "_id" : {
                    "$dateToString" : {
                        "format" : "%m-%d-%Y",
                        "date" : "$time"
                    }
                },
                "count" : {
                    "$sum" : 1.0
                }
            }
        }
    ]

which returns

KEY            VALUE

_id            04-24-2023
count          476.0

_id            04-23-2023
count          28.0

That could've worked but when I code this query in the go project a red squiggly appears under "$dateToString" with the message "Invalid field name" and ideally I would like the date to have a key of "time" instead of "_id". When I do following change in the group stage:

{
    _id: null,
    "date": {"$dateToString": { "format": "%m-%d-%Y", "date": "$time"}},
    "count": {"$sum": 1}
}

I get an unknown group operator "$dateToString" error. So I thought of creating the groups and then add a project stage for the "$dateToString" but now the group stage returns groups for every millisecond which defeats the point of grouping.

I realize I am discussing 2 different problems. However, while help with everything would be wonderful, this question is specifically about fixing the mongo query. I will return for the Golang coding on an other thread if necessary. Let me know if I can be clearer.

Answer 1

First of all, if you have an application UI to display the query result, don't bother to format the output in the query. That's the application UI's responsibility. BTW, if you have an application UI, consider using $dateTrunc instead of $dateToString.

Anyway, regarding the requirement in your question, a $project stage like this one should work for you:

[
  {
    "$group": {
      "_id": {
        "$dateToString": { "date": "$time", "format": "%m-%d-%Y" }
      },
      "count": { "$sum": 1 }
    }
  },
  { "$project": { "_id": 0, "time": "$_id", "count": "$count" } }
]

Output from MongoDB Shell:

{ "time" : "02-08-2020", "count" : 2 }
{ "time" : "05-18-2020", "count" : 2 }
{ "time" : "03-20-2021", "count" : 3 }
{ "time" : "01-11-2021", "count" : 1 }

Regarding using the query in a Go project, here is a demo:

package main

import (
    "context"
    "fmt"
    "time"

    "go.mongodb.org/mongo-driver/bson"
    "go.mongodb.org/mongo-driver/mongo"
    "go.mongodb.org/mongo-driver/mongo/options"
)

func main() {
    ctx, cancel := context.WithTimeout(context.Background(), 20*time.Second)
    defer cancel()
    client, err := mongo.Connect(ctx, options.Client().ApplyURI("mongodb://localhost:27017"))
    if err != nil {
        panic(err)
    }

    coll := client.Database("baz").Collection("cakeSales")

    matchStage := bson.D{
        {"$match", bson.D{
            {"$and", []bson.D{
                {{"time", bson.D{
                    {"$gte", time.Date(2019, 6, 1, 0, 0, 0, 0, time.UTC)},
                }}},
                {{"time", bson.D{
                    {"$lte", time.Date(2021, 2, 1, 0, 0, 0, 0, time.UTC)},
                }}},
            }},
        }},
    }

    groupStage := bson.D{
        {"$group", bson.D{
            {"_id", bson.D{
                {"$dateToString", bson.D{
                    {"date", "$time"},
                    {"format", "%m-%d-%Y"},
                }},
            }},
            {"count", bson.D{
                {"$sum", 1},
            }},
        }},
    }

    projectStage := bson.D{
        {"$project", bson.D{
            {"_id", 0},
            {"time", "$_id"},
            {"count", "$count"},
        }},
    }

    cursor, err := coll.Aggregate(context.TODO(),
        mongo.Pipeline{matchStage, groupStage, projectStage})
    if err != nil {
        panic(err)
    }

    var results []bson.M
    if err = cursor.All(context.TODO(), &results); err != nil {
        panic(err)
    }
    for _, result := range results {
        fmt.Printf(
            "time: %s count: %v\n",
            result["time"],
            result["count"])
    }
}