Mongodb aggregation project after lookup
You can use the following query in 3.4.
The below query uses $addFields
to overwrite the existing awayPitcherList
with the updated awayPitcherList
which includes name
and gameRecord
.
$map
stage to keep the name
field and $filter
to filter the gameRecord
to retain only matching gameIndex
element.
Similar aggregation for 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
]
}
}
}
}
}
}
}
}
])
Use below aggregate query for 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
]
}
}
}
}
}
}
}
}
])
Admin
Updated on June 16, 2022Comments
-
Admin almost 2 years
I am doing MongoDB aggregation. I want to lookup two collections then project only desired field in nested array.
Two collections to lookup:
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 ] }
Aggregate query:
> 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()
Finally desired Output:
"_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, } ] ]
I want name and gameRecord which contains gameIndex of (in this case) 1 only.
Please improve my aggregate query. Many many tnx for Spring code if you have one.