aggregation framework - Group by an optional field in mongodb

Question

I would like to independently group the results of an or clause, including overlap. The data set is rather large so running 2 queries sequentially will result in an undesirable wait time. I am hoping I can somehow project which clause returned the corresponding data. Given this data set:

[
  {
    "_id": 1,
    "item": "abc",
    "name": "Michael",
    "price": NumberDecimal("10"),
    "quantity": NumberInt("2"),
    "date": ISODate("2014-03-01T08:00:00Z")
  },
  {
    "_id": 2,
    "item": "jkl",
    "name": "Toby",
    "price": NumberDecimal("20"),
    "quantity": NumberInt("1"),
    "date": ISODate("2014-03-01T09:00:00Z")
  },
  {
    "_id": 3,
    "item": "xyz",
    "name": "Keith",
    "price": NumberDecimal("5"),
    "quantity": NumberInt("10"),
    "date": ISODate("2014-03-15T09:00:00Z")
  },
  {
    "_id": 4,
    "item": "abc",
    "name": "Dwight",
    "price": NumberDecimal("5"),
    "quantity": NumberInt("20"),
    "date": ISODate("2014-04-04T11:21:39.736Z")
  },
  {
    "_id": 5,
    "item": "abc",
    "name": "Ryan",
    "price": NumberDecimal("10"),
    "quantity": NumberInt("10"),
    "date": ISODate("2014-04-04T21:23:13.331Z")
  },
  {
    "_id": 6,
    "item": "def",
    "name": "Jim",
    "price": NumberDecimal("7.5"),
    "quantity": NumberInt("5"),
    "date": ISODate("2015-06-04T05:08:13Z")
  },
  {
    "_id": 7,
    "item": "abc",
    "name": "Keith",
    "price": NumberDecimal("7.5"),
    "quantity": NumberInt("10"),
    "date": ISODate("2015-09-10T08:43:00Z")
  },
  {
    "_id": 8,
    "item": "abc",
    "name": "Michael",
    "price": NumberDecimal("10"),
    "quantity": NumberInt("5"),
    "date": ISODate("2016-02-06T20:20:13Z")
  },
  
] 

I would like to receive this result:

 [{
    "_id": {
      "name": "Keith"
    },
    "count": 2
  },
  {
    "_id": {
      "item": "abc",
    },
    "count": 5
  }]

Here is what I have tried so far:

db.collection.aggregate([
  {
    $match: {
      $or: [
        {
          item: "abc"
        },
        {
          name: "Keith"
        }
      ]
    }
  },
  {
    $group: {
      _id: {
        item: "$item",
        name: "$name"
      },
      count: {
        $sum: 1
      }
    }
  }
])

question from:https://stackoverflow.com/questions/65908030/group-by-an-optional-field-in-mongodb

Answer 1

You can use $facet to get multiple aggregation pipelines into the same stage in this way:

Using $facet there are two "outputs" one group by name and other by item.

In each one there are multiple stages:

• First $match to process only documents you want.
• Then $group with _id name or item, and $count to get the total.

db.collection.aggregate([
  {
    "$facet": {
      "groupByName": [
        {
          "$match": {"name": "Keith"}
        },
        {
          "$group": {"_id": "$name","count": {"$sum": 1}}
        }
      ],
      "groupByItem": [
        {
          "$match": {"item": "abc"}
        },
        {
          "$group": {"_id": "$item","count": {"$sum": 1}}
        }
      ]
    }
  }
])

Example here

The output is:

{
    "groupByItem": [
      {
        "_id": "abc",
        "count": 5
      }
    ],
    "groupByName": [
      {
        "_id": "Keith",
        "count": 2
      }
    ]
  }