Concatenate arrays in an array in MongoDB

Question

I currently have a field in MongoDB that is a list of arrays. I would like to convert all this into a string, but I first need (I guess?) to concatenate my list of arrays into one single array before converting it.

Here is what my field looks like :

"SitesList" : [
        [
            "553550", 
            "496573", 
            "496574"
        ], 
        [
            "553550", 
            "496573", 
            "496574"
        ], 
        [
            "527772", 
            "565085"
        ], 
        [
            "565085", 
            "563248"
        ], 
        [
            "496576"
        ]
    ]

Here's what I tried so far to concatenate it (with concatArrays), but it's not working:

{ $addFields: {"Sites": { $concatArrays: ["$tdb.Data.site.list"]}}},

And what I would like to do ultimately to convert the final array into a string:

SitesList: {
                            $reduce: {
                                input: "$Sites",
                                initialValue: "",
                                in: {$concat: ["$$value",",",  {$toString:"$$this"}]}
                            }
                    },

Can someone one help me please? :) Thanks in advance!

Begs the question: why make MongoDB do this? You're not filtering or aggregating. Why not simply fetch SitesList and format as desired on the client side? — Buzz Moschetti
– Buzz Moschetti, Commented Nov 13, 2020 at 16:55
Hi @BuzzMoschetti, actually, I need to export my MongoDB data and insert it into an SQL Server database. That's why I want to get rid of arrays :) This is also not my full query which is much longer and complex. — Helley
– Helley, Commented Nov 16, 2020 at 9:48

turivishal · Accepted Answer · 2020-11-13 16:46:41Z

4

You can try,

$reduce to get concat arrays in single array and other $reduce to convert array to string using $concat and $substr to remove extra , from beginning of the string,

db.collection.aggregate([
  {
    $addFields: {
      SitesList: {
        $substr: [
          {
            $reduce: {
              input: {
                $reduce: {
                  input: "$SitesList",
                  initialValue: [],
                  in: { $concatArrays: ["$$this", "$$value"] }
                }
              },
              initialValue: "",
              in: { $concat: ["$$value", ",", "$$this"] }
            }
          },
          1,
          -1
        ]
      }
    }
  }
])

Playground

edited Nov 13, 2020 at 16:46

answered Nov 13, 2020 at 16:36

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

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

Collectives™ on Stack Overflow

Concatenate arrays in an array in MongoDB

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related