How to write an aggregate query for nested documents?

Question

user Schema

_id: ObjectId
applications: [
    {
        jobId: ObjectId
        status: String (can be accepted / rejected / active)
    },
]

This is my schema for each user on my web app. I want the mongo query I can use for the following:

For an array of jobIDs, I need to find out the aggregate sums of all the users who have applied to the job grouped by the status. So for example,

For [10,20,34,53] Job IDs, I need to get the following output:

[
    { jobId: 10, 
      accepted: 20 (20 users have been accepted)
      rejected: 17 (17 users have been rejected)
      active: 12 (12 users have active applications)
    },
    { jobId: 20, 
      accepted: 20 (20 users have been accepted)
      rejected: 17 (17 users have been rejected)
      active: 12 (12 users have active applications)
    },
    .
    .
]

Mohammad Faisal · Accepted Answer · 2020-09-17 07:31:39Z

You can try the following query, I have used $unwind and then $group to group by jobId

db.user.aggregate([
    { "$unwind": "$applications" },
    { "$match": { "applications.jobId": { "$in": [10, 20, 34, 53]  } }},
    { "$group": { 
         "_id":"$applications.jobId",
         "jobId": { "$first": "$applications.jobId"},
         "accepted": { "$sum": {"$cond": [{"$eq": ["$applications.status","accepted"] }, 1, 0 ]} },
         "rejected": {"$sum": {"$cond": [{"$eq": ["$applications.status","rejected"] }, 1, 0 ]} },
         "active": {"$sum": {"$cond": [{"$eq": ["$applications.status", "active"] }, 1, 0 ]} }
    } }
])

I have tested the above query here, Mongo Playground

Collectives™ on Stack Overflow

How to write an aggregate query for nested documents?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related