MongoDB group by field and count

Question

I have the following collection data

{
  "_id": 1,
  "date": "2023-11-08",
  "field": "A"
},
{
  "_id": 2,
  "date": "2023-11-08",
  "field": "B"
},
{
  "_id": 3,
  "date": "2023-11-08",
  "field": "C"
},
{
  "_id": 4,
  "date": "2023-11-09",
  "field": "B"
},
{
  "_id": 5,
  "date": "2023-11-09",
  "field": "C"
}

I want group by date and count total and count field value is in Array or not in Array

Array is like this

['A', 'D', 'E', ...]

The output I want

{
  _id: '2023-11-08',
  in_array: 1,
  not_in_array: 2,
  count: 3
},
{
  _id: '2023-11-09',
  in_array: 0,
  not_in_array: 2,
  count: 2
}

how can i make it?

Answer 1

Do conditional $sum in a $group.

db.collection.aggregate([
  {
    "$group": {
      "_id": "$date",
      "in_array": {
        $sum: {
          $let: {
            vars: {
              inputArray: [
                "A",
                "D",
                "E"
              ]
            },
            in: {
              "$cond": {
                "if": {
                  "$in": [
                    "$field",
                    "$$inputArray"
                  ]
                },
                "then": 1,
                "else": 0
              }
            }
          }
        }
      },
      "not_in_array": {
        $sum: {
          $let: {
            vars: {
              inputArray: [
                "A",
                "D",
                "E"
              ]
            },
            in: {
              "$cond": {
                "if": {
                  $not: {
                    "$in": [
                      "$field",
                      "$$inputArray"
                    ]
                  }
                },
                "then": 1,
                "else": 0
              }
            }
          }
        }
      },
      "count": {
        $sum: 1
      }
    }
  }
])

Mongo Playground