12

I got a problem when I use db.collection.aggregate in MongoDB.

I have a data structure like: 

_id:...

Segment:{
  "S1":1,
  "S2":5,
  ...
  "Sn":10
}

It means the following in Segment: I might have several sub attributes with numeric values. I'd like to sum them up as 1 + 5 + .. + 10

The problem is: I'm not sure about the sub attributes names since for each document the segment numbers are different. So I cannot list each segment name. I just want to use something like a for loop to sum all values together.

I tried queries like:

db.collection.aggregate([

  {$group:{
    _id:"$Account",

    total:{$sum:"$Segment.$"}
])

but it doesn't work.

Alex Shesterov
  • 26,085
  • 12
  • 82
  • 103
aaintw
  • 133
  • 1
  • 1
  • 4
  • You'll need to change your schema to be something like `segment: 5, value: 400` and put them in an array, or use a MapReduce. You can't do a sum on arbitrary fields. – WiredPrairie Jul 15 '13 at 20:21

3 Answers3

20

You have made the classical mistake to have arbitrary field names. MongoDB is "schema-free", but it doesn't mean you don't need to think about your schema. Key names should be descriptive, and in your case, f.e. "S2" does not really mean anything. In order to do most kinds of queries and operations, you will need to redesign you schema to store your data like this:

_id:...
Segment:[
    { field: "S1", value: 1 },
    { field: "S2", value: 5 },
    { field: "Sn", value: 10 },
]

You can then run your query like:

db.collection.aggregate( [
    { $unwind: "$Segment" },
    { $group: {
        _id: '$_id', 
        sum: { $sum: '$Segment.value' } 
    } } 
] );

Which then results into something like this (with the only document from your question):

{
    "result" : [
        {
            "_id" : ObjectId("51e4772e13573be11ac2ca6f"),
            "sum" : 16
        }
    ],
    "ok" : 1
}
Carles Sala
  • 1,989
  • 1
  • 16
  • 34
Derick
  • 35,169
  • 5
  • 76
  • 99
  • Thanks. I think you are right. But the problem is that I have loaded whole dataset which is pretty large.. Is there any way to transform in place? – aaintw Jul 16 '13 at 23:03
  • I don't think so. You'll have to write a script to do this one by one. Make sure you throttle your script though if this is already running in production. – Derick Jul 17 '13 at 09:51
  • Well, the answer was really helpful but $unwind may lead to some serious performance issues with a large dataset.So I will try thinking of a better solution first. – Himanshu Chandra Dec 22 '17 at 10:27
  • I think this question is similar to this one https://stackoverflow.com/questions/56640054/mongodb-aggregate-fields-without-knowing-all-the-fields-before-hand – Shoebie Jun 18 '19 at 17:52
10

Starting Mongo 3.4, this can be achieved by applying inline operations and thus avoid expensive operations such as $group:

// { _id: "xx", segments: { s1: 1, s2: 3, s3: 18, s4: 20 } }
db.collection.aggregate([
  { $addFields: {
      total: { $sum: {
        $map: { input: { $objectToArray: "$segments" }, as: "kv", in: "$$kv.v" }
      }}
  }}
])
// { _id: "xx", total: 42, segments: { s1: 1, s2: 3, s3: 18, s4: 20 } }

The idea is to transform the object (containing the numbers to sum) as an array. This is the role of $objectToArray, which starting Mongo 3.4.4, transforms { s1: 1, s2: 3, ... } into [ { k: "s1", v: 1 }, { k: "s2", v: 3 }, ... ]. This way, we don't need to care about the field names since we can access values through their "v" fields.

Having an array rather than an object is a first step towards being able to sum its elements. But the elements obtained with $objectToArray are objects and not simple integers. We can get passed this by mapping (the $map operation) these array elements to extract the value of their "v" field. Which in our case results in creating this kind of array: [1, 3, 18, 42].

Finally, it's a simple matter of summing elements within this array, using the $sum operation.

Xavier Guihot
  • 54,987
  • 21
  • 291
  • 190
0

Segment: {s1: 10, s2: 4, s3: 12}

{$set: {"new_array":{$objectToArray: "$Segment"}}}, //makes field names all "k" or "v"
{$project: {_id:0, total:{$sum: "$new_array.v"}}}

"total" will be 26.

$set replaces $addFields in newer versions of mongo. (I'm using 4.2.)

"new_array": [
                {
                    "k": "s1",
                    "v": 10
                },
                {
                    "k": "s2",
                    "v": 4
                },
                {
                    "k": "s3",
                    "v": 12
                }
            ]

You can also use regular expressions. Eg. /^s/i for words starting with "s".

Honeybear65
  • 311
  • 1
  • 10