Concatenate array elements in MongoDB 3.4 with Aggregation Framework

Question

What I have :

{ "_id" : ObjectId("577dc9d61a0b7e0a40499f90"), "equ" : 123456, "key" : "p" }
{ "_id" : ObjectId("577c789b1a0b7e0a403f1b52"), "equ" : 123456, "key" : "r" }
{ "_id" : ObjectId("577b27481a0b7e0a4033965a"), "equ" : 123456, "key" : "r" }
{ "_id" : ObjectId("5779d6111a0b7e0a40282dc7"), "equ" : 123456, "key" : "o" }

What I want :

{ "_id" : ObjectId("5779d6111a0b7e0a40282dc7"), "equ" : 123456, "keys" : "prro" }

What I tried :

db.table.aggregate([{"$group":{"_id":0, "keys":{"$push":"$key"}}}]) returns an array and not a string:

{"_id":0, "keys":["p","r","r","o"]}

Do you have any idea?

Answer 1

TL; DR

---

Use this aggregation pipeline:

db.col.aggregate([
    {$group: {_id: "$equ", last: {$last: "$_id"}, keys: {$push: "$key"}}},
    {
        $project: {
            equ: "$_id",
            _id: "$last",
            keys: {
                $reduce: {
                    input: "$keys",
                    initialValue: "",
                    in: {$concat: ["$$value", "$$this"]}
                }
            }
        }
    }
])

More details

---

First you should group the documents based on the equ value and also maintain an array of keys along with the _id of the last group member:

var groupByEqu = {
    $group: {
        _id: "$equ",
        last: {$last: "$_id"},
        keys: {$push: "$key"}
    }
}

Just applying this pipeline operation would result in:

{ 
    "_id" : 123456, 
    "last" : ObjectId("5779d6111a0b7e0a40282dc7"), 
    "keys" : [ "p", "r", "r", "o" ] 
}

You should use a Projection to transform the preceding document into your desired one. The first two transformations are trivial. For joining the array elements you can use the new $reduce operator:

var project = {
    $project: {
        equ: "$_id",
        _id: "$last",
        keys: {
            $reduce: {
                input: "$keys",
                initialValue: "",
                in: {$concat: ["$$value", "$$this"]}
            }
        }
    }
} 

Applying these two pipeline operations would give you the desired result:

db.col.aggregate([groupByEqu, project])

Which is:

{ 
    "equ" : 123456, 
    "_id" : ObjectId("5779d6111a0b7e0a40282dc7"), 
    "keys" : "prro"
}