MongoDB Group by field, count it with condition

May 13, 2022

I would like to count different hu for a shipment. So I write this query but how I can count different hu group by shipment ?

my query :

db.eventstranslated.aggregate([
  { "$group": {
    "_id": "$IdShipment",
    "count": { "$sum": 1 }
  }},
  { "$group": {
    "_id": null,
    "ship": {
      "$push": {
        "shipment": "$_id",
        "count": "$count"
      }
    }
  }},
  { "$project": { "_id": 0 }}
])

Result :

{ shipment: A01, count: 1 },
{ shipment: A02, count: 1 },

expect output:

{shipment: A01, count: 2 }
{shipment: A02, count: 1 }

my collection:

{
  _id: 1,
  IdShipment: "A01",
  hu: "C111"
},
{
  _id: 2,
  IdShipment: "A01",
  hu: "C112"
},
{
  _id: 3,
  IdShipment: "A02",
  hu: "D111"
},
{
  _id: 4,
  IdShipment: "A02",
  hu: "D111"
}

>Solution :

You can use two $group stages like this:

First $group has a compound key to get all differents options, and the second $group is to get only differents IdShipment.

db.collection.aggregate([
  {
    "$group": {
      "_id": {
        "IdShipment": "$IdShipment",
        "hu": "$hu"
      }
    }
  },
  {
    "$group": {
      "_id": "$_id.IdShipment",
      "count": {
        "$sum": 1
      }
    }
  }
])

Example here

Also you can use $project to output the name you want. Example here