Multiple Group By with condition in MongoDB

user_1797689 0
Viewed 53

I have the fields, group_id, category, account, sku and revenue. I want to do an aggregation where the revenue is grouped by the group_id, category, sku and account separately and in sku there will be two divisions, one where there is a comma and one where there isn't. How do I do this?

For example, if my data is

[
  {
    "group_id": "ABC",
    "category": "test",
    "account": "abc1",
    "sku": "grre,qrvf",
    "revenue": 100,
  },
  {
    "group_id": "ABC2",
    "category": "test",
    "account": "abc",
    "sku": "grre,qrvf",
    "revenue": 100,
  },
  {
    "group_id": "ABC3",
    "category": "test2",
    "account": "abc2",
    "sku": "qwe",
    "revenue": 100,
  },
  {
    "group_id": "ABC",
    "category": "test2",
    "account": "abc",
    "sku": "qwe",
    "revenue": 100,
  },
  {
    "group_id": "ABC2",
    "category": "test3",
    "account": "abc2",
    "sku": "asd,fgh",
    "revenue": 100,
  },
  {
    "group_id": "ABC",
    "category": "test3",
    "account": "abc",
    "sku": "asd,fgh",
    "revenue": 100,
  },
  {
    "group_id": "ABC3",
    "category": "test",
    "account": "abc2",
    "sku": "grre,qrvf",
    "revenue": 100,
  }
]

then the result should be like

[
  {groups: [{group_id: "ABC", "revenue": 300}, {group_id: "ABC2", "revenue": 200}, {group_id: "ABC3", "revenue": 200}]},
  {categories: [{category: "test", "revenue": 300}, {category: "test2", "revenue": 200}, {category: "test3", "revenue": 200}]},
  {accounts: [{account: "abc", "revenue": 300}, {account: "abc2", "revenue": 100}, {account: "abc3", "revenue": 300}]},
  {skus: [{single: [{sku: "qwe", revenue: 100}, {sku: "iou", revenue: 100}]}, {multi: [{sku: "grre,qrvf", revenue: 300}, {sku: "asd,fgh", revenue: 200}]}]},
]
aggregation-framework mongodb
1 Answers

You can use $facet to run multiple aggregations separately in one round-trip and $indexOfBytes to split skus:

db.collection.aggregate([
    {
        $facet: {
            group: [ { $group: { _id: "$group_id", revenue: { $sum: "$revenue" } } }, { $project: { _id: 0, group: "$_id", revenue: 1 } } ],
            categories: [ { $group: { _id: "$category", revenue: { $sum: "$revenue" } } }, { $project: { _id: 0, category: "$_id", revenue: 1 } } ],
            accounts: [ { $group: { _id: "$account", revenue: { $sum: "$revenue" } } }, { $project: { _id: 0, account: "$_id", revenue: 1 } } ],
            skus: [ 
                {  $group: { _id: "$sku", revenue: { $sum: "$revenue" } } }, 
                {  $group: { _id: null, aggregates: { $push: { sku: "$_id", revenue: "$revenue" } } } }, 
                {  $project: { 
                    _id: 0,
                    single: { $filter: { input: "$aggregates", cond: { $eq: [ { $indexOfBytes: [ "$$this.sku", "," ] }, -1 ] } } },
                    multi: { $filter: { input: "$aggregates", cond: { $ne: [ { $indexOfBytes: [ "$$this.sku", "," ] }, -1 ] } } },
                },
                }
            ],
        }
    }
])

Mongo Playground

user_62389770
Related