CODEWITHSUNDEEP
mongodbaggregation-framework
Abdul Moiz
Abdul Moiz

Reputation: 1327

group concat of fields in mongodb in aggregation

My documents in collection,

{
    "_id" : "abc1",
    "Data" : {
        "Campaign Name" : "Campaign 1",
        "Ad Set Name" : "Adset 1",
        "Ad Name" : "Ad 1",
        "URL Tags" : "url tag 1"
    }
},
{
    "_id" : "abc2",
    "Data" : {
        "Campaign Name" : "Campaign 2",
        "Ad Set Name" : "Adset 1",
        "Ad Name" : "Ad 1",
        "URL Tags" : "url tag 1"
    }
},
{
    "_id" : "abc3",
    "Data" : {
        "Campaign Name" : "Campaign 2",
        "Ad Set Name" : "Adset 2",
        "Ad Name" : "Ad 2",
        "URL Tags" : "url tag 2"
    }
},
{
    "_id" : "abc4",
    "Data" : {
        "Campaign Name" : "Campaign 3",
        "Ad Set Name" : "Adset 1",
        "Ad Name" : "Ad 1",
        "URL Tags" : "url tag 1"
    }
}

Need something like this in output,

{
    "_id" : "Campaign 3",
    "total" : 60.0,
    "myField" : [ "url tag 1-Ad 1" ]
},
{
    "_id" : "Campaign 2",
    "total" : 120.0,
    "myField" : [ "url tag 1-Ad 1", "url tag 2-Ad 2" ]
},
{
    "_id" : "Campaign 1",
    "total" : 96.0,
    "myField" : [ "url tag 1-Ad 1" ]
}

What i have done(unsuccessfully) so far,

db.adsets.aggregate([
    {$group:{_id:"$Data.Campaign Name",total:{$sum:1}}},
    {$project:{_id:1,total:1,myField:{$concat:["$Data.URL Tags","-","$Data.Ad Name"]}}}
],{explain:false});

Currently getting this in output,

{
    "_id" : "Campaign 3",
    "total" : 60.0,
    "myField" : null
},
{
    "_id" : "Campaign 2",
    "total" : 120.0,
    "myField" : null
},
{
    "_id" : "Campaign 1",
    "total" : 96.0,
    "myField" : null
}

I don't know what i am doing wrong which is why i am getting null instead of concatenated string in myField. Please help!! Thanks in advance.

Upvotes: 8

Views: 16970

Answers (1)

Soorjith
Soorjith

Reputation: 196

You can concatenate required values and push to an array during grouping:

db.adsets.aggregate([
    {$group:{
        _id:"$Data.Campaign Name",
        total : {$sum:1},
        myfield: {$push: {$concat:["$Data.URL Tags", "-", "$Data.Ad Name"]}}
        }
    }]
)

This should give output as you mentioned.
(As you mentioned in your question comment, myfield field will be an array as you need and not object like initial question)

If you don't want concatenated values to appear twice when same, you can use $addToSet instead of push 

db.adsets.aggregate([
    {$group:{
        _id:"$Data.Campaign Name",
        total : {$sum:1},
        myfield: {$addToSet: {$concat:["$Data.URL Tags", "-", "$Data.Ad Name"]}}
        }
    }]
)

Upvotes: 14

Related Questions