I'm trying to get the count of transactions, by each company, for each month,
I have data in the following format -

[{
    "id": "1",
    "month": "October",
    "company": "tesla",
    "saleId": "111"
},
{ 
    "id": "2",
    "month": "October",
    "company": "ford",
    "saleId": "222"
},
{
  "id": "3",
  "month": "October",
  "company": "tesla",
  "saleId": "333"
},
{
    "id": "4",
    "month": "November",
    "company": "tesla",
    "saleId": "444"
},
{
    "id": "5",
    "month": "December",
    "company": "kia",
    "saleId": "555"
},
{
    "id": "6",
    "month": "December",
    "company": "ford",
    "saleId": "666"
}]

expected output by month, for each car maker -

{
    "October": [{
            "company": "tesla",
            "count": 2
        },
        {
            "company": "ford",
            "count": 1
        }
    ],
    "November": [{
        "company": "tesla",
        "count": 1
    }],
    "December": [{
            "company": "kia",
            "count": 1
        },
        {
            "company": "ford",
            "count": 1
        }
    ]
}

I've managed to the result by the following query -

let result = await con.aggregate([
    { 
      $group : { 
        _id:{
          month: "$month",
          company: "$company",
        }, 
        count:{$sum:1}
      }
    }
    ]).toArray();

The result is -

[
    { _id: { month: 'October', company: 'tesla' }, count: 2 },
    { _id: { month: 'November', deliveryPartner: 'tesla' }, count: 1 },
    { _id: { month: 'October', deliveryPartner: 'ford' }, count: 1 },
    { _id: { month: 'December', deliveryPartner: 'ford' }, count: 1 },
    { _id: { month: 'December', deliveryPartner: 'kia' }, count: 1 },
]

But I'll have to loop the result to get it into the expected format,
is there a way to handle it on the query level?

CodePudding user response：

You have the right idea, you just need to continue with the restructuring, I achieved this using $replaceRoot and $arrayToObject like so:

db.collection.aggregate([
  {
    $group: {
      _id: {
        month: "$month",
        company: "$company"
      },
      count: {
        $sum: 1
      }
    }
  },
  {
    $sort: {
      count: -1
    }
  },
  {
    $group: {
      _id: "$_id.month",
      v: {
        $push: {
          company: "$_id.company",
          count: "$count"
        }
      }
    }
  },
  {
    $group: {
      _id: null,
      values: {
        $push: {
          k: "$_id",
          v: "$v"
        }
      }
    }
  },
  {
    $replaceRoot: {
      newRoot: {
        "$arrayToObject": "$values"
      }
    }
  }
])

Mongo Playground