英文:
Golang mgo returning value with aggregate $group
问题
如何使聚合查询返回在$group语句中使用的字段值。
代码如下:
type TheGroup struct{
Id bson.ObjectId `json:"id,omitempty" bson:"_id,omitempty"`
Totalamount int
Dayofyear int
Actualyear string
Transactiondate string
Count int
}
var results []TheGroup
o1 := bson.M{"$match" :bson.M{"transactiontype": transactiontype}}
o2 := bson.M{"$group" : bson.M{"_id": bson.M{"day": "$dayofyear", "year":"$actualyear"},"totalamount":bson.M{"$sum":"$qty"}, "count":bson.M{"$sum":1}}}
operations := []bson.M{o1, o2}
pipe := collection.Pipe(operations)
err1 := pipe.All(&results)
if err := json.NewEncoder(w).Encode(results); err != nil {
panic(err)
}
输出结果如下:
[{"Totalamount":2061,"Dayofyear":0,"Actualyear":"","Transactiondate":"","Count":679},{"Totalamount":8705,"Dayofyear":0,"Actualyear":"","Transactiondate":"","Count":2145},{"Totalamount":8156,"Dayofyear":0,"Actualyear":"","Transactiondate":"","Count":2806},{"Totalamount":9865,"Dayofyear":0,"Actualyear":"","Transactiondate":"","Count":3294},{"Totalamount":9619,"Dayofyear":0,"Actualyear":"","Transactiondate":"","Count":3102},{"Totalamount":9975,"Dayofyear":0,"Actualyear":"","Transactiondate":"","Count":3457},{"Totalamount":14839,"Dayofyear":0,"Actualyear":"","Transactiondate":"","Count":4036},{"Totalamount":5100,"Dayofyear":0,"Actualyear":"","Transactiondate":"","Count":1699},{"Totalamount":9649,"Dayofyear":0,"Actualyear":"","Transactiondate":"","Count":2854},{"Totalamount":11457,"Dayofyear":0,"Actualyear":"","Transactiondate":"","Count":3220},{"Totalamount":12643,"Dayofyear":0,"Actualyear":"","Transactiondate":"","Count":3860},{"Totalamount":10301,"Dayofyear":0,"Actualyear":"","Transactiondate":"","Count":3620},{"Totalamount":7681,"Dayofyear":0,"Actualyear":"","Transactiondate":"","Count":2816},{"Totalamount":8130,"Dayofyear":0,"Actualyear":"","Transactiondate":"","Count":3023}]
我认为我理解为什么Dayofyear / Actualyear没有被填充 - 因为在$group遍历匹配的文档时,没有对文档值进行聚合 - 但是我该如何使它们填充呢?
英文:
How do I get an aggregate query to return field values used in the $group statement.
The code:
type TheGroup struct{
Id bson.ObjectId `json:"id,omitempty" bson:"_id,omitempty"`
Totalamount int
Dayofyear int
Actualyear string
Transactiondate string
Count int
}
var results []TheGroup
o1 := bson.M{"$match" :bson.M{"transactiontype": transactiontype},}
o2 := bson.M{"$group" : bson.M{"_id": bson.M{"day": "$dayofyear", "year":"$actualyear"},"totalamount":bson.M{"$sum":"$qty"}, "count":bson.M{"$sum":1}},}
operations := []bson.M{o1, o2}
pipe := collection.Pipe(operations)
err1 := pipe.All(&results)
if err := json.NewEncoder(w).Encode(results); err != nil {
panic(err)
}
The output is as follows:
[{"Totalamount":2061,"Dayofyear":0,"Actualyear":"","Transactiondate":"","Count":679},{"Totalamount":8705,"Dayofyear":0,"Actualyear":"","Transactiondate":"","Count":2145},{"Totalamount":8156,"Dayofyear":0,"Actualyear":"","Transactiondate":"","Count":2806},{"Totalamount":9865,"Dayofyear":0,"Actualyear":"","Transactiondate":"","Count":3294},{"Totalamount":9619,"Dayofyear":0,"Actualyear":"","Transactiondate":"","Count":3102},{"Totalamount":9975,"Dayofyear":0,"Actualyear":"","Transactiondate":"","Count":3457},{"Totalamount":14839,"Dayofyear":0,"Actualyear":"","Transactiondate":"","Count":4036},{"Totalamount":5100,"Dayofyear":0,"Actualyear":"","Transactiondate":"","Count":1699},{"Totalamount":9649,"Dayofyear":0,"Actualyear":"","Transactiondate":"","Count":2854},{"Totalamount":11457,"Dayofyear":0,"Actualyear":"","Transactiondate":"","Count":3220},{"Totalamount":12643,"Dayofyear":0,"Actualyear":"","Transactiondate":"","Count":3860},{"Totalamount":10301,"Dayofyear":0,"Actualyear":"","Transactiondate":"","Count":3620},{"Totalamount":7681,"Dayofyear":0,"Actualyear":"","Transactiondate":"","Count":2816},{"Totalamount":8130,"Dayofyear":0,"Actualyear":"","Transactiondate":"","Count":3023}]
I think I understand why Dayofyear / Actualyear are not populated - because as there is no aggregration of the document values as the matched documents are traversed by the $group - but how do I get them to populate?
答案1
得分: 4
在mongo shell中运行以下管道应该会给出正确的结果:
pipeline = [
{
"$match": { "transactiontype": transactiontype }
},
{
"$group": {
"_id": {
"year": { "$year": "$transactiondate" },
"dayOfYear": { "$dayOfYear": "$transactiondate" }
},
"totalamount": { "$sum": "$qty" },
"count": { "$sum": 1 },
"date": { "$first": "$transactiondate"}
}
},
{
"$project": {
"_id": 0,
"totalamount": 1,
"dayOfYear": "$_id.dayOfYear",
"actualyear": { "$substr": [ "$_id.year", 0, 4 ] },
"transactiondate": {
"$dateToString": { "format": "%Y-%m-%d %H:%M", "date": "$date" }
},
"count": 1
}
}
]
db.collection.aggregate(pipeline)
其中等效的mGo表达式如下(未经测试):
pipeline := []bson.M{
bson.M{
"$match": bson.M{ "transactiontype": transactiontype }
},
bson.M{
"$group": bson.M{
"_id": bson.M{
"year": bson.M{ "$year": "$transactiondate" },
"dayOfYear": bson.M{ "$dayOfYear": "$transactiondate" }
},
"totalamount": bson.M{ "$sum": "$qty" },
"count": bson.M{ "$sum": 1 },
"date": bson.M{ "$first": "$transactiondate"}
}
},
bson.M{
"$project": bson.M{
"_id": 0,
"totalamount": 1,
"dayOfYear": "$_id.dayOfYear",
"actualyear": bson.M{ "$substr": []interface{}{ "$_id.year", 0, 4 } },
"transactiondate": bson.M{
"$dateToString": bson.M{ "format": "%Y-%m-%d %H:%M", "date": "$date" }
},
"count": 1
}
}
}
pipe := collection.Pipe(pipeline)
以上是给出正确结果的在mongo shell中运行的管道和等效的mGo表达式。
英文:
Running the following pipeline in mongo shell should give you the correct result:
pipeline = [
{
"$match": { "transactiontype": transactiontype }
},
{
"$group": {
"_id": {
"year": { "$year": "$transactiondate" },
"dayOfYear": { "$dayOfYear": "$transactiondate" }
},
"totalamount": { "$sum": "$qty" },
"count": { "$sum": 1 },
"date": { "$first": "$transactiondate"}
}
},
{
"$project": {
"_id": 0,
"totalamount": 1,
"dayOfYear": "$_id.dayOfYear",
"actualyear": { "$substr": [ "$_id.year", 0, 4 ] },
"transactiondate": {
"$dateToString": { "format": "%Y-%m-%d %H:%M", "date": "$date" }
},
"count": 1
}
}
]
db.collection.aggregate(pipeline)
of which the equivalent mGo expression follows (untested):
pipeline := []bson.M{
bson.M{
"$match": bson.M{ "transactiontype": transactiontype }
},
bson.M{
"$group": bson.M{
"_id": bson.M{
"year": bson.M{ "$year": "$transactiondate" },
"dayOfYear": bson.M{ "$dayOfYear": "$transactiondate" }
},
"totalamount": bson.M{ "$sum": "$qty" },
"count": bson.M{ "$sum": 1 },
"date": bson.M{ "$first": "$transactiondate"}
}
},
bson.M{
"$project": bson.M{
"_id": 0,
"totalamount": 1,
"dayOfYear": "$_id.dayOfYear",
"actualyear": bson.M{ "$substr": []interface{}{ "$_id.year", 0, 4 } },
"transactiondate": bson.M{
"$dateToString": bson.M{ "format": "%Y-%m-%d %H:%M", "date": "$date" }
},
"count": 1
}
}
}
pipe := collection.Pipe(pipeline)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论