MongoDB aggregate from adjustable foreign collections

Question

My documents in the orders collection has _client key, which is an ObjectId references to another entity in another collection. The collection could be organization and could be users - I mean - it's variable collection. I want to tell Mongo to lookup if the _client id is found in both collections.

{
    $lookup: {
      from: "users", // could be "organizations" 
      let: { "client": "$_client" }, // could be "_organization"
      pipeline: [
        { $match: { $expr: { $eq: ["$_id", "$$client"] }}},
      ],
      as: "client"
    }
  },
  {
    $unwind: "$client"
  },

I have tried to just set up two look ups, once for _client and one for _organization however when there one of them is missing, I just got no results at all.

score 1 · Accepted Answer · answered Feb 10 '19 at 11:25

$unwind filtered out the documents where arrays are empty and do not contain any element.

So, You have to use preserveNullAndEmptyArrays and set it to true

{ "$unwind": { "path": "$client", "preserveNullAndEmptyArrays": true }}

and same for the or organizations

{ "$unwind": { "path": "$organization", "preserveNullAndEmptyArrays": true }}

MongoDB aggregate from adjustable foreign collections

1 Answers1