$lookup on array of objects

Working with Data
,
1

I’m pretty new to mongo and this is causing me quite some confusion. I’ve seen other posts describing how to do this, but it doesn’t seem to work in my case. I’d appreciate any guidance on my data model as well, it maybe that it isn’t appropriate.

player collection

{
  "id": 12,
  ...
}

events collection

{
  "id" : ...
  "matchResults" : [ 
     {
         "player1": 12,
         "player1": 13,
         ....
     }, 
     {
         "player1": 14,
         "player1": 12,
         ...
     },
     ....
  ]
}

Basically an event will have many matchResults, which may or may not be related to a specific player, and the player could appear in field player1 or player2.

I’d like an aggregation that returns the player with only their matches from all event documents.
From what I can see some kind of aggregation pipeline using $lookup and $in is the way to go, but I couldn’t get compass to go beyond complaining about $in requires an array as a second argument, found: missing

2

This is as far as I got with the aggregation. I just focused on matching to player1 initially, but that still doesn’t work.

{
  from: 'event',
  let: {'id':"$matchResults.player1"},
  pipeline: [{
    $match : {
      $expr : {
        $in : ['$id', "$$id"]
      }
    }  
  }],
  as: 'matches'
}
3

Hi @jimmyb ,

You can do it like this:

  • $match with $or - to find all documents that has at least one event where requested player was either player1 or player2.

  • $filter - to filter out only events where the requested player was either player1 or player2.

db.collection.aggregate([
  {
    "$match": {
      "$or": [
        {
          "matchResults.player1": 1
        },
        {
          "matchResults.player2": 1
        }
      ]
    }
  },
  {
    "$project": {
      "matchResults": {
        "$filter": {
          "input": "$matchResults",
          "cond": {
            "$or": [
              {
                "$eq": [
                  "$$this.player1",
                  1
                ]
              },
              {
                "$eq": [
                  "$$this.player2",
                  1
                ]
              }
            ]
          }
        }
      }
    }
  }
])

Working Example

1 Like
4

Thanks. I found a solution before the reply, but it ended up being fairly similar.