查找后的MongoDB聚合项目

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 的游戏索引的名称和游戏记录。

请改进我的聚合查询。很多很多 tnx 用于 Spring 代码，如果你有的话。

Answer 1

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

以下查询使用 $addFields 用更新的 awayPitcherList 覆盖现有的 awayPitcherList，其中包括 name 和游戏记录.

$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
                      ]
                    }
                  }
                }
              }
            }
          }
        }
      }
    ])