MongoDB聚合查询：lookup后的project操作

Question

我正在进行MongoDB聚合操作。我希望查找两个集合，然后在嵌套数组中仅投影所需字段。

需要查找的两个集合如下:

db.pitcher.find().pretty()

{
         "_id" : ObjectId("59b22eeef224252e6c7eeaf6"),
        "userId" : "a0",
        "name" : "test50000",
        "index" : 50000,
        "position" : "SP",
        "order" : 0,
        "gameRecord" : [
                {
                        "seasonIndex" : 2017251,
                        "gameIndex" : 1,
                        "ERA" : 3.00,
                },
        {
                        "seasonIndex" : 2017251,
                        "gameIndex" : 2,
                        "ERA" : 4.50,
                }
        ]
       }

db.gameResult.find().pretty()

{
        "_id" : ObjectId("59b22b7dac48252e6c7eeaf6"),
        "seasonIndex" : 2017251,
        "gameIndex" : 1,
        "away" : "a9",
        "home" : "a0",
        "awayScore" : 9,
        "homeScore" : 4,
        "awayPitcherList" : [
                50180
        ],
        "homePitcherList" : [
                50000,
                50049,
                50048,
                50047
        ]
      }

聚合查询：

> db.gameResult.aggregate([
{
    $match : {gameIndex : 1 ,home : "a0"}
},
{
    $lookup:
        {
        from: "pitcher",
        localField : "awayPitcherList",
        foreignField : "index",
        as: "awayPitcherList"
        }
},
{
    $lookup:
        {
        from: "pitcher",
        localField : "homePitcherList",
        foreignField : "index",
        as: "homePitcherList"
        }
}
]).pretty()

最终期望输出：

"_id" : ObjectId("59b22b7dac48252e6c7eeaf6"),
"seasonIndex" : 2017251,
"gameIndex" : 1,
"away" : "a9",
"home" : "a0",
"awayScore" : 9,
"homeScore" : 4,

"awayPitcherList" : [
    {
    "name" : "test50180",
    "gameRecord" : [
        {
                "seasonIndex" : 2017251,
                "gameIndex" : 1,
                "ERA" : 3.00,
        }
]
],
"homePitcherList" : [
             {
    "name" : "test50000",
    "gameRecord" : [
        {
                "seasonIndex" : 2017251,
                "gameIndex" : 1,
                "ERA" : 3.00,
        }
],
             {
    "name" : "test50049",
    "gameRecord" : [
        {
                "seasonIndex" : 2017251,
                "gameIndex" : 1,
                "ERA" : 3.00,
        }
],
             {
    "name" : "test50048",
    "gameRecord" : [
        {
                "seasonIndex" : 2017251,
                "gameIndex" : 1,
                "ERA" : 3.00,
        }
],
            {
    "name" : "test50047",
    "gameRecord" : [
        {
                "seasonIndex" : 2017251,
                "gameIndex" : 1,
                "ERA" : 3.00,
        }
]
]

我希望能获取包含游戏编号（在此情况下为1）的姓名和游戏记录。

请改进我的聚合查询。如果您有Spring代码，请提供，非常感谢。

- Ricky Lee

Answer 1

您可以在3.4中使用以下查询。

下面的查询使用 $addFields 覆盖现有的 awayPitcherList ，将更新后的包含 name 和 gameRecord 的 awayPitcherList 进行了覆盖。

使用 $map 阶段保留 name 字段，并使用 $filter 过滤 gameRecord 以仅保留匹配的 gameIndex 元素。 homePitcherList 的类似聚合。

db.gameResult.aggregate(
[
  {
    "$match": {
      "gameIndex": 1,
      "home": "a0"
    }
  },
  {
    "$lookup": {
      "from": "pitcher",
      "localField": "awayPitcherList",
      "foreignField": "index",
      "as": "awayPitcherList"
    }
  },
  {
    "$addFields": {
      "awayPitcherList": {
        "$map": {
          "input": "$awayPitcherList",
          "as": "awayPitcher",
          "in": {
            "name": "$$awayPitcher.name",
            "gameRecord": {
              "$filter": {
                "input": "$$awayPitcher.gameRecord",
                "as": "gameRecord",
                "cond": {
                  "$eq": [
                    "$$gameRecord.gameIndex",
                    1
                  ]
                }
              }
            }
          }
        }
      }
    }
  },
  {
    "$lookup": {
      "from": "pitcher",
      "localField": "homePitcherList",
      "foreignField": "index",
      "as": "homePitcherList"
    }
  },
  {
    "$addFields": {
      "homePitcherList": {
        "$map": {
          "input": "$homePitcherList",
          "as": "homePitcher",
          "in": {
            "name": "$$homePitcher.name",
            "gameRecord": {
              "$filter": {
                "input": "$$homePitcher.gameRecord",
                "as": "gameRecord",
                "cond": {
                  "$eq": [
                    "$$gameRecord.gameIndex",
                    1
                  ]
                }
              }
            }
          }
        }
      }
    }
  }
])

在3.2版本中，请使用以下聚合查询。

 db.gameResult.aggregate(
    [
      {
        "$match": {
          "gameIndex": 1,
          "home": "a0"
        }
      },
      {
        "$lookup": {
          "from": "pitcher",
          "localField": "awayPitcherList",
          "foreignField": "index",
          "as": "awayPitcherList"
        }
      },
      {
        "$project": {
          "homePitcherList":1,
          "awayPitcherList": {
            "$map": {
              "input": "$awayPitcherList",
              "as": "awayPitcher",
              "in": {
                "name": "$$awayPitcher.name",
                "gameRecord": {
                  "$filter": {
                    "input": "$$awayPitcher.gameRecord",
                    "as": "gameRecord",
                    "cond": {
                      "$eq": [
                        "$$gameRecord.gameIndex",
                        1
                      ]
                    }
                  }
                }
              }
            }
          }
        }
      },
      {
        "$lookup": {
          "from": "pitcher",
          "localField": "homePitcherList",
          "foreignField": "index",
          "as": "homePitcherList"
        }
      },
      {
        "$project": {
          "awayPitcherList":1,
          "homePitcherList": {
            "$map": {
              "input": "$homePitcherList",
              "as": "homePitcher",
              "in": {
                "name": "$$homePitcher.name",
                "gameRecord": {
                  "$filter": {
                    "input": "$$homePitcher.gameRecord",
                    "as": "gameRecord",
                    "cond": {
                      "$eq": [
                        "$$gameRecord.gameIndex",
                        1
                      ]
                    }
                  }
                }
              }
            }
          }
        }
      }
    ])

- s7vr

---

我也可以得到$project查询吗？我很难找到addFields构建器。谢谢。 - Ricky Lee