Composition in mongo query (SQL sub query equivalent)

Question

I have a couple of collections, for example;

members
   id
   name
   //other fields we don't care about
emails
   memberid
   //other fields we don't care about

I want to delete the email for a given member. In SQL I could use a nested query, something like;

delete emails
where memberid in (select id from members where name = "evanmcdonnal")

In mongo I'm trying something like this;

db.emails.remove( {"memberid":db.members.find( {"name":"evanmcdonnal"}, {id:1, _id:0} ) )

But it returns no results. So I took the nested query and ran it on it's own. The issue I believe is that it returns;

{
    "id":"myMadeUpId"
}

Which - assuming inner queries execute first - gives me a query of;

db.emails.remove( {"memberid":{ "id""myMadeUpId"} )

When really I just want the value of id. I've tried using dictionary and dot notation to access the value of id with no luck. Is there a way to do this that is similar to my attempted query above?

Answer 1

As forEach() way didn't work for me i solved this using:

var plzDeleteIds = db.members.find({ "name" : "evanmcdonnal" }, { "id" : 1 }).toArray();
var aux = plzDeleteIds["0"];
var aux2 = aux.map(function(u) { return u.name; } );
db.emails.remove({ "memberid" : { "$in" : aux2 } });

i hope it help!

Answer 2

Let's see how you'd roughly translate

delete emails where memberid in (select id from members where name = "evanmcdonnal")

into a set of mongo shell operations. You can use:

db.members.find({ "name" : "evanmcdonnal" }, { "id" : 1 }).forEach(function(doc) {
    db.emails.remove({ "memberid" : doc.id });
});

However, this does one remove query for each result document from members. You could push the members result ids into an array and use $in:

var plzDeleteIds = db.members.find({ "name" : "evanmcdonnal" }, { "id" : 1 }).toArray();
db.emails.remove({ "memberid" : { "$in" : plzDeleteIds } });

but that could be a problem if plzDeleteIds gets very, very large. You could batch. In all cases we need to do multiple requests to the database because we are querying multiple collections, which always requires multiple operations in MongoDB (as of 2.6, anyway).

The more idiomatic way to do this type of thing in MongoDB is to store the member information you need in the email collection on the email documents, possibly as a subdocument. I couldn't say exactly if and how you should do this since you've given only a bit of your data model that has, apparently, been idealized.