Retrieve item count for each date on MongoDB aggregate

Question content

I have a collection containing events. Each event has a timestamp accurate to milliseconds. I want to group daily events. For example:

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 do

key        value

date       04-22-2023
count      1.0

date       04-23-2023
count      2.0

The ultimate goal is to use queries in golang projects.

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
                }
            }
        }
    ]

return

key            value

_id            04-24-2023
count          476.0

_id            04-23-2023
count          28.0

This was supposed to work, but when I write this query in my go project, a red squiggly line appears under "$datetostring" with the message "Invalid field name", ideally I would like the date to have a " time" key instead of "_id". When I make the following changes during the group stage:

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

I'm getting an unknown group operator '$datetostring' error. So I thought of creating the group and then adding an item stage for "$datetostring" but now the group stage returns the group every millisecond which defeats the purpose of grouping.

I realize I'm discussing two different issues. However, while any help would be great, this question is specifically about fixing mongo queries. I'll return to golang coding on another thread if necessary. If I can make it clearer, please let me know.

Correct answer

First of all, if you have an application ui to display the query results, you don't have to bother formatting the output in the query. This is the responsibility of the application ui. BTW, if you have an application ui, consider using $datetrunc instead of $datetostring.

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

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

mongodb shell output:

{ "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 queries in go projects, here is a demonstration:

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"])
    }
}

The above is the detailed content of Retrieve item count for each date on MongoDB aggregate. For more information, please follow other related articles on the PHP Chinese website!