Mongodb Question posted in
The official documentation can be found here.

Mongodb – Get document counts based on unique field values

I have a Mongoose job model, each job has a US State. I’m attempting to make 1 query that will give me back the number of jobs that are in each state. Goal would be to populate a US heat map of jobs.

My model looks like the following:

const JobSchema = new Schema({
  state: {
    type: String,
    required: true
  },
  status: {
    type: String,
    enum: ['Available', 'Accepted', 'Closed'],
    default: 'Available'
  }
}, { versionKey: false, timestamps: true });

The result I’m looking for would give me something close to:

{
  docs: [
    {
      state: 'FL',
      count: 27
    },
    {
      state: 'NC',
      count: 41
    }
  ]
}

I’ve tinkered with iterations if $distinct and others but no luck. Any help would be appreciated.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Armin's answer above lead down the right path to solving the problem. Kudos to Mongo Atlas for including aggregation in their interface which helped me understand stages in aggregation.

    There was one additional complexity to my above question that's worth noting here for the next person learning aggregation. The system I'm building is multi-tenant, so jobs belong to one organization - and there are many orgs with many jobs each. I wanted my query to enable getting counts for jobs belonging to a single org.

    My aggregation query ended up being:

       const aggConfig = [
      {
        $match: {
          organizationId: new mongoose.Types.ObjectId(orgId)
        }
      },
      {
        $group: {
          '_id': '$state', 
          'count': {
            '$sum': 1
          }
        }
      }
    ];

    Note that the agg query is an array. Each item in the array is a stage, a stage being a step in the process - order matters in this case. Step 1 (aggConfig[0]) isolates the result set to only the jobs that belong to a specific organization. Stage 2 (aggConfig[1]) performs the summation of jobs by state, returning the desired array of data.

    To execute with a Mongoose model:

    const agggregatedByState = await JobModel.aggregate(aggConfig);

  2. 0

    You need to create an aggregation like below:

    [
  {
     $group: {
        _id: "$state",
        count: {
          $sum: 1
        }
     }   
  }
]

    This aggregation groups data by state and the count uses the $sum operator and for each document adds one.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search