Merging array fields in MongoDB aggregation

Question

Is it possible to merge array fields in while using MongoDB aggregation framework? Here is a summary problem I am trying to solve:

Sample input documents for aggregation:

{
  "Category" : 1,
  "Messages" : ["Msg1", "Msg2"],
  "Value" : 1
},
{
  "Category" : 1,
  "Messages" : [],
  "Value" : 10
},
{
  "Category" : 1,
  "Messages" : ["Msg1", "Msg3"],
  "Value" : 100
},
{
  "Category" : 2,
  "Messages" : ["Msg4"],
  "Value" : 1000
},
{
  "Category" : 2,
  "Messages" : ["Msg5"],
  "Value" : 10000
},
{
  "Category" : 3,
  "Messages" : [],
  "Value" : 100000
}

We want to group by 'Category' while summing up 'Value' and merging 'Messages'. I have tried this aggregation pipeline:

{group : {
        _id : "$Category",
        Value : { $sum : "$Value"},
        Messages : {$push : "$Messages"}
    }
}, 
{$unwind : "$Messages"}, 
{$unwind : "$Messages"}, 
{$group : {
        _id : "$_id",
        Value : {$first : "$Value"},
        Messages : {$addToSet : "$Messages"}
    }
}

The result is:

"result" : [{
        "_id" : 1,
        "Value" : 111,
        "Messages" : ["Msg3", "Msg2", "Msg1"]
    }, 
    {
        "_id" : 2,
        "Value" : 11000,
        "Messages" : ["Msg5", "Msg4"]
    }
]

However, this completely misses Category 3 since the documents where 'Category' is 3 do not have any 'Messages' and they are dropped by the second unwind. We would like the result to include the following as well:

{
    "_id" : 3,
    "Value" : 100000,
    "Messages" : []
}

Is there a neat way of achieving this by the aggregation framework?

is Messages guaranteed to be there as an array? Or is it possible it won't exist or will be there but as a different type? — Asya Kamsky
– Asya Kamsky, Commented Oct 18, 2013 at 1:11
yes Messages is guaranteed to exist as an array (which may be empty for some records). — etkarayel
– etkarayel, Commented Oct 29, 2013 at 11:13
have you tried the preserveNullAndEmptyArrays option to $unwind? — tony_k
– tony_k, Commented Dec 20, 2016 at 15:40
This question was raised when we were using v2.6. I believe using preserveNullAndEmptyArrays should do what we were looking for. — etkarayel
– etkarayel, Commented Mar 30, 2017 at 13:09

c1moore · Accepted Answer · 2016-08-15 23:22:12Z

19

Here is a trick you can use if Messages is guaranteed to be an array:

> db.messages.find()
    { "Category" : 1, "Messages" : [  "Msg1",  "Msg2" ], "Value" : 1 }
    { "Category" : 1, "Messages" : [ ], "Value" : 10 }
    { "Category" : 1, "Messages" : [  "Msg1",  "Msg3" ], "Value" : 100 }
    { "Category" : 2, "Messages" : [  "Msg4" ], "Value" : 1000 }
    { "Category" : 2, "Messages" : [  "Msg5" ], "Value" : 10000 }
    { "Category" : 3, "Messages" : [ ], "Value" : 100000 }

> var group1 = {
    "$group":   {
        "_id":      "$Category",
        "Value":    {
            "$sum":     "$Value"
        },
        "Messages": {
            "$push":    "$Messages"
        }
    }
};

> var project1 = {
    "$project": {
        "Value":    1,
        "Messages": {
            "$cond":    [
                {
                    "$eq":  [
                        "$Messages",
                        [ [ ] ]
                    ]
                },
                [ [ null ] ],
                "$Messages"
            ]
        }
    }
};

> db.messages.aggregate( group1, project1 )
    { "_id" : 3, "Value" : 100000, "Messages" : [  [  null ] ] }
    { "_id" : 2, "Value" : 11000, "Messages" : [  [  "Msg4" ],  [  "Msg5" ] ] }
    { "_id" : 1, "Value" : 111, "Messages" : [  [  "Msg1",  "Msg2" ],  [ ],  [  "Msg1",  "Msg3" ] ] }

Now unwind twice and re-group to get a single Messages array.

> var unwind = {"$unwind":"$Messages"};

> var group2 = {
    $group: {
        "_id":      "$_id", 
        "Value":    {
            "$first":       "$Value"
        }, 
        "Messages": {
            "$addToSet":    "$Messages"
        }
    }
};

> var project2 = {
    "$project": {
        "Category": "$_id",
        "_id":      0,
        "Value":    1,
        "Messages": {
            "$cond":    [
                {
                    "$eq":  [
                        "$Messages",
                        [ null ]
                    ]
                },
                [ ],
                "$Messages"
            ]
        }
    }
};

> db.messages.aggregate(group1, project1, unwind, unwind, group2 ,project2 )
    { "Value" : 111, "Messages" : [  "Msg3",  "Msg2",  "Msg1" ], "Category" : 1 }
    { "Value" : 11000, "Messages" : [  "Msg5",  "Msg4" ], "Category" : 2 }
    { "Value" : 100000, "Messages" : [ ], "Category" : 3 }

edited Aug 15, 2016 at 23:22

c1moore

1,8771 gold badge17 silver badges27 bronze badges

answered Oct 18, 2013 at 1:27

Asya Kamsky

42.4k5 gold badges113 silver badges133 bronze badges

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

5 Comments

etkarayel Over a year ago

Thanks for the tips. It almost does what I need. However, there is a case where it does not produce the desired result. Aggregated result for Category 1 (based on the documents in my original post) ends up having 4 messages: ["Msg1", "Msg2", "Msg3", "dummy"]. I am not sure how to easily get rid of "dummy" for this case.

Asya Kamsky Over a year ago

Right - there is a way to get rid of it - I'll update the answer

Asya Kamsky Over a year ago

ok, the complete answer now with all the steps - should be exactly what you want :)

viren Over a year ago

@AsyaKamsky thanks this is great help could you help me for one more use case in it as I have two array field in my document as you can say messages and tags. And I need same behaviour for two fields

Asya Kamsky Over a year ago

post it as a question with full details - comments aren't really for discussing new problems.

Ruben Stolk · Accepted Answer · 2018-05-17 21:46:34Z

4

As already mentioned in one of the comments, the simplest answer to the original question is to add preserveNullAndEmptyArrays to the $unwind stage.

answered May 17, 2018 at 21:46

Ruben Stolk

13.4k2 gold badges20 silver badges12 bronze badges

Collectives™ on Stack Overflow

Merging array fields in MongoDB aggregation

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related