I want to project a not exist field to default value
CollectionA
{
_id: ObjectId("6013859ba0c3120034d08bfa"),
name: "A1",
refs:[
{id: ObjectId("6013859ba0c3120034d08bfb"), text: "ABC"},
{id: ObjectId("6013859ba0c3120034d08bfc"), text: "DEF"}
]
}
CollectionB
{
_id: ObjectId("6013859ba0c3120034d08bfb"),
name: "B1",
altName: "b1"
}
{
_id: ObjectId("6013859ba0c3120034d08bfc"),
name: "B2"
}
Expected output
{
"_id" : ObjectId("6013859ba0c3120034d08bfa"),
"name" : "A1",
"refs" : [
{
"id" : ObjectId("6013859ba0c3120034d08bfb"),
"text" : "ABC",
"name" : "B1",
"altName" : "b1"
},
{
"id" : ObjectId("6013859ba0c3120034d08bfc"),
"text" : "DEF",
"name" : "B2",
"altName" : "Unspecified"
}
]
}
In my case, I just want to do this in project expression. I tried with project like in this question
{
$project: {
...,
altName: {
$first: { $ifNull: [ "$$refsB.altName", "Unspecified" ] }
},
}
}
}
CodePudding user response:
You need to retrieve the refsB.altName
value via $first
first, then with $ifNull
to check and assign value if it is null.
altName: {
$ifNull: [
{
$first: "$refsB.altName"
},
"Unspecified"
]
}
Complete query
db.collectionA.aggregate([
{
$unwind: "$refs"
},
{
"$lookup": {
"from": "collectionB",
"localField": "refs.id",
"foreignField": "_id",
"as": "refsB"
}
},
{
$project: {
_id: 1,
name: 1,
refs: {
id: "$refs.id",
text: "$refs.text",
name: {
$first: "$refsB.name"
},
altName: {
$ifNull: [
{
$first: "$refsB.altName"
},
"Unspecified"
]
},
}
}
},
{
$group: {
_id: "$_id",
name: {
$first: "$name"
},
refs: {
$push: "$refs"
}
}
}
])