我正在尝试从以下数据编写嵌套的组查询:
[
{
"mmyy": "JAN-2019",
"location": "Boston",
"category": "Shoes",
"qty": 5
},
{
"mmyy": "JAN-2019",
"location": "New York",
"category": "Shoes",
"qty": 10
},
{
"mmyy": "JAN-2019",
"location": "Boston",
"category": "Hats",
"qty": 2
},
{
"mmyy": "JAN-2019",
"location": "New York",
"category": "Hats",
"qty": 3
},
{
"mmyy": "FEB-2019",
"location": "Boston",
"category": "Shoes",
"qty": 5
},
{
"mmyy": "FEB-2019",
"location": "New York",
"category": "Hats",
"qty": 10
},
]
预期结果:
[
{
month: "JAN-2019",
month_total: 20,
locations:[
{
name: "Boston",
location_total: 7,
categories: [
{name: "Shoes", total: 5},
{name: "Hats", total: 2},
]
}
...
]
},
...
]
我能够进行第二级汇总(针对“月份”和“位置”),但是很难将所有汇总归类到嵌套结果中。这是我使用多个$group
和做的$push
:
db.sales.aggregate([
{$group: {
_id:{ month: "$mmyy", location: "$location"},
total: { $sum: "$qty"}
}},
{ $group:{
_id: "$_id.month",
monthly_total: { $sum: "$total" },
locations: {
$push:{
name: "$_id.location",
total: "$total"
}
}
}}
])
你需要$push
的categories
第一$group
阶段。
db.collection.aggregate([
{ "$group": {
"_id": {
"month": "$mmyy",
"location": "$location",
"category": "$category"
},
"total": { "$sum": "$qty" }
}},
{ "$group": {
"_id": {
"month": "$_id.month",
"location": "$_id.location"
},
"categories": {
"$push": {
"name": "$_id.category",
"total": "$total"
}
}
}},
{ "$group": {
"_id": "$_id.month",
"locations": {
"$push": {
"name": "$_id.location",
"categories": "$categories",
"location_total": {
"$sum": "$categories.total"
}
}
}
}}
])
当我这样做时,实际上似乎有一个奇怪的行为。在每个“位置”下,我得到一个重复的“类别”的列表,其中同一类别多次出现,并且
total
在它们之间进行拆分。你能在这里告诉我吗mongoplayground.net/p/lxnRgQatfYx
当然。如果您在任何月份的某个位置的每个“类别”都有多个条目,则会发生这种情况。看到这里:https : //mongoplayground.net/p/J7sIWG5i0My
更新了我的答案