$lookup with conditional localField

Working with Data
, , ,
1

Hi,

I want to perform a $lookup, where the localField depends on the foreignField beign of type objectId or a string.
Example:

const obj = {
    _id: ObjectId('3432sdscscsc'),
    b: 'string'
};

if parameter b is a string, the localField must be x; had been a objectId type, the localField whould be y.

Someone knows how to handle the matter?
Thanks
km

2

Please provide sample documents of both the source and looked up collections that represents the 2 situations.

But, an aggregation pipeline is simply an array of stages. Nothing stops you from using normal control flow statement of the programming language you use.

For example:

pipeline = [ { /* any stage */ } ]
lookup = {}
if ( obj.b == 'string' )
{
  lookup =
  { '$lookup' :
    { 'from' : YourCollectionName ,
      'localField' : x ,
      'foreignField' : ... ,
      'as' : ...
    }
  }
}
else if ( obj.b === 'objectId' )
{
 lookup =
 { '$lookup' :
   { 'from' : YourCollectionName ,
     'localField' : y ,
     'foreignField' : ... ,
      'as' : ...
    }
  }
}
pipeline.push( lookup ) ;
3

Hi Steevej,

well, I should try this. If it works it is it.

My problem is that I wanted to allow the user to upload data, for which some references were not passed along. Like: an invoice with a supplier, but without providing information from this supplier apart from the mere string of the name.
But now I thing I should just create an id for this supplier and simply not allow in the app entities without id.

I don’t know if that’s the solution. (I do thing so, although I also thought the other way was the solution). But I noticed that managing the inherent complexity of working with both id and string is just huge and not worth it.

Regarding your solution, I think I didn’t explained it well, but the obj.b, in my case, is not present in my app memory. obj is the mongo collection I’m looking it up. The “YourCollectionName”. The ideawas to provide a localField depending on one field in the retrieved object.

4

That is why it is best to publish real documents from your collections. When I see

const obj = {
    _id: ObjectId('3432sdscscsc'),
    b: 'string'
};

I see a constant object defined in JS code. Not a document in a collection. For this my solution is inadequate unless you want to pay the performance penalty of 2 database access.

You will not be able to use the localField/foreingField version. You will need to use the version at https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/#join-conditions-and-subqueries-on-a-joined-collection.

Also see Conditional $lookup

5

Very true Steevej,
I over supposed. I looked those links you send and I’m wondering if that could be the solution for another issue I have.
Could you check this?

I think It can’t because that solution has to do with $lookup and I would need to perform with a bulk operation.

6

I saw your other question and I have no solution.

1 Like
7

(this may help others to get answer)
here is one solution to One2One mapping using look up

db.history.aggregate()
    .project({ "year": { "$year": "$created_date" }, user_id: 1 })
    .match({ year: 2021, "user_id": 0 })
    .group({ _id: "$user_id", count: { $sum: 1 } })
    .lookup({
        "from": "lookup_c",
        "let": { "id": "$_id" },
        "pipeline": [
            { "$project": { "_id": 1, "user_name": 1 } },
            { "$match": { "$expr": { "$and": [{ "$eq": ["$_id", "$$id"] }] } } }
        ],
        "as": "p"
    })
    .unwind("$p")
    .project({ _id: 1, count: 1, username: "$p.user_name" })
    .sort({ count: -1 });
1 Like