CODEWITHSUNDEEP
arraysmongodbmongodb-queryaggregation-framework
VBC
VBC

Reputation: 301

How to compare the elements of an array from the same object in MongoDB?

I've a document collection with the below data

[{
_id:ObjectId(),
status:"created",
users:[{
email:"[email protected]",
role:"admin"
},
{
email:"[email protected]",
role:"admin"
},
{
email:"[email protected]",
role:"user"
}]
},
{
_id:ObjectId(),
status:"created",
users:[{
email:"[email protected]",
role:"admin"
},
{
email:"[email protected]",
role:"admin"
},
{
email:"[email protected]",
role:"user"
}]
}]

I want to fetch the count of documents which have the user with both "admin" and "user" roles and the status is "created". Now I'm trying to match the email id and also the role.

db.documents.aggregate([
       {"$match":{
           "status":"created",
    "$expr":{
      "$eq":[
        {"$size":{"$setIntersection":["$users.email_id","$users.email_id"]}},
        0
      ]
    }
  }},
    ])

Is there any way to compare the elements of the array from the same object?

Upvotes: 0

Views: 798

Answers (1)

prasad_
prasad_

Reputation: 14287

I want to fetch the count of documents which have the user with both "admin" and "user" roles and the status is "created". Now I'm trying to match the email id and also the role.

You can use an aggregation or a find query as follows:

var MATCH_EMAIL = "[email protected]"

db.collection.aggregate( [
  { 
      $match: { 
          $and: [ { status: "created" } , { "users": { $elemMatch: { role: "admin", email: MATCH_EMAIL } } } ],
          "users": { $elemMatch: { role: "user", email: MATCH_EMAIL } } 
      } 
  },
  { 
      $count: "Count" 
  }
] )

// -- or --

db.collection.find( { 
    $and: [ { status: "created" } , { "users": { $elemMatch: { role: "admin", email: MATCH_EMAIL } } } ],
    "users": { $elemMatch: { role: "user", email: MATCH_EMAIL } } 
} ).count()

Note the query filter is same in both cases. The aggregation returns a result document like { "Count" : 1 }, but the find query returns just the count value, like 1.

[ EDIT ADD ]

Updated code to have the same email for "user" and "admin" riles.

db.collection.aggregate( [
  { 
      $match: { 
          $and: [ { status: "created" } , { "users.role": "admin" } ],
          "users.role": "user"
      } 
  },
  { 
      $unwind: "$users" 
  },
  { 
      $group: { 
          _id: { _id: "$_id", email: "$users.email" },
          roles: { $push: "$users.role" }
      } 
  },
  { 
      $match: { 
          $expr: { $gt: [ { $size: "$roles" }, 1 ] } 
      } 
  },
  { 
      $count: "Count with user+admin roles and same email" 
  }
] )

Upvotes: 1

Related Questions