如何在MongoDB Compass中运行`select subject,count(*) from books group by subject`查询？

Hey there! Let's break down how to do this grouping and count operation in MongoDB Compass—it's straightforward once you know where to look, since MongoDB uses aggregation pipelines for this kind of work instead of SQL-style GROUP BY.

Step-by-Step Guide #

1. Connect to your database & select the books collection
   Open up MongoDB Compass, connect to your cluster/local instance, navigate to your target database, and click on the books collection to access its data.

2. Switch to the Aggregation tab
   At the top of the Compass window, you’ll see tabs like Overview, Documents, Aggregation—click on Aggregation. This is where we’ll build our grouping pipeline.

3. Add the $group stage (core grouping logic)

   • Click the Add Stage button, then select $group from the dropdown (or just type it directly in the stage input field).
   • Configure the $group stage with this code:

     {
       "_id": "$subject",
       "count": { "$sum": 1 }
     }
     

     Let’s map this to your SQL query:
     • _id: "$subject" = GROUP BY subject—this tells MongoDB to group documents using the subject field as the key.
     • count: { $sum: 1 } = COUNT(*)—this adds 1 for every document in each group, tallying up the total per subject.

4. (Optional) Clean up output to match SQL format
   If you want the result to mirror your SQL output exactly (with a subject field instead of MongoDB’s default _id grouping key), add a $project stage:

   • Click Add Stage again, select $project, and use this configuration:

     {
       "subject": "$_id",
       "count": 1,
       "_id": 0
     }
     

   This renames _id to subject and hides the original _id field, so your output will look like:

   { "subject": "fiction", "count": 15 },
   { "subject": "history", "count": 8 },
   ...
   

5. Run the pipeline
   Hit the Run button at the top right, and you’ll see your grouped count results load immediately.

Full Aggregation Pipeline Code #

If you prefer to paste the entire pipeline directly into Compass’s pipeline editor (the text area at the top of the Aggregation tab), here’s the complete code:

[
  {
    "$group": {
      "_id": "$subject",
      "count": { "$sum": 1 }
    }
  },
  {
    "$project": {
      "subject": "$_id",
      "count": 1,
      "_id": 0
    }
  }
]

That’s all there is to it! Let me know if you hit any snags getting this set up.

内容的提问来源于stack exchange，提问作者AVEbrahimi