Mongodb aggregation project after lookup
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.
Answer
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
]
}
}
}
}
}
}
}
}
])