Perform a conditional lookup, only when the localfield exists?

Question

I have a collection, which contains two types of documents. Based on the type of document I want to perform lookups aggregation.

Action is a collection, which contains both conversations and message type of actions. I want to perform a lookup for conversation and messages differently based on the fields present in MongoDB.

The action document for conversation type is as follows

{ 
    "_id" : ObjectId("592bdeaf45c7201421793871"), 
    "actionType" : "conversation", 
    "created" : NumberInt(1496047280), 
    "users" : [
        ObjectId("590c53a85fba594a59fe3d0f"), 
        ObjectId("590c50175df715499129e41b")
    ], 
    "conversationId" : ObjectId("592bdeaf45c7201421793870"), 
    "missionId" : ObjectId("590c50fa5df715499129e41c")
}

Action document for message

{ 
    "_id" : ObjectId("592bdeaf45c7201421793871"), 
    "actionType" : "message", 
    "created" : NumberInt(1496047280), 
    "messageId" : ObjectId("592bdeaf45c7201421793870")
}

I want actions after a particular time and want to perform lookups to get the conversation and message data from their object Ids.

I tried this

let matchQuery = {
          users : new ObjectId(userId),
          created:
            {
              $gt : createdTime
            }};

       let aggregation = [
          {
            $match : matchQuery
          },
          {$cond : 
            {if:
              {
                $users:{
                  $exists:true
                }
            }, 
          then: {
            $unwind : "$users",
            $lookup: {
                 from : "users",
                 localfield:"$users",
                 foreignfield:"_id",
                 as:"userDetail"
                       },
            $group:{
                  "users" : { $push : "userDetail"}
                    }
                  }
              }
            },
            {$cond : 
            {if:
              {
                $conversationId:{
                  $exists:true
                }
            }, 
          then: {
           $lookup : {
                            from:"conversations",
                            localfield:"conversationId",
                            foreignfield:"_id",
                            as:"conversationDetail"
                          }
                  }
              }
            },
            {$cond : 
            {if:
              {
                $missionId:{
                  $exists:true
                }
            }, 
          then: {
           $lookup : {
                            from:"services_v2",
                            localfield:"missionId",
                            foreignfield:"_id",
                            as:"missionDetail"
                          }
                  }
              }
            },
            {$cond : 
            {if:
              {
                $messageId:{
                  $exists:true
                }
            }, 
          then: {
           $lookup : {
                            from:"messagev2",
                              localfield:"messageId",
                              foreignfield:"_id",
                              as:"messageDetail"
                          }
                  }
              }
            },
          {
            $project : {
              "_id" : 1,
              "actionType" : 1,
              "userDetail":1,
              "conversationDetail":1,
              "missionDetail":1,
              "messageDetail":1
}}
        ];

connection.collection('actions')
.aggregate(aggregation).toArray((err,result)=> {  
    if(err){
      console.log(err);
    }
    console.log(result);
 })
};

Answer 1

I think you are overthinking this a little bit and you don't need a "conditional lookup", and it's probably best to demonstrate by example.

Take these documents in separate collections, first the conversation:

> db.conversation.find()
{ "_id" : ObjectId("592ccbf8fceb6b40e6489759"), "message" : "I'm here" }

Then the message collection:

> db.message.find()
{ "_id" : ObjectId("592ccc0bfceb6b40e648975a"), "text" : "Something here" }

And then I have a master collection that has references to each of those documents in separate documents:

> db.master.find()
{ 
  "_id" : ObjectId("592ccc73fceb6b40e648975b"),
  "a" : 1, 
  "conversation" : ObjectId("592ccbf8fceb6b40e6489759")
}
{ 
  "_id" : ObjectId("592ccc95fceb6b40e648975c"),
  "a" : 2,
  "message" : ObjectId("592ccc0bfceb6b40e648975a")
}

Now if I do a $lookup operation ( which is somewhat analogous to a "left join" ):

db.master.aggregate([
  { "$lookup": {
    "from": "conversation",
    "localField": "conversation",
    "foreignField": "_id",
    "as": "conversation"
  }}
])

Then I get a result like this, which of course projects an empty array on the document that did not have a "localField" to match:

{
    "_id" : ObjectId("592ccc73fceb6b40e648975b"),
    "a" : 1,
    "conversation" : [
        {
            "_id" : ObjectId("592ccbf8fceb6b40e6489759"),
            "message" : "I'm here"
        }
    ]
}
{
    "_id" : ObjectId("592ccc95fceb6b40e648975c"),
    "a" : 2,
    "message" : ObjectId("592ccc0bfceb6b40e648975a"),
    "conversation" : [ ]
}

If I now add to the pipeline a "second" $lookup operation to link to the message collection:

db.master.aggregate([
  { "$lookup": {
    "from": "conversation",
    "localField": "conversation",
    "foreignField": "_id",
    "as": "conversation"
  }},
  { "$lookup": {
    "from": "message",
    "localField": "message",
    "foreignField": "_id",
    "as": "message"
  }}
])

Then we see the similar effect where the document that does not have the property now has an empty array, but where the property did exist we now have the entry from the specific collection:

{
    "_id" : ObjectId("592ccc73fceb6b40e648975b"),
    "a" : 1,
    "conversation" : [
        {
            "_id" : ObjectId("592ccbf8fceb6b40e6489759"),
            "message" : "I'm here"
        }
    ],
    "message" : [ ]
}
{
    "_id" : ObjectId("592ccc95fceb6b40e648975c"),
    "a" : 2,
    "message" : [
        {
            "_id" : ObjectId("592ccc0bfceb6b40e648975a"),
            "text" : "Something here"
        }
    ],
    "conversation" : [ ]
}

You can either leave this as it is ( which seems to be the desired final state of what you tried so far ) or now do other operations with it, such as combining into a single array:

db.master.aggregate([
  { "$lookup": {
    "from": "conversation",
    "localField": "conversation",
    "foreignField": "_id",
    "as": "conversation"
  }},
  { "$lookup": {
    "from": "message",
    "localField": "message",
    "foreignField": "_id",
    "as": "message"
  }},
  { "$project": {
    "a": 1,
    "combined": {
      "$concatArrays": [
        { "$map": {
          "input": "$conversation",
          "as": "el",
          "in": {
            "type": "conversation",
            "_id": "$$el._id",
            "message": "$$el.message"
          }
        }},
        { "$map": {
          "input": "$message",
          "as": "el",
          "in": {
            "type": "message",
            "_id": "$$el._id",
            "text": "$$el.text"
          }
        }}
      ]
    }
  }}
])

Which outputs as:

{
    "_id" : ObjectId("592ccc73fceb6b40e648975b"),
    "a" : 1,
    "combined" : [
        {
            "type" : "conversation",
            "_id" : ObjectId("592ccbf8fceb6b40e6489759"),
            "message" : "I'm here"
        }
    ]
}
{
    "_id" : ObjectId("592ccc95fceb6b40e648975c"),
    "a" : 2,
    "combined" : [
        {
            "type" : "message",
            "_id" : ObjectId("592ccc0bfceb6b40e648975a"),
            "text" : "Something here"
        }
    ]
}

The point being that $lookup will quite intentionally simply leave behind an "empty arrray" if either the "localField" or "foreignField" expressions do not match any elements. This does not affect the document results returned other than adding the empty array property for the target.

Now you can "loose" documents using $unwind, but that will also only happen if you omit the "preserveNullAndEmptyArrays" option, which is there to deal with such occurrences.

But for general usage of a "discriminator" based $lookup, then simply use separate pipeline stage for each collection you want to "link" to.