How to fix MongoDB array concatination error?

Question

I have a collection in mongodb with a few million documents. there is an attribute(categories) that is an array that contains all the categories that a document belongs to. I am using following query to convert the array into a comma separated string to add it to SQL server through a spoon transformation. for example the document has ["a","b","c",...] and i need a,b,c,.... so i can pit it in a column

categories: {
        $cond: [
          { $eq: [{ $type: "$categories" }, "array"] },
          {
            $trim: {
              input: {
                $reduce: {
                  input: "$categories",
                  initialValue: "",
                  in: { $concat: ["$$value", ",", "$$this"] }
                }
              }
            }
          },
          "$categories"
        ]
      }

when i run the query i get the following error and i cannot figure out what the problem is.

com.mongodb.MongoQueryException: Query failed with error code 16702 and error message '$concat only supports strings, not array' on server

a few documents had this attribute as string and not array so i added a type check. but still the issue is there. any help on how to narrow down the issue will be very appreciated.

A few other attributes were the same in the same collection and this query is working fine for the rest of them.

Answer 1

I don't see any problem in your aggregation. It shouldn't give this error. Can you try to update your mongodb version?

However, your aggregation is not working properly reduce wasn't working . I converted it to this:

db.collection.aggregate([
    {
        "$project": {
            categories: {
                $cond: [
                    {
                        $eq: [{ $type: "$categories" }, "array"]
                    },
                    {
                        '$reduce': {
                            'input': '$categories',
                            'initialValue': '',
                            'in': {
                                '$concat': [
                                    '$$value',
                                    { '$cond': [{ '$eq': ['$$value', ''] }, '', ', '] },
                                    '$$this'
                                ]
                            }
                        }
                    },
                    "$categories"
                ]
            }
        }
    }
])

Edit:

So, if you have nested arrays in the categories field. We can flat our arrays with unwind stage. So if you can add these 3 stages above the $project stage. Our aggregation will work.

  {
    "$unwind": "$categories"
  },
  {
    "$unwind": "$categories"
  },
  {
    "$group": {
      _id: null,
      categories: {
        $push: "$categories"
      }
    }
  },

Playground