Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MongoDB Aggregation join array of strings to single string

Tags:

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.

like image 459
patrickkeller Avatar asked Jul 14 '16 14:07

patrickkeller


People also ask

Can we use $and in aggregate MongoDB?

You can use $and with aggregation but you don't have to write it, and is implicit using different filters, in fact you can pipe those filters in case one of them needs a different solution.

Which aggregation method is preferred for use by MongoDB?

The pipeline provides efficient data aggregation using native operations within MongoDB, and is the preferred method for data aggregation in MongoDB. The aggregation pipeline can operate on a sharded collection. The aggregation pipeline can use indexes to improve its performance during some of its stages.


1 Answers

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.

like image 168
Friedrich Avatar answered Oct 02 '22 18:10

Friedrich