Mongodb - Concatenate multiple nested arrays and convert to a string

Question

I have the following documents

{"_id": "1", "posts": [{"text": "all day long I dream about", "datetime": "123"}, {"text": "all day long ", "datetime": "321"}]}
{"_id": "1", "posts": [{"text": "all day long I dream about", "datetime": "123"}, {"text": "all day long ", "datetime": "8888"}, {"text": "I became very hungry after watching this movie...", "datetime": "8885"}]}

I wish to concatenate the text fields into a new field and the datetime field into a new field while also joining the arrays elements into a string in a way that the new field will be as follows

{"_id": "1", "text": "all day long I dream about, all day long ", "datetime": "123, 321"}
{"_id": "1", "text": "all day long I dream about, all day long ,I became very hungry after watching this movie... ", "datetime": "123, 8888, 8885"}

What's the best way of doing that directly on Mongodb server? Is there such way?

Answer 1

Query

• reduce on posts starting with null (same code 2x)
• if not null (concat all_string ", " current_string)
• else current_string (this happens only for the first string)

*that check if not null is only for the first string, to not have something like ", string1, string2 ...." we do this to avoid add , to the first string
*you could do it with 1 reduce also, but code would be more complicated

Test code here

aggregate(
[{"$set":
  {"text":
   {"$reduce":
    {"input":"$posts",
     "initialValue":null,
     "in":
     {"$cond":
      ["$$value", {"$concat":["$$value", ", ", "$$this.text"]},
       "$$this.text"]}}}}},
 {"$set":
  {"datetime":
   {"$reduce":
    {"input":"$posts",
     "initialValue":null,
     "in":
     {"$cond":
      ["$$value", {"$concat":["$$value", ", ", "$$this.datetime"]},
       "$$this.datetime"]}}}}}
 {"$unset":["posts"]}])