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

Question

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
      }
    ]
  }
})

D. SM · Accepted Answer · 2020-08-29 04:02:10Z

1

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 } ] }

answered Aug 29, 2020 at 4:02

D. SM

14.6k3 gold badges15 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

varman · Accepted Answer · 2020-08-29 03:52:08Z

1

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

answered Aug 29, 2020 at 3:52

varman

8,9326 gold badges25 silver badges56 bronze badges

1 Comment

strblr Over a year ago

Yep, that's what I wanted to avoid. Any alternative without aggregation ?

raga · Accepted Answer · 2020-08-29 04:12:34Z

1

You can do:

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

Playground

answered Aug 29, 2020 at 4:12

raga

95710 silver badges14 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related