CODEWITHSUNDEEP
mongodbmongooseaggregation-framework
Farhan Tahir
Farhan Tahir

Reputation: 2144

Group by based on the field of sub documents in mongodb

I have following documents stored in a mongodb collection:

{
    "_id" : ObjectId("5b56d4072eb1102f495c11ab"),
    "currentStage" : ObjectId("5b06999d889de3bd613ab79d"),
    "stagePermissions" : [
      {
            "_id" : ObjectId("5b55a8cd8bb066994dc21314"),
            "status" : "AWAITING_REVIEW",
            "stage" : ObjectId("5b06999d889de3bd613ab79d"),
      },
      {
            "_id" : ObjectId("5b55a8cd8bb066994dc21354"),
            "status" : "IN_PROGRESS",
            "stage" : ObjectId("5b06999d889de3bd613ac79d"),
      }
    ]

}

I want to get counts of documents by stagePermissions.status where stagePermissions.stage equals to currentStage. 

Expected OutPut: 
[
  {_id: IN_PROGRESS, count: 1},
  {_id: AWAITING_REVIEW, count: 1}
]

Upvotes: 1

Views: 311

Answers (2)

Subhashree Pradhan
Subhashree Pradhan

Reputation: 812

Update :

For Mongo 3.4, you can make use of this aggregation query, since $expr is introduced only in Mongo 3.6 :

db.test.aggregate([
    { $unwind : "$stagePermissions"},
    {$project: {_id : 1, currentStage : 1, stagePermissions : 1, value : {$cmp: ['$currentStage','$stagePermissions.stage']}}},
    {$match: { value : {$eq:0}}},
    { $group :
        {
        _id : "$stagePermissions.status",
        count : {$sum : 1}
        }
    }
])

Here we are using the $cmp operator to first obtain a comparison value and then $match operator to find those documents that are equal in nature.

========================================================================= Input Document :

{
        "_id" : ObjectId("5b56d4072eb1102f495c11ab"),
        "currentStage" : ObjectId("5b06999d889de3bd613ab79d"),
        "stagePermissions" : [
                {
                        "_id" : ObjectId("5b55a8cd8bb066994dc21314"),
                        "status" : "AWAITING_REVIEW",
                        "stage" : ObjectId("5b06999d889de3bd613ab79d")
                },
                {
                        "_id" : ObjectId("5b55a8cd8bb066994dc21354"),
                        "status" : "IN_PROGRESS",
                        "stage" : ObjectId("5b06999d889de3bd613ab79d")
                }
        ]
}

Aggregate Query :

db.test.aggregate([
    { $unwind : "$stagePermissions"},
    { $match : {$expr : {$eq : ["$currentStage", "$stagePermissions.stage" ]}}},
    { $group :
        {
        _id : "$stagePermissions.status",
        count : {$sum : 1}
        }
    }
])

Output : 

{ "_id" : "IN_PROGRESS", "count" : 1 }
{ "_id" : "AWAITING_REVIEW", "count" : 1 }

Upvotes: 1

s7vr
s7vr

Reputation: 75994

You can use below aggregation in 3.6 version.

$match with $in to consider only documents where there is at least one permission stage matches current stage permission.

$unwind to expand the stage permissions and filter all permission matching current stage permission.

$group on stage permission status followed by $sum to count the permissions.

db.col.aggregate([
  {"$match":{"$expr":{"$in":["$currentStage","$stagePermissions.stage"]}}},
  {"$unwind":"$stagePermissions"},
  {"$match":{"$expr":{"$eq":["$currentStage","$stagePermissions.stage"]}}},
  {"$group":{"_id":"$stagePermissions.status","count":{"$sum":1}}}
])

3.4 Update

$filter to output sub documents where the stage permission stage is same as current stage.

$addFields to overwrite the stage permission array with filter output.

The query will return all the participants documents with users sub documents that have age as participants age.

db.col.aggregate([
  {"$addFields":{"stagePermissions":{"$filter":{"input":"$stagePermissions","cond":{"$eq":["$$this.stage","$currentStage"]}}}}},
  {"$unwind":"$stagePermissions"},
  {"$group":{"_id":"$stagePermissions.status","count":{"$sum":1}}}
])

Upvotes: 1

Related Questions