I am looking for the equivalent mongo db query for this tsql query.

select instructor, count(instructor)
from test
group by instructor

I have looked at mongo db documentation but nothing I run seems to work.

db.test.aggregate([
  {
    $group: {
      instructor: "",
      count: {
        $count: {}
      }
    }
  }
])

I get an error saying test is not an accumulator.

My desired output would be something like so.

instructor count a 1 b 7 c 23

CodePudding user response：

For $group stage, you need the _id which is the group key. For the fields other than _id, it must be an accumulator which using the accumulator operator.

db.test.aggregate([
  {
    $group: {
      _id: "$instructor",
      count: {
        $count: {}
      }
    }
  },
  {
    $project: {
      _id: 0,
      test: "$_id",
      count: 1
    }
  }
])

For the update that why the query is not workable for Post Owner, it is due to instructor field was within section_listing array/object.

1. $set - Create instructors field.

   1.1. For the document which has the section_listing field as an array, removes the duplicate instructor with $setUnion.

2. $unwind - Deconstruct instructors array to multiple documents.

3. $group - Group by instructors and perform the count.

4. $project - Decorate the output documents.

db.collection.aggregate([
  {
    $set: {
      instructors: {
        $cond: {
          if: {
            $eq: [
              {
                $type: "$section_listing.instructor"
              },
              "array"
            ]
          },
          then: {
            $setUnion: [
              "$section_listing.instructor"
            ]
          },
          else: "$section_listing.instructor"
        }
      }
    }
  },
  {
    $unwind: "$instructors"
  },
  {
    $group: {
      _id: "$instructors",
      count: {
        $count: {}
      }
    }
  },
  {
    $project: {
      _id: 0,
      instructor: "$_id",
      count: 1
    }
  }
])

Demo @ Playground