我正在进行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代码,请提供,非常感谢。