Retrieve item count for each date on MongoDB aggregate-Golang-php.cn

Retrieve item count for each date on MongoDB aggregate

王林

Release： 2024-02-06 10:36:04

forward

435 people have browsed it

检索 MongoDB 聚合上每个日期的项目计数

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

Copy after login

I do

key        value

date       04-22-2023
count      1.0

date       04-23-2023
count      2.0

Copy after login

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

Copy after login

return

key            value

_id            04-24-2023
count          476.0

_id            04-23-2023
count          28.0

Copy after login

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

Copy after login

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

Copy after login

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 }

Copy after login

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

Copy after login

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!