Mongo aggregate nested array

Question

I have a mongo collection with following structure

{
    "userId" : ObjectId("XXX"),
    "itemId" : ObjectId("YYY"),
    "resourceId" : 1,
    "_id" : ObjectId("528455229486ca3606004ec9"),
    "parameter" : [
        {
            "name" : "name1",
            "value" : 150,
            "_id" : ObjectId("528455359486ca3606004eed")
        },
        {
            "name" : "name2",
            "value" : 0,
            "_id" : ObjectId("528455359486ca3606004eec")
        },
        {
            "name" : "name3",
            "value" : 2,
            "_id" : ObjectId("528455359486ca3606004eeb")
        }
    ]
}

There can be multiple documents with the same 'useId' with different 'itemId' but the parameter will have same key/value pairs in all of them.

What I am trying to accomplish is return aggregated parameters "name1", "name2" and "name3" for each unique "userId" disregard the 'itemId'. so final results would look like for each user :

{
  "userId" : ObjectId("use1ID"),
  "name1" : (aggregatedValue),
  "name2" : (aggregatedValue),
  "name3" : (aggregatedVAlue) 
},
{
  "userId" : ObjectId("use2ID"),
  "name1" : (aggregatedValue),
  "name2" : (aggregatedValue),
  "name3" : (aggregatedVAlue) 
}

Is it possible to accomplish this using the aggregated methods of mongoDB ? Could you please help me to build the proper query to accomplish that ?

Neil Lunn · Accepted Answer · 2014-03-15 14:38:59Z

The simplest form of this is to keep things keyed by the "parameter" "name":

db.collection.aggregate(
   // Unwind the array
   { "$unwind": "$parameter"},

   // Group on the "_id" and "name" and $sum "value"
   { "$group": {
       "_id": { 
           "userId": "$userId",
           "name": "$parameter.name"
       }, 
       "value": { "$sum": "$parameter.value" } 
   }},

   // Put things into an array for "nice" processing
   { "$group": {
       "_id": "$_id.userId",
       "values": { "$push": { 
           "name": "$_id.name",
           "value": "$value"
       }}
   }}
)

If you really need to have the "values" of names as the field values, you can do the the following. But since you are "projecting" the fields/properties then you must specify them all in your code. You cannot be "dynamic" anymore and you are coding/generating each one:

db.collection.aggregate([
    // Unwind the array
    { "$unwind": "$parameter"},

   // Group on the "_id" and "name" and $sum "value"
    { "$group": {
        "_id": {
             "userId": "$userId",
             "name": "$parameter.name"
        },
        "value": { "$sum": "$parameter.value"}
    }},

    // Project out discrete "field" names with $cond
    { "$project": {
        "name1": { "$cond": [
            { "$eq": [ "$_id.name", "name1" ] },
            "$value",
            0
        ]},
        "name2": { "$cond": [
            { "$eq": [ "$_id.name", "name2" ] },
            "$value",
            0
        ]},
        "name3": { "$cond": [
            { "$eq": [ "$_id.name", "name3" ] },
            "$value",
            0
        ]},
    }},

    // The $cond put "0" values in there. So clean up with $group and $sum
    { "$group": { 
        _id: "$_id.userId",
        "name1": { "$sum": "$name1" },
        "name2": { "$sum": "$name2" },
        "name3": { "$sum": "$name3" }
    }}

])

So while the extra steps give you the result that you want ( well with a final project to change the _id to userId ), for my mind the short version is workable enough, unless you really do need it. Consider the output from there as well:

{
    "_id" : ObjectId("53245016ea402b31d77b0372"),
    "values" : [
        {
            "name" : "name3",
            "value" : 2
        },
        {
            "name" : "name2",
            "value" : 0
        },
        {
            "name" : "name1",
            "value" : 150
        }
    ]
}

So that would be what I would use, personally. But your choice.

Maksym Strukov · Accepted Answer · 2014-03-15 14:36:46Z

Not sure if I got your question but if the name field can contain only "name1", "name2", "name3" or at least you are only interested in this values, one of the possible queries could be this one:

db.aggTest.aggregate(
    {$unwind:"$parameter"}, 
    {$project: {"userId":1, "parameter.name":1, 
       "name1" : {"$cond": [{$eq : ["$parameter.name", "name1"]}, "$parameter.value", 0]},
       "name2" : {"$cond": [{$eq : ["$parameter.name", "name2"]}, "$parameter.value", 0]}, 
       "name3" : {"$cond": [{$eq : ["$parameter.name", "name3"]}, "$parameter.value", 0]}}}, 
    {$group : {_id : {userId:"$userId"}, 
       name1 : {$sum:"$name1"}, 
       name2 : {$sum:"$name2"}, 
       name3 : {$sum:"$name3"}}})

It firsts unwinds the parameter array, then separates name1, name2 and name3 values into different columns. There's a simple conditional statement for that. After that we can easily aggreagate by the new columns.

Hope it helps!

Collectives™ on Stack Overflow

Mongo aggregate nested array

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related