Get documents only if all elements inside an array match the condition in MongoDb

user_3222156 0
Viewed 681

I am trying to delete all orphan projects. In db I need to get the projects where all of its users are deleted. Already tried with below query but it's always giving the results where the delete field is absent.

db.projects.find({
  "user_list": {
     $not: {
       $elemMatch: {
         "deleted": false
       }
   }
 },
 "user_list.deleted": {$exists: true}
}, {"user_list": 1})

Need help in writing a query to fetch the only projects with all user deleted. E.g in below example I should only get the second document.

List of projects

/* 1 */
{
    "_id" : ObjectId("636a6aa584d5f92f14f0c548"),
    "user_list" : [ 
        {
            "deleted" : false,
            "user_id" : "602cf72a3fcad3cc605b8d59"
        },
        {
            "deleted" : true,
            "user_id" : "602cf72a3fcad3cc605b8d50"
        }
    ]
}

/* 2 */
{
    "_id" : ObjectId("602e443bacdd4184511d6e29"),
    "user_list" : [ 
        {
            "deleted" : true,
            "user_id" : "602cf72a3fcad3cc605b8d59"
        }, 
        {
            "deleted" : true,
            "user_id" : "602cf72a3fcad3cc605b8d59"
        }, 
        {
            "deleted" : true,
            "user_id" : "602cf72a3fcad3cc605b8d59"
        }
    ]
}

/* 3 */
{
    "_id" : ObjectId("60332242acdd4184511ed664"),
    "user_list" : [ 
        {
            "deleted" : true,
            "user_id" : "602cf72a3fcad3cc605b8d59",
        }, 
        {
            "deleted" : true,
            "user_id" : "602cf72a3fcad3cc605b8d59"
        }, 
        {
            "user_id" : "602cf72a3fcad3cc605b8d59"
        }
    ]
}
mongodb-query mongodb
3 Answers

You can use $nin to filter out false and null values.

db.projects.find({
  "user_list.deleted": {
    $nin: [
      false,
      null
    ]
  }
})

MongoDB Playground

user_37247160 
db.projects.find(
   { "user_list.deleted": { $ne: false } }
)

should give you all the projects which has no record of deleted === false.

you can $and it with $ne: null for null set to deleted to avoid them too.

Updates: From https://docs.mongodb.com/manual/reference/operator/query/and/index.html I could imagine following to work, but is not valid when I run it on my local machine. This could be because of the nesting.

{ user_list.deleted: { $ne: false, $exists: true } }

But this one works,

 { user_list.deleted: { $ne: false, $ne: null } }
user_5654820 
db.projects.find({ $and: [ 
              { "user_list" : { $in : ["deleted"] } },   
              { "user_list" : { $elemMatch : { "deleted" : true} } },   
              { "user_list" : { $elemMatch : { "deleted" :{$ne:false}} } } ]});

Try with this query. I think it should work in all mentioned cases.

user_121804530
Related