希望你能帮助我,我是一个新的聚合查询。
我有一个嵌套的数据结构,我希望对其进行分组以生成统计输出。我有一套订单,其中的订单是一个国家,产品和产品代码。订单如下所示:
>db.orders.findOne();
{
"_id" : ObjectId("5efc6db38cb109193e41c4d3"),
"createdDate" : ISODate("2020-06-25T02:06:25.428Z"),
"data" : {
"nested" : {
"country" : "France"
},
"product" : "Product 4",
"latest" : {
"sub" : {
"code" : "Code 3"
}
}
}
}我有一个聚合查询,按国家/地区、产品和代码分组。
db.getCollection('orders').aggregate([
{
$unwind :{
path: "$data.nested.country"
}
},
{
$group: {
_id: { country: "$data.nested.country", product: "$data.product", code: "$data.latest.sub.code" }
}
}
])这会产生如下输出:
{ "_id" : { "country" : "Slovenia", "product" : "Product 3", "code" : "Code 7" } }
{ "_id" : { "country" : "Japan", "product" : "Product 1", "code" : "Code 9" } }
{ "_id" : { "country" : "Japan", "product" : "Product 4", "code" : "Code 4" } }
{ "_id" : { "country" : "China", "product" : "Product 1", "code" : "Code 1" } }
{ "_id" : { "country" : "France", "product" : "Product 3", "code" : "Code 4" } }
{ "_id" : { "country" : "Japan", "product" : "Product 4", "code" : "Code 8" } }
{ "_id" : { "country" : "Japan", "product" : "Product 4", "code" : "Code 5" } }
{ "_id" : { "country" : "Slovenia", "product" : "Product 4", "code" : "Code 4" } }
{ "_id" : { "country" : "Slovenia", "product" : "Product 4", "code" : "Code 7" } }
{ "_id" : { "country" : "Slovenia", "product" : "Product 4", "code" : "Code 2" } }
{ "_id" : { "country" : "China", "product" : "Product 4", "code" : "Code 8" } }
{ "_id" : { "country" : "France", "product" : "Product 4", "code" : "Code 4" } }
{ "_id" : { "country" : "Japan", "product" : "Product 2", "code" : "Code 3" } }
{ "_id" : { "country" : "Japan", "product" : "Product 2", "code" : "Code 6" } }
{ "_id" : { "country" : "Japan", "product" : "Product 2", "code" : "Code 3" } }
{ "_id" : { "country" : "Slovenia", "product" : "Product 2", "code" : "Code 9" } }
{ "_id" : { "country" : "China", "product" : "Product 2", "code" : "Code 6" } }我想按国家分组这些数据,然后产品,然后编码,例如,日本将有一个产品的列表,即Product 4, Product 2在每个将有一个代码的列表,所以"Product 4": ["Code 8","Code 5","Code 3","Code 6","Code 2"]等。
{ "_id" : { "country": "Japan", products: [{"product":"Product 2","codes":[{"code":"Code 3","count":2},{"code":"Code 6","count":1]}]发布于 2020-07-02 13:35:33
你在正确的轨道上。除非您希望data.nested.country在某些顺序中是一个数组,否则您不应该需要$unwind阶段。
小组赛阶段是一个很好的第一步,你只需要添加一个计数器:
{$group: {
_id: {
country: "$data.nested.country",
product: "$data.product",
code: "$data.latest.sub.code"
},
count: {$sum:1}
}}这就给你留下了不同的country+product+code,频率计数为each。然后按国家/地区和产品分组,将所有编码值及其计数收集到一个数组中:
{$group: {
_id: {
country: "$_id.country",
product: "$_id.product"
},
codes:{
$push: {
code:"$_id.code",
count:"$count"
}
}
}}这使得每个产品都有一个包含它们的计数的代码数组。最后,按国家分组,并将产品收集到一个数组中:
{$group: {
_id: {
country: "$_id.country"
},
products: {
$push:{
product:"$_id.product",
codes: "$codes"
},
}
}}https://stackoverflow.com/questions/62675872
复制相似问题