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