Golang mgo使用聚合操作$group返回值。

huangapple go评论83阅读模式
英文:

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)

huangapple
  • 本文由 发表于 2016年2月16日 01:36:34
  • 转载请务必保留本文链接:https://go.coder-hub.com/35415604.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定