MongoDB Aggregation join array of strings to single string

Question

We're trying to 'join' an array of strings to a single string within an aggregation.

Given is the following dataset:

Collection 1:

{
  id: 1234,
  field: 'test'
}

Collection 2:

{
  id: 1111,
  collection1_id: 1234,
  name: 'Max'
},
{
  id: 1112,
  collection1_id: 1234,
  name: 'Andy'
}

The current result (after lookup etc.):

{
  id: 1234,
  field: 'test',
  collection2: ['Max', 'Andy'] 
}

The desired result:

{
  id: 1234,
  field: 'test',
  collection2: 'Max, Andy'
}

Is it somehow possible to join the 'collection2' to a single string? We've tried with $concat but it only accepts strings.

Meza

Answer 1

You were on the right track.

Just add $reduce over $concat in your $project stage.

'collection2': {
    '$reduce': {
        'input': '$collection2',
        'initialValue': '',
        'in': {
            '$concat': [
                '$$value',
                {'$cond': [{'$eq': ['$$value', '']}, '', ', ']}, 
                '$$this']
        }
    }
}

Note: We use $cond to prevent a leading , in the concatenation. You could also use $substrCP before $reduce as an alternative to $cond.

Belden

Any solution for mongo 3.2? I don't have access to $reduce – Kosygin

@Friedrich May you please elaborate more on how one may use $substrCP? – Roil

Answer 2

Starting Mongo 4.4, the $group stage has a new aggregation operator $accumulator allowing custom accumulations of documents as they get grouped:

// { "collectionId" : 1234, "name" : "Max"  }
// { "collectionId" : 876,  "name" : "Rob"  }
// { "collectionId" : 1234, "name" : "Andy" }
db.collection.aggregate([
  { $group: {
    _id: "$collectionId",
    names: {
      $accumulator: {
        accumulateArgs: ["$name"],
        init: function() { return [] },
        accumulate: function(names, name) { return names.concat(name) },
        merge: function(names1, names2) { return names1.concat(names2) },
        finalize: function(names) { return names.join(",") },
        lang: "js"
      }
    }
  }}
])
// { "_id" : 876,  "names" : "Rob"      }
// { "_id" : 1234, "names" : "Max,Andy" }

The accumulator:

• accumulates on the field name (accumulateArgs)
• is initialised to an empty array (init)
• accumulates by concatenating new names to already seen names (accumulate and merge)
• and finally joins all names as a string (finalize)

Barrios

Answer 3

To flatten this array, you need to shift process to client.

mongo will provide some new flattening options in new edition, but afaik it will be arithmetic ones (avg, min, max....).

Spode

Answer 4

Sometimes it's easiest to use JavaScript:

db.getCollection('Collection1').aggregate([
{
   $lookup:
     {
       from: 'Collection2',
       localField: 'id',
       foreignField: 'collection1_id',
       as: 'col2'
     }
}]).map((e) => ({
  id: e.id,
  field: e.field,
 collection2: Array.isArray(e.col2) &&
   e.col2.reduce((arr, el) => {
     arr.push(el.name);
    return arr;
  }, []).join(', ')
}))

Howze