Question 331678 in Mongodb Question posted in
The official documentation can be found here.

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?

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    MongoDB is a NoSQL DB and by design you should only be querying a single row by ID, and all aggregations are meant to be separately. E.g. during write time.

    $concat will not really work for you since it aggregates values within a single row.

    I think in your case it’s very simple to get all the results and then concatenate at the client side.

    E.g. JavaScript / NodeJS:

    function concatKeys(input) {
    return (input || []).map(({key}) => key).join("|")
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search