String aggregation in MongoDB

I have a collection of documents as follows:

{
    "id": ...,
    "key": 47
},
{
    "id": ...,
    "key": 12
}

I want to aggregate the whole collection to a single string, by concatenating the keys, delimited by |. So here, the output would be the string 47|12. I don’t mind an additional | at the end, if that makes things easier.

How is this done in MongoDB aggregation pipelines? I am coming from SQL and in PostgreSQL, this would look like select string_agg(key, "|" order by id) from my_table.

I think I can make this work by first aggregating the collection to an array and then applying $reduce with the $concat function on the single array, but I am not sure if this is the best solution, as aggregating first to an array feels wrong. Is there a way to reduce immediately on the collection instead?

>Solution :

The desired result can be achieved by using the $group stage with $push to create an array of all keys. After that, use $reduce to concatenate the keys with the desired delimiter.

Example with delimeter "|":

[
  {
    "$group": {
      "_id": null,
      "keys": { "$push": "$key" }
    }
  },
  {
    "$project": {
      "_id": 0,
      "concatenatedKeys": {
        "$reduce": {
          "input": "$keys",
          "initialValue": "",
          "in": { "$concat": ["$$value", "|", { "$toString": "$$this" }] }
        }
      }
    }
  }
]

In the end $toString converts each key to a string before concatenation. This may be omitted if all keys are of string type.