时间戳

首页 > 解决方案 > MongoDB：两个集合之间的多个 $lookup

mongodb - MongoDB：两个集合之间的多个 $lookup

问题描述

我有两个系列。玩家：

    [{
  "_id": {
    "$oid": "6009cf73445ff54178c9f123"
  },
  "Nb_Player": 10,
  "Name_Player": "Roddick",
  "Nationality": "United States",
  "Score": 7,
  "Name_Tournament": "Roland Garros",
  "Gender": "Male"
},{
  "_id": {
    "$oid": "6009cf73445ff54178c9f124"
  },
  "Nb_Player": 10,
  "Name_Player": "Roddick",
  "Nationality": "United States",
  "Score": 8,
  "Name_Tournament": "Australia Open",
  "Gender": "Male"
},{
  "_id": {
    "$oid": "6009cf73445ff54178c9f125"
  },
  "Nb_Player": 10,
  "Name_Player": "Roddick",
  "Nationality": "United States",
  "Score": 8,
  "Name_Tournament": "Flusshing Meadows",
  "Gender": "Male"
},{
  "_id": {
    "$oid": "6009cf73445ff54178c9f126"
  },
  "Nb_Player": 10,
  "Name_Player": "Roddick",
  "Nationality": "United States",
  "Score": 0,
  "Name_Tournament": "Paris-Bercy Open",
  "Gender": "Male"
},{
  "_id": {
    "$oid": "6009cf73445ff54178c9f127"
  },
  "Nb_Player": 20,
  "Name_Player": "Ginepri",
  "Nationality": "United States",
  "Score": 8,
  "Name_Tournament": "Roland Garros",
  "Gender": "Male"
},{
  "_id": {
    "$oid": "6009cf73445ff54178c9f128"
  },
  "Nb_Player": 30,
  "Name_Player": "Gasquet",
  "Nationality": "France",
  "Score": 1,
  "Name_Tournament": "Australia Open",
  "Gender": "Male"
},{
  "_id": {
    "$oid": "6009cf73445ff54178c9f129"
  },
  "Nb_Player": 40,
  "Name_Player": "Montfils",
  "Nationality": "France",
  "Score": 3,
  "Name_Tournament": "Australia Open",
  "Gender": "Male"
},{
  "_id": {
    "$oid": "6009cf73445ff54178c9f12a"
  },
  "Nb_Player": 100,
  "Name_Player": "Mauresmo",
  "Nationality": "France",
  "Score": null,
  "Name_Tournament": null,
  "Gender": "Female"
},{
  "_id": {
    "$oid": "6009cf73445ff54178c9f12b"
  },
  "Nb_Player": 200,
  "Name_Player": "Davenport",
  "Nationality": "United States",
  "Score": 4,
  "Name_Tournament": "Roland Garros",
  "Gender": "Female"
}]

和团队：

[{
  "_id": {
    "$oid": "6020251048a99f26045bb879"
  },
  "Nb_Team": 1,
  "Name_Player_1": "Roddick",
  "Name_Player_2": "Montfils",
  "Score": 9,
  "Name_Tournament": "Roland Garros"
},{
  "_id": {
    "$oid": "6020251048a99f26045bb87a"
  },
  "Nb_Team": 2,
  "Name_Player_1": "Ginepri",
  "Name_Player_2": "Gasquet",
  "Score": 7,
  "Name_Tournament": "Roland Garros"
},{
  "_id": {
    "$oid": "6020251048a99f26045bb87b"
  },
  "Nb_Team": 1,
  "Name_Player_1": "Roddick",
  "Name_Player_2": "Montfils",
  "Score": 7,
  "Name_Tournament": "Flusshing Meadows"
},{
  "_id": {
    "$oid": "6020251048a99f26045bb87c"
  },
  "Nb_Team": 2,
  "Name_Player_1": "Ginepri",
  "Name_Player_2": "Gasquet",
  "Score": 8,
  "Name_Tournament": "Flusshing Meadows"
}]

作为输出，我希望法国国籍的球员 (Name_Player) 在 Teams 以及他们的 Nb_Team 中比赛。像这样的东西：

{ "_id" : { "Player" : "Montfils", "Nationality" : "France", "Nb_Team": 1 } }
{ "_id" : { "Player" : "Gasquet", "Nationality" : "France", "Nb_Team": 2 } }

我尝试了 3 $lookup：

db.Player.aggregate([
    {
        "$lookup": {
            "from": "Teams",
            "localField": "Name_Player",
            "foreignField": "Name_Player_1",
            "as": "FrenchPlayerTeam1"
        }
    },
    {
        "$lookup": {
            "from": "Teams",
            "localField": "Name_Player",
            "foreignField": "Name_Player_2",
            "as": "FrenchPlayerTeam2"
        }
    },
    {
        "$lookup": {
            "from": "Teams",
            "localField": "Name_Tournament",
            "foreignField": "Name_Tournament",
            "as": "Name_Tournament"
        }
    },
      { "$match": {"Nationality": "France", "Score": {"$exists": true,"$ne": null}, "Name_Tournament": {"$exists": true,"$ne": null} }},
  { "$group": {
    "_id": {
    "Player":"$Name_Player",
    "Nationality": "$Nationality",
    "Nb_Team": "$Nb_Team"},
  }}    
  ]);

结果是：

[ { _id: { Player: 'Gasquet', Nationality: 'France' } },
  { _id: { Player: 'Montfils', Nationality: 'France' } } ]

如果我在第二次 $lookup 之后尝试使用 $unwind，我根本不会得到任何结果。有没有办法得到我想要的结果？谢谢

标签： mongodbmongodb-querylookup

解决方案

不确定我是否正确理解了您的要求，但从您的示例数据看来，两个玩家都将拥有Nb_Team:2.

不过，这里有一个Mongo Playground的查询。

db.Player.aggregate([
  {
    "$match": {
      "Nationality": "France",
      "Score": {
        "$exists": true,
        "$ne": null
      }
    }
  },
  {
    "$lookup": {
      "from": "Teams",
      "let": {
        name: "$Name_Player"
      },
      "pipeline": [
        {
          $match: {
            $expr: {
              $or: [
                {
                  $eq: [
                    "$Name_Player_1",
                    "$$name"
                  ]
                },
                {
                  $eq: [
                    "$Name_Player_2",
                    "$$name"
                  ]
                }
              ]
            }
          }
        },
        {
          "$count": "cnt"
        }
      ],
      "as": "Nb_Team"
    }
  },
  {
    "$unwind": "$Nb_Team"
  },
  {
    $project: {
      "_id": {
        "Player": "$Name_Player",
        "Nationality": "$Nationality",
        "Nb_Team": "$Nb_Team.cnt"
      }
    }
  }
])

这个想法是使用$lookup 子管道来操纵$lookup结果。您可以轻松地修改$lookup子管道中的查询并$project使其适合您的需求。

推荐阅读