MongoDB按一个字段分组并计算数组中字段值的出现次数。

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

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

  1. $unwind - 将 Object.Items.ItemDetail 数组拆分为多个文档。

  2. $group - 根据 Object.Payment.PaymentHeader.CashierNameObject.Items.ItemDetail.ItemName 进行分组,并执行计数。

  3. $group - 根据 _id.CashierName 进行分组。将带有 ItemNameCount 字段的文档添加到 Items 数组中。

  4. $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
    }
  }
])

在 Mongo Playground 上查看演示

英文:
  1. $unwind - Deconstruct the Object.Items.ItemDetail array into multiple documents.

  2. $group - Group by Object.Payment.PaymentHeader.CashierName and Object.Items.ItemDetail.ItemName. And perform the count.

  3. $group - Group by _id.CashierName. Add the document with the ItemName and Count fields into the Items array.

  4. $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
    }
  }
])

Demo @ Mongo Playground

huangapple
  • 本文由 发表于 2023年6月29日 20:51:11
  • 转载请务必保留本文链接:https://go.coder-hub.com/76581240.html
匿名

发表评论

匿名网友

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

确定