How to do a conditional $lookup in MongoDB-CodePudding

I have three collections: A,B,C.

A has a property named a, which is a ref id in B or C.

What I want to do is, look up in B first, if nothing found, then look up in C.

Something like this:

[
    {
        $lookup: 
        {
            from: 'B',
            localField: 'a',
            foreignField: '_id',
            as: 'temp'
        }
    },
    { 
        $unwind: 
        {
            path: '$temp', 
            preserveNullAndEmptyArrays: true 
        }
    },
    {
        $lookup: 
        {
            if: 'temp not exist',      <! just demonstrate !>
            from: 'C',
            localField: 'a',
            foreignField: '_id',
            as: 'temp'
        }
    },
]

I don't know how to implement this. Can someone help? Thanks.

CodePudding user response：

I don't think we can do conditional pipeline stages(we can do conditional operators but not stages), based on information we have while the query is running.

*if we had the information while writting the query we could generate the query dynamically and include a stage or not

But you can use $lookup with a pipeline, and add this filter, like if temp is empty array (meaning no join happened), to join only then.

I dont see a reason to do the unwind in the middle, make the 2 lookups and do it after.

Query

do the first lookup
second lookup is pipeline with filter that requires first lookup temp array to be empty
(if you keep the unwind in the middle use this {"$eq": [{"$type": "$temp"}, "missing"]} instead of the empty check)
keeps as temp, the temp or tempC depending who is not empty
unset the tempC
does the unwind now (join before unwind is faster i think, its less documents), also you want first to find the join members and then unwind

aggregate(
[ {"$lookup": 
    {"from": "B", "localField": "a", "foreignField": "_id", "as": "temp"}},
  {"$lookup": 
    {"from": "C",
      "let": {"a": "$a", "temp": "$temp"},
      "pipeline": 
      [{"$match": 
          {"$expr": 
            {"$and": [{"$eq": ["$temp", []]}, {"$eq": ["$a", "$_id"]}]}}}],
      "as": "tempC"}},
  {"$set": {"temp": {"$cond": [{"$eq": ["$temp", []]}, "$tempC", "$temp"]}}},
  {"$unset": ["tempC"]},
  {"$unwind": {"path": "$temp", "preserveNullAndEmptyArrays": true}}])