英文:
MongoDB Group by one field and count occurrences of field values within an array
问题
{
"CashierName": "RVwGwYjpCsdYJg",
"Items": [
{
"ItemName": "Sausage Biscuit",
"Count": 23
},
{
"ItemName": "LG-ICED LATTE",
"Count": 52
}
]
}
英文:
I have the below data structure per document (we are working with a test set of 1.9 million documents like this):
{
"Object": {
"Items": {
"ItemDetail": [
{
"ItemNumber": 3562,
"ItemName": "LG-ICED LATTE",
},
{
"ItemNumber": 18419,
"ItemName": "Sausage Biscuit",
}
],
},
"Payment": {
"PaymentHeader": {
"CashierNumber": 23,
"CashierName": "RVwGwYjpCsdYJg"
},
},
}
}
}
I would like to be able to produce the following output as efficiently as possible:
{
CashierName: "RVwGwYjpCsdYJg",
Items: [
{
"ItemName": "Sausage Biscuit",
"Count": 23
},
{
"ItemName": "LG-ICED LATTE",
"Count": 52
}
]
}
This is part of a C# project I am working on where I currently generate this kind of output from the following queries but it takes a little longer than we are looking for I am trying to leverage the database to produce the output I need:
aggregate([{ "$project" : { "CashierNumber" : "$Object.Payment.PaymentHeader.CashierNumber", "ItemName" : "$Object.Items.ItemDetail.ItemName" } }, { "$unwind" : "$ItemName" }])
Or
aggregate([{ "$project" : { "CashierNumber" : "$Object.Payment.PaymentHeader.CashierNumber", "ItemName" : "$Object.Items.ItemDetail.ItemName" } }])
Which returns:
{
_id: 6461fec95c6d9dcad9530a2f
CashierNumber: 23
ItemName: "[LG-, $ FULL FLAVOR SHOT, SF VANILLA, SKIM]"
}
OR
{
_id: 6461fec95c6d9dcad9530a2f
CashierNumber: 23
ItemName: "[[LG-] [$FULL FLAVOR SHOT, SF VANILLA, SKIM]]"
}
which we then manipulate.
答案1
得分: 2
-
$unwind
- 将Object.Items.ItemDetail
数组拆分为多个文档。 -
$group
- 根据Object.Payment.PaymentHeader.CashierName
和Object.Items.ItemDetail.ItemName
进行分组,并执行计数。 -
$group
- 根据_id.CashierName
进行分组。将带有ItemName
和Count
字段的文档添加到Items
数组中。 -
$project
- 装饰输出文档。
db.collection.aggregate([
{
$unwind: "$Object.Items.ItemDetail"
},
{
$group: {
_id: {
"CashierName": "$Object.Payment.PaymentHeader.CashierName",
"ItemName": "$Object.Items.ItemDetail.ItemName"
},
Count: {
$count: {}
}
}
},
{
$group: {
_id: "$_id.CashierName",
Items: {
$push: {
ItemName: "$_id.ItemName",
Count: "$Count"
}
}
}
},
{
$project: {
_id: 0,
CashierName: "$_id",
Items: 1
}
}
])
英文:
-
$unwind
- Deconstruct theObject.Items.ItemDetail
array into multiple documents. -
$group
- Group byObject.Payment.PaymentHeader.CashierName
andObject.Items.ItemDetail.ItemName
. And perform the count. -
$group
- Group by_id.CashierName
. Add the document with theItemName
andCount
fields into theItems
array. -
$project
- Decorate the output document.
db.collection.aggregate([
{
$unwind: "$Object.Items.ItemDetail"
},
{
$group: {
_id: {
"CashierName": "$Object.Payment.PaymentHeader.CashierName",
"ItemName": "$Object.Items.ItemDetail.ItemName"
},
Count: {
$count: {}
}
}
},
{
$group: {
_id: "$_id.CashierName",
Items: {
$push: {
ItemName: "$_id.ItemName",
Count: "$Count"
}
}
}
},
{
$project: {
_id: 0,
CashierName: "$_id",
Items: 1
}
}
])
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论