Lookup Array In Foreign Field Collection

Hi,
What I’m attempting appears very similar to:

Where they have:
user collection:

{
  "uId": "user1"
}

I have a users collection:

{"_id":{"$oid":"62c2b5f0966b72973fe52314"},"active":true,"name":"Mary","age":{"$numberDouble":"28.0"},"gender":"Female","description":{"level":"C grade","comment":"Looking for good games with women only"},"ownerOf":["Anglia Ladies Ladder","Marys Buddies Ladder"],"memberOf":["Janes Buddies","Anglia C graders"]}

Instead of matchDetails collection, I have ‘rankings’ collection (playerId is the key field):

{"_id":{"$oid":"62c66dc612296752b7c82cde"},"active":true,"name":"Jons Buddies Ladder","owner":"Jon","number":{"$numberDouble":"2.0"},"base address":{"street":"99 George Street","city":"Super City"},"players":[{"playerId":"62c2b79d966b72973fe52316","challengerId":"62c2b79d966b72973fe52317","rank":{"$numberInt":"1"}},{"playerId":"62c2b79d966b72973fe52317","challengerId":"62c2b79d966b72973fe52316","rank":{"$numberInt":"2"}}],"owner_id":"62c2b79d966b72973fe52317","lastModified":{"$date":{"$numberLong":"1658371901842"}}}

My code attempt is therefore similar to the referenced article above:

db.users.aggregate([
 {$lookup: {
    "from": "rankings",
    "let": {"userId": "$_id"},
    pipeline: [
      {
        "$match": {
          "$expr": {
            "$in": ["$$userId", "$players.playerId"],  
          },
        },
      },
    ],
    as: "details"
  }}
])

The results give me a list of users (no matching has taken place) and an empty array:
"details": []
How can I populate the details array with the matched user names? thanks …
P.S. How to edit topics in this forum? I had to delete and re-write this topic to ‘edit’ it … thanks …

The example user I should have posted above was:
{"_id":{"$oid":"62c2b79d966b72973fe52316"},"active":true,"name":"Hary","age":{"$numberDouble":"29.0"},"gender":"Male","description":{"level":"B grade","comment":"Looking for good games with men only"},"ownerOf":["Anglia Mens Ladder","Harys Buddies Ladder"],"memberOf":["Bobs Buddies","Anglia B graders"]}
so that a match would be possible.

I also tried with _id.str to get the user id as a string:
"let": {"userId": "$_id.str"},
but I get the same result: "details": []

New to NoSQL dbs and hadn’t appreciated that I needed to change my id fields (e.g. playerId) to ObjectId and then “$_id” would simply match on the ObjectId (not a string, which most online examples were using but I want to use system generated ObjectIds, since there is no good reason for me to generate my own).

1 Like

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.