I need to join entities from the same table 'entities' together. The local key is the _id of the local entity. The foreign key is within the 'rels' array of objects (the _id is stored in rels.r).

The lookup works fine when I test $match with an example _id string. But when I try to use the variable declared with 'let' instead (which should use that same string, just in a variable) it doesn't work.

Example data:

{ // PARENT ENTITY
  "_id":"123", // the local key
  "title":"initialentity",
},
{ // CHILD ENTITY 
  "_id":"456",
  "title":"relatedentity",
  "rels":[
    {
      "r":"123", // the foreign key
      "a":"exampledata-ignorethisfield",
    },
  ]
}

Aggregation:

[
  { "$lookup": {
    "from": "entities",
    "let": { "id": { $toString: "$_id" }  },
    // "let": { "id": "$_id"  }, // this alternative let doesn't work either
    "pipeline": [
      //{ "$match": { "rels.r": "123" } }, // This test works fine
      { "$match": { "rels.r": "$$id" } }, // But this, using the let variable, doesn't work
      {"$limit": 1}
    ],
    "as": "result"
  }},
  {"$limit": 1}
]

Actual Output (results array is empty):

{ 
  "_id":"123", // the local key
  "title":"initialentity",
  "results": []
},

Expected Output (results array has the related object):

{ 
  "_id":"123", // the local key
  "title":"initialentity",
  "results": [
    {
      "_id":"456",
      "title":"relatedentity",
      "rels":[
        {
          "r":"123", // the foreign key
          "a":"exampledata-ignorethisfield",
        },
      ]
    }
  ]
},

I need it to be using this method (with pipeline) due to other conditions that will be added in.

Thanks in advance for your help. I'm sure I'm missing something obvious. I've trawled through many SO qs & mongo docs and just can't figure this one out. I've also tried things like the following without success:

{ "$match": { $expr: { "rels": {"r" : "$$id"} } } }, // This doesn't work (returns unrelated entities)
{ "$match": { $expr: { $eq: [ "rels.r" , "$$id" ] } } }, // This doesn't work (returns empty array)

CodePudding user response：

You need to use $expr as you've tried in order to access the variable $$id, but you also need to prefix rels.r with a $ so that it compares the value at rels.r to $$id and not the literal string "rels.r".

[
  { "$lookup": {
    "from": "entities",
    "let": { "id": { $toString: "$_id" }  },
    "pipeline": [
      { "$match": { $expr: { $eq: [ "$rels.r" , "$$id" ] } } },
      { "$limit": 1 }
    ],
    "as": "result"
  }},
  { "$limit": 1 }
]

Edit

$eq doesn't appear to work if you're matching against an array of objects, I've been able to get it to work using $in:

[
  { "$lookup": {
    "from": "entities",
    "let": { "id": { $toString: "$_id" }  },
    "pipeline": [
      { "$match": { $expr: { $in: [ "$$id" , { $ifNull: [ "$rels.r", [] ] } ] } } },
      { "$limit": 1 }
    ],
    "as": "result"
  }},
  { "$limit": 1 }
]

I've chosen to include $ifNull because $in will fail if the second argument doesn't resolve to an array, which would happen if a document doesn't have a rels field. If every document does have a rels field, feel free to omit this in favor of just "$rels.r".

Working MongoPlayground