mongodb - Average a Sub Document Field Across Documents in Mongo

Question

For a given record id, how do I get the average of a sub document field if I have the following in MongoDB:

/* 0 */
{
    "item" : "1",
    "samples" : [ 
        {
            "key" : "test-key",
            "value" : "1"
        }, 
        {
            "key" : "test-key2",
            "value" : "2"
        }
    ]
}

/* 1 */
{
    "item" : "1",
    "samples" : [ 
        {
            "key" : "test-key",
            "value" : "3"
        }, 
        {
            "key" : "test-key2",
            "value" : "4"
        }
    ]
}

I want to get the average of the values where key = "test-key" for a given item id (in this case 1). So the average should be $avg (1 + 3) = 2

Thanks

Answer 1

You'll need to use the aggregation framework. The aggregation will end up looking something like this:

db.stack.aggregate([
  { $match: { "samples.key" : "test-key" } },
  { $unwind : "$samples" },
  { $match : { "samples.key" : "test-key" } },
  { $project : { "new_key" : "$samples.key", "new_value" : "$samples.value" } },
  { $group : { `_id` : "$new_key", answer : { $avg : "$new_value" } } }
])

The best way to think of the aggregation framework is like an assembly line. The query itself is an array of JSON documents, where each sub-document represents a different step in the assembly.

Step 1: $match

The first step is a basic filter, like a WHERE clause in SQL. We place this step first to filter out all documents that do not contain an array element containing test-key. Placing this at the beginning of the pipeline allows the aggregation to use indexes.

Step 2: $unwind

The second step, $unwind, is used for separating each of the elements in the "samples" array so we can perform operations across all of them. If you run the query with just that step, you'll see what I mean. Long story short :

{ name : "bob", 
  children : [ {"name" : mary}, { "name" : "sue" } ] 
} 

becomes two documents :

{ name : "bob", children : [ { "name" : mary } ] }
{ name : "bob", children : [ { "name" : sue } ] }

Step 3: $match

The third step, $match, is an exact duplicate of the first $match stage, but has a different purpose. Since it follows $unwind, this stage filters out previous array elements, now documents, that don't match the filter criteria. In this case, we keep only documents where samples.key = "test-key"

Step 4: $project (Optional)

The fourth step, $project, restructures the document. In this case, I pulled the items out of the array so I could reference them directly. Using the example above..

{ name : "bob", children : [ { "name" : mary } ] }

becomes

{ new_name : "bob", new_child_name : mary }

Note that this step is entirely optional; later stages could be completed even without this $project after a few minor changes. In most cases $project is entirely cosmetic; aggregations have numerous optimizations under the hood such that manually including or excluding fields in a $project should not be necessary.

Step 5: $group

Finally, $group is where the magic happens. The _id value what you will be "grouping by" in the SQL world. The second field is saying to average over the value that I defined in the $project step. You can easily substitute $sum to perform a sum, but a count operation is typically done the following way: my_count : { $sum : 1 }.

The most important thing to note here is that the majority of the work being done is to format the data to a point where performing the operation is simple.

Final Note

Lastly, I wanted to note that this would not work on the example data provided since samples.value is defined as text, which can't be used in arithmetic operations. If you're interested, changing the type of a field is described here: MongoDB How to change the type of a field