CODEWITHSUNDEEP
mongodbaggregation-framework
Sean Barry
Sean Barry

Reputation: 71

How to combine results in a Mongo aggregation query

I'm new to aggregation queries in Mongo and been really struggling trying to produce the output I want. I have the following aggregation query:

db.events.aggregate([
  { $match: { requestState: "APPROVED" } },
  { $unwind: { path: "$payload.description" } },
  { $group: { _id: { instr: "$payload.description", bu: "$createdByUser", count: { $sum: 1 } } } }
]);

that returns the following results:

{ "_id" : { "instr" : "ABC-123", "bu" : "BU2", "count" : 1 } }
{ "_id" : { "instr" : "ABC-123", "bu" : "BU1", "count" : 1 } }
{ "_id" : { "instr" : "DEF-456", "bu" : "BU1", "count" : 1 } }

How can I amend the aggregation query so that there are only 2 documents returned instead of 3? With the two "ABC-123" results combined into a single result with a new array of counts with the "bu" and "count" fields i.e.

{ "_id" : { "instr" : "ABC-123", "counts": [ { "bu" : "BU1", "count" : 1 }, { "bu" : "BU2", "count" : 1 } ] } }

Many thanks

Upvotes: 0

Views: 735

Answers (1)

thammada.ts
thammada.ts

Reputation: 5245

You can add another stage to only $group by _id.instr and another stage to $project to your desired output shape

db.events.aggregate([
  {
    $match: { requestState: "APPROVED" }
  },
  {
    $unwind: { path: "$payload.description" }
  },
  {
    $group: {
      _id: { instr: "$payload.description", bu: "$createdByUser", count: { $sum: 1 } }
    }
  },
  {
    $group: {
      _id: { instr: "$_id.instr" },
      counts: { $push: { bu: "$_id.bu", count: "$_id.count" } }
    }
  },
  {
    $project: {
      _id: { instr: "$_id.instr", counts: "$counts" }
    }
  }
]);

Upvotes: 1

Related Questions