CODEWITHSUNDEEP
mongodbmongoosemongodb-query
strblr
strblr

Reputation: 950

Finding all docs whose nested array contains at least two specific subdocuments

I have a collection Project with documents that look like the following :

{
  _id: ObjectId(...),
  name: "some name",
  members: [
    {
      user: ObjectId(...),
      joined: false,
      ...
    },
    {
      user: ObjectId(...),
      joined: true,
      ...
    },
    {
      user: ObjectId(...),
      joined: true,
      ...
    },
    ...
  ]

Given two user ids, I would like to query all the documents where members contains at least both users with joined equal to true. Additional members could be present, but the two must be present. I have no clue what to do, especially with $elemMatch. Any idea ?

Intuitively, I would have done something like this :

Project.find({
  members: {
    $elemMatch: [
      {
        user: firstId,
        joined: true
      },
      {
        user: secondId,
        joined: true
      }
    ]
  }
})

Upvotes: 0

Views: 44

Answers (3)

raga
raga

Reputation: 937

You can do:

db.collection.find({
  "members.joined": true,
  "members.user": {
    $all: [
      3,
      4
    ]
  }
})

Playground

Upvotes: 1

D. SM
D. SM

Reputation: 14520

Use $and over two $elemMatch clauses:

MongoDB Enterprise mongos> db.foo.insert({_id:1,members:[{a:1,joined:true},{a:2,joined:true}]})
WriteResult({ "nInserted" : 1 })
MongoDB Enterprise mongos> db.foo.insert({_id:2,members:[{a:1,joined:true},{a:2,joined:false}]})
WriteResult({ "nInserted" : 1 })
MongoDB Enterprise mongos> db.foo.find({$and:[{members:{$elemMatch:{a:1,joined:true}}},{members:{$elemMatch:{a:2,joined:true}}}]})
{ "_id" : 1, "members" : [ { "a" : 1, "joined" : true }, { "a" : 2, "joined" : true } ] }

Upvotes: 1

varman
varman

Reputation: 8894

You can achieve this using aggregation.

db.collection.aggregate([{
    
    $addFields:{
        members:{
            $filter:{
                input:"$members",
                cond:{
                    $or:[
                        {
                            $and:[
                                {$eq:["$$this.user",ObjectId("5f49d0623c7e81bfcf37b8ed")]},
                                {$eq:["$$this.joined",true]}
                                ]
                        }
                        ,
                        {
                            $and:[
                                {$eq:["$$this.user",ObjectId("5f49d075d14cb1f5e2ff6a77")]},
                                {$eq:["$$this.joined",true]}
                                ]
                        }
                        ]
                }
            }
        }
    }
}])

Working Mongo playground

Upvotes: 1

Related Questions