$concat on aggregate mongodb

Question

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?

Answer 1

$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"] }
        }
    }    
])