Mongodb : select all the questions which has no user record and delete it

user_4219542 0
Viewed 239

Development server:

I have two tables users and questions, user_id is stored in each question.

Unintentionally I have deleted some users, now I want delete questions for which the user doesn't exist,

delete from questions where q.id not in( select q.id from questions q inner join users u on u.id = q.user_id);

I think the above query does that in mysql but I want to do that in mongodb.

I am new to mongodb I know lookup aggregate function does the join but I don't how to do the above query.

mongodb
2 Answers

A $lookup is indeed needed for this, but you need another pipeline stage, which is $match to get only those documents where the userId count is 0 (we do not want to have documents in our result set where the length of userId is > 0 as this means that the user_id exists in users).

With the result set from the aggregation, you can do a simple iteration and remove all docs which remain in the set. Something like this should do it (since I cannot test it right now, maybe give it a quick test run though):

db.getCollection('questions').aggregate([{
            "$lookup": {    
                "from": "users",
                "localField": "user_id",
                "foreignField": "user_id",
                "as": "userId"    
            }
        }, {
            "$match": {
                "userId": {
                    "$size": 0
                }
            }
        },

    ]).forEach((doc) => {
        db.getCollection("questions").remove({ "_id": doc._id });
    });

Instead of the forEach in the last part, you also get all the ids and remove them in one single remove-query like Ravi did.

user_37616280

I believe you will have to use $lookup and aggregation pipeline to get all the questions where the user does not exist and then delete those questions.

Try this:

var pipeline = [{
        "$lookup": {    
            "from": "users",
            "localField": "user_id",
            "foreignField": "_id",
            "as": "user_id"    
        }
    }, {
        "$match": {
            "user_id": {
                "$size": 0
            }
        }
    }
]

var cursor = db.questions.aggregate(pipeline);
// create a map to get _id of all the question where user doesnt exist
var ids = cursor.map(function (doc) { return doc._id; });
// remove all those questions
db.questions.remove({"_id": { "$in": ids }});
user_50840000
Related