join equivalent of two MongoDB collections

Question

I have two collections, "Messages" & "ConversationMappings". Messages is a collection of individual email messages. ConversationMappings is a collection of Messages that are part of one conversation thread.

I have a LOT of orphaned "ConversationMappings" that I am trying to remove, so I'm trying to find and remove the ConversationMappings that do not have Messages.

I have 999 Messages and 20,000+ ConversationMappings and I have to delete all the ConversationMappings that do not have Messages in the collection of 999 Messages. This would be a simple join in relational....but I don't know how to do it in MongoDB

Collection Schema

**Message**
({
    "created_at": ISODate("2016-10-05T14:04:31.690-07:00"),
    "account_id": "579f7b64144a99xxxxxx81d94db",
    "from": {
        "name": "Joe Emailer",
        "email": "[email protected]"
    },

    "message": "Text of the message",
    "timestamp": ISODate("2015-06-16T12:40:55.322-07:00"),
    "to": {
        "name": "Jane Emailer",
        "email": "[email protected]"
    },
    "updated_at": ISODate("2016-10-05T14:04:31.690-07:00")
})

**ConversationMapping**
 ({
    "archived": false,
    "messages": [
        "5xxxxxxxxxxxxx81d94db",
        "5xxxxxxxxxxxxx81d94dc",
        "5xxxxxxxxxxxxx81d94dd",
        "5xxxxxxxxxxxxx81d94de"
    ],
    "account_id": "579f7b64144a99xxxxxx81d94db",
    "participants": [
        "[email protected]",
        "[email protected]"
    ],
    "timestamp": ISODate("2014-07-24T17:00:00.000-07:00")
})

Answer 1

db.message.aggregate([
   {
      $lookup:
         {
            from: "conversationMapping",
            localField: "account_id",
            foreignField: "account_id",
            as: "orphaned_docs"
        }
   },
   {
      $match: { "orphaned_docs": { $ne: [] } }
   }
])

The above query will give you all the non-orphaned conversationMappings. once you have all valid converstionMappings you can dump them to a new collection and get rid of old.

however

db.message.aggregate([
   {
      $lookup:
         {
            from: "conversationMapping",
            localField: "account_id",
            foreignField: "account_id",
            as: "orphaned_docs"
        }
   },
   {
      $match: { "orphaned_docs": { $eq: [] } }
   }
])

this should return all the orphaned conversationMappings but somehow for your data it seems not working. May be i used wrong way of replicating it but this query will do the trick for you.

explanation : $lookup helps you 'kind of joining' two collections based on specified field. You are welcome to play around using it.