I have a collection of documents like the ones below:

{
  '_id': ,
  'group': 'group1',
  'name': 'car; pet; bike'
},
{
  '_id': ,
  'group': 'group1',
  'name': 'car; pet; plant'
},
{
  '_id': ,
  'group': 'group2',
  'name': 'music; toy; cat'
},
{
  '_id': ,
  'group': 'group2',
  'name': 'dog'
},
{
  '_id': ,
  'group': 'group3',
  'name': 'star; planet'
}

I need to use the .aggregate() to get the following:

{
  '_id': ,
  'group': 'group1',
  'name': 'car; pet; bike; plant'
},
{
  '_id': ,
  'group': 'group2',
  'name': 'music; toy; cat; dog'
},
{
  '_id': ,
  'group': 'group3',
  'name': 'star; planet'
}

So, the result is a group of the field group and concat the field name doing a distinct in the values.

I couldn't figure out how to do it.

How can it be done ?

CodePudding user response：

Maybe something like this:

 mongos> db.k.find()
 { "_id" : ObjectId("619e0e7830b7997817f9f634"), "group" : "group1" }
 { "_id" : ObjectId("619e0e9430b7997817f9f635"), "group" : "group1", "name" : "car; pet; bike" }
 { "_id" : ObjectId("619e0ea430b7997817f9f636"), "group" : "group1", "name" : "car; pet" }
 { "_id" : ObjectId("619e0eb530b7997817f9f637"), "group" : "group2", "name" : "car; pet" }
mongos> db.k.aggregate([ 
 { $project : { name : { $split: ["$name", " ;"] },group:1 }} ,
 { $unwind:"$name"} ,
 { $group:{_id:"$group" , name:{$addToSet:"$name"}}} ,
 { $addFields:{names:{$reduce:{input:{$slice:["$name",1,{$size:"$name"} ]},initialValue:{$arrayElemAt:["$name",0]},in:{$concat:["$$value"," ; ","$$this" ]}    }} }} , 
 { $project:{group:"$_id" , name:"$names" , _id:0 }}])

{ "group" : "group2", "name" : "car; pet" }
{ "group" : "group1", "name" : "car; pet; bike ; car; pet" }
mongos>

explained:

1. split the strings in array name by ";"
2. unwind the array name
3. group/join by group and form the array name to remove duplicates
4. add new field names to join the array strings in
5. project just the necesary fields as expected in the output