This a little bit confusing.
I am trying to $group the result of the aggregatation and while grouping them I create new field which consists of concation of two different fields. Hmm. Actually I was not willing to share the structure of database and confuse you. But the description is not explanatory.
So here we go.
student collection
{id: "1", school: "georgia tech"}
university collection
{name: "georgia tech" , state: "Georgia" , city: "Atlanta"}
What I want to get? I want to get
{id: 1, name: "georgia tech" , place: "Georgia_Atlanta"}
What have I done to achieve this?
db.student.aggregate([
{$match: {"id": "1"}},
{$lookup: {from: "university" , localField: "school" , foreignField: "name", as: "document"}},
{$group: {_id: "$id", name: {$push: "$school"}, place: {$push: {$concat: ["$document.state" , "_" , "$document.city"]}}}}
])
But this throws an error as;
assert: command failed: {
"ok" : 0,
"errmsg" : "$concat only supports strings, not Array",
"code" : 16702
}
In the meantime;
db.student.aggregate([
{$match: {"id": "1"}},
{$lookup: {from: "university" , localField: "school" , foreignField: "name", as: "document"}},
{$group: {_id: "$id", name: {$push: "$school"}, place: {$push: "$document.state" }}}
])
returns as;
{ "_id" : "1", "name" : [ "georgia tech" ], "place" : [ [ "Georgia" ] ] }
The problem is concating the state and city fields.
So here the question again. How can I concat document.state, _ and document.city?
$lookup returns an array so you need to use the $arrayElemAt operator to flatten it (if it has a single element) or $unwind (if it has multiple elements). So in the end, you should be able to run the following pipeline to get the desired result:
db.student.aggregate([
{ "$match": { "id": "1" } },
{
"$lookup": {
"from": "university",
"localField": "school",
"foreignField": "name",
"as": "document"
}
},
{
"$project": {
"id": 1,
"university": { "$arrayElemAt": [ "$document", 0 ] }
}
},
{
"$project": {
"id": 1,
"name": "$university.name",
"place": { "$concat": ["$university.state", "_", "$university.city"] }
}
}
])
I do not know the exact usecase, but if you need to use GroupBy, this is working version. Otherwise, chridam is using simplier approach :
db.student.aggregate([
{$match: {"id": "1"}},
{$lookup: {from: "university" , localField: "school" , foreignField: "name", as: "document"}},
{$group: {_id: "$id", name: {$first: "$school"}, tempplace: {$first: "$document" }}},
{$unwind: "$tempplace"},
{$project: {id: 1, name: 1, place: {$concat: ["$tempplace.state", "_", "$tempplace.city"]}}}
])