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?

Takis · Accepted Answer · 2022-01-06 15:12:42Z

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

edited Jan 6, 2022 at 15:12

answered Jan 6, 2022 at 14:45

Takis

8,7552 gold badges17 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Lior Magen Over a year ago

Works perfectly and you explanation is blessed. Thank you.

Lior Magen Over a year ago

Let's say I want to create a new string of 'datetime' even when all the values are null, for example - "null, null, null..." How would I do that?

Takis Over a year ago

i think you need this , it uses one variable, if datetime is null, then it is as it it was "null" , else has the normal datetime value.

Lior Magen Over a year ago

You are a magician. Thank you so much, you helped me a lot.

Collectives™ on Stack Overflow

Mongodb - Concatenate multiple nested arrays and convert to a string

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related