MongoDB array as string

Question

I have a collection of docs that has a couple arrays as items, I want to export them into a CSV, but the array obviously comes back as something like "items" : ["one","two",'three"] and the spreadsheet sees these as separate one, two, three

Is there a way to convert and array like

["one","two",'three"]

to a string like this

"one,two,three"

I would prefer to do this in the aggregation query, however If I need to dump to a temp collection and convert the collection - I am ok with that.

any help appreciated

Does this answer your question: stackoverflow.com/questions/55838681/… ? — mickl
– mickl, Commented Jun 22, 2020 at 16:04

igorkf · Accepted Answer · 2020-06-22 17:18:35Z

2

You have to use $reduce.
This applies an expression to each element in an array and combines them into a single value.

Test data:

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "key": [
      "one",
      "two",
      "three"
    ]
  }
]

Query:

db.collection.aggregate([
  {
    "$project": {
      "key": {
        "$rtrim": {
          "input": {
            "$reduce": {
              "input": "$key",
              "initialValue": "",
              "in": {
                "$concat": [
                  "$$value",
                  "$$this",
                  ","
                ]
              }
            }
          },
          "chars": ","
        }
      }
    }
  }
])

Result:

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "key": "one,two,three"
  }
]

The $rtrim was used to eliminate the last , remaining. Without this, the result would be "one,two,three,"

You can test it yourself here: https://mongoplayground.net/p/zNGlOw5LjeP

edited Jun 22, 2020 at 17:18

answered Jun 22, 2020 at 16:13

igorkf

3,6152 gold badges26 silver badges37 bronze badges

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

1 Comment

geekyj Over a year ago

Thank you for this very creative solution! I couldn't find an MongoDB operator that does exactly this until I find your answer! Worked as designed when used on MongoDB v6.

Collectives™ on Stack Overflow

MongoDB array as string

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related