Geek asks and answers

Mongodb and the aggregation structure. Summarize array elements and summarize document elements

There are many documents:

{
        "_id"   : ObjectId("506ddd1900a47d802702a904"),
        "subid" : "s1",
        "total" : "300",
        "details" :[{
                      name:"d1", value: "100"
                    },
                    {
                      name:"d2", value: "200"
                    }]
}

{
        "_id"   : ObjectId("306fff1900a47d802702567"),
        "subid" : "s1",
        "total" : "700",
        "details" : [{
                      name:"d1", value: "300"
                    },
                    {
                      name:"d8", value: "400"
                    }]
 }

Elements in arrays of "detail" may vary.

Question: how can I get such a result using the aggregation structure and java?

{
        "_id"     : "s1",
        "total"   : "1000",
        "details" : [{
                      name:"d1", value: "400"
                    },
                    {
                      name:"d2", value: "200"
                    },
                    {
                      name:"d8", value: "400"
                    }]
 }

Or maybe I should use custom map reduction features?

+3
source share
1 answer

This is very achievable with the aggregate, although a bit dumb, but allows you to run it:

db.collection.aggregate([

    // First Group to get the *master* total for the documents
    {"$group": {
        "_id": "$subid",
         "total": { "$sum": "$total" },
         details: { "$push": "$details" } 
     }},

     // Unwind the details
     {"$unwind": "$details"},

     // Unwind the details "again" since you *pushed* and array onto an array
     {"$unwind":"$details"},

     // Now sum up the values by each name (keeping levels)
     {"$group": {
         "_id:" {
              "_id": "$_id",
              "total": "$total",
              "name":  "$details.name"
          },
          "value": {"$sum": "$details.value"}
      }},

     // Sort the names (because you expect that!)
     {"$sort": { "_id.name": 1}},

     // Do some initial re-shaping for convenience
     {"$project": {
         "_id": "$_id._id",
         "total": "$_id.total",
         "details": { "name": "$_id.name", "value": "$value" }
     }},

     // Now push everything back into an array form
     {"$group": {
         "_id": {
              "_id": "$_id",
              "total": "$total"
         },
         "details": {"$push": "$details"}
     }},

     // And finally project nicely
     {"$project": {
         "_id": "$_id._id",
         "total": "$_id.total",
         "details": 1 
     }}
])

So, if you tried earlier, you could skip the concept of creating an initial group to get the sum of the top level in the field totalin your documents.

, - "" , . , , , "-" .

, $group name:

equiv (GROUP BY _id, total, "details.name" )

, . name ( ), , , $project .

, , . , double unwind.

+2

More articles:


All Articles