I’m trying to make a lookup, where the foreignField is dynamic:

{
    $merge: {
        _id: ObjectId('61e56339b528bf009feca149')
    }
},
{
    $lookup: {
        from: 'computer',
        localField: '_id',
        foreignField: 'configs.?.refId',
        as: 'computers'
    }
}

I know that the foreignField always starts with configs and ends with refId, but the string between the two is dynamic.

Here is an example of what a document looks like:

'_id': ObjectId('6319bd1540b41d1a35717a16'),
'name': 'MyComputer',
'configs': {
    'ybe': {
        'refId': ObjectId('61e56339b528bf009feca149')
        'name': 'Ybe Config'
    },
    'test': {
        'refId': ObjectId('61f3d7ec47805d1443f14540')
        'name': 'TestConfig'
    },
    ...
}

As you can see the configs property contains different objects with different names (‘ybe’, ‘test’, etc…). I want to lookup based on the refId inside of all of those objects.

How do I achieve that?

>Solution :

Using dynamic value as a field name is considered an anti-pattern and introduces unnecessary complexity to querying. However, you can achieve your behaviour with $objectToArray by converting the object into array of k-v pairs and perform the $match in a sub-pipeline.

db.coll.aggregate([
  {
    "$lookup": {
      "from": "computer",
      "let": {
        id: "$_id"
      },
      "pipeline": [
        {
          $set: {
            configs: {
              "$objectToArray": "$configs"
            }
          }
        },
        {
          "$unwind": "$configs"
        },
        {
          $match: {
            $expr: {
              $eq: [
                "$$id",
                "$configs.v.refId"
              ]
            }
          }
        }
      ],
      "as": "computers"
    }
  }
])

MongoPlayground