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.

Zoe - Save the data dump · Accepted Answer · 2019-04-28 14:54:49Z

47

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.

edited Apr 28, 2019 at 14:54

Zoe - Save the data dump

28.4k22 gold badges130 silver badges163 bronze badges

answered Jan 18, 2018 at 12:52

fielc92

94311 silver badges13 bronze badges

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

2 Comments

Graeme Moss Over a year ago

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

user2402616 Over a year ago

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

Xavier Guihot · Accepted Answer · 2020-03-15 16:15:23Z

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)

profesor79 · Accepted Answer · 2016-07-14 16:16:33Z

-1

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....).

answered Jul 14, 2016 at 16:16

profesor79

9,5033 gold badges36 silver badges53 bronze badges

Comments

Pax Beach · Accepted Answer · 2020-10-08 09:25:30Z

-2

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(', ')
}))

answered Oct 8, 2020 at 9:25

Pax Beach

2,9112 gold badges26 silver badges30 bronze badges

Collectives™ on Stack Overflow

MongoDB Aggregation join array of strings to single string

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related