CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
kurtko
kurtko

Reputation: 2126

MongoDB lookup match two values

I have two collections, tennis matches with two players and players.

matches looks like this:

{ 
    "_id" : ObjectId("5ce51febc6dd820a820f20a5"), 
    "players" : [
        ObjectId("5ce51c1af3cd6009a171a5b3"), 
        ObjectId("5ce51c1af3cd6009a171a350")
    ], 
    "result" : "4:6 6:3 7:6(7) 7:6(8)"    
},
{ 
    "_id" : ObjectId("5ce51febc6dd820a820f20a6"), 
    "players" : [
        ObjectId("5ce51c1af3cd6009a171a005"), 
        ObjectId("5ce51c1af3cd6009a171a16c")
    ], 
    "result" : "6:2 4:6 6:3"    
},
[...]

and players like this:

{ 
    "_id" : ObjectId("5ce51c1af3cd6009a171a5b3"),               
    "name" : "Serena Williams",         
    "country" : "USA"         
},
{
    "_id" : ObjectId("5ce51c1af3cd6009a171a350"),               
    "name" : "Garbiñe Muguruza",         
    "country" : "Spain"         
},
[...]

I need all matches where players[0] is equal to a name and players[1] to another name.

I've tried this without success:

db.matches.aggregate([
  {
    $unwind: "$players"
  },
  {
    $lookup: {
      from: "players",
      localField: "players",
      foreignField: "_id",
      as: "tmp_join"
    }
  },
  {
    $match: {
      "tmp_join.name": ["Serena Williams","Garbiñe Muguruza"]
    }
  }
])

Upvotes: 1

Views: 142

Answers (1)

Ashh
Ashh

Reputation: 46441

You have to first $unwind the tmp_join array and then you can use $in to find the documents contain name.

db.matches.aggregate([
  { "$lookup": {
    "from": "players",
    "localField": "players",
    "foreignField": "_id",
    "as": "tmp_join"
  }},
  { "$unwind": "$tmp_join" },
  { "$match": {
    "tmp_join.name": {
      "$in": ["Serena Williams","Garbiñe Muguruza"]
    }
  }}
])

Use below aggregation if you are using mongodb 3.6 and above

db.matches.aggregate([
  { "$lookup": {
    "from": "players",
    "let": { "players": "$players" },
    "pipeline": [
      { "$match": {
        "$expr": { "$in": ["$_id", "$$players"] },
        "name": { "$in": ["Serena Williams", "Garbiñe Muguruza"] }
      }}
    ],
    "as": "tmp_join"
  }},
  { "$match": { "$expr": { "$gt": [{ "$size": "$tmp_join" }, 1] }}}
])

Upvotes: 1

Related Questions