Average of 3 fields in same document

Question

I have documents stored in my database with exactly the same 'schema'. Each document has three float values (keys are 'A', 'B' and 'C') and what I need to do is output each document projected as the average of all three.

I also need to divide by 100, so my calculation is (A + B + C)/3/100. As far as I understand, I need to project each document as a different output, but $group is no use here (since I'm not averaging across all documents, just each one).

I need to first be able to write this as a MongoDB command, then afterwards translate it to PHP. I think I'll be able to do the PHP part but what I need is a bit of help getting started on the actual MongoDB command...

db.measurements.aggregate(   )

My data is stored as follows:

{ "_id" : ObjectId(xxx), "A": 22.34, "B": 23.32, "C": 75.32, "device_id": 3 }
{ "_id" : ObjectId(xxx), "A": 22.34, "B": 23.32, "C": 75.32, "device_id": 3 }
{ "_id" : ObjectId(xxx), "A": 22.34, "B": 23.32, "C": 75.32, "device_id": 3 }

When I submit a get request, I need my data to come out as follows:

{ "_id" : ObjectId(xxx), "Average of A,B,C": 40.50, "device_id": 3 }
{ "_id" : ObjectId(xxx), "Average of A,B,C": 40.50, "device_id": 3 }
{ "_id" : ObjectId(xxx), "Average of A,B,C": 40.50, "device_id": 3 }

After some further research, it seems aggregate is not the method I should be using here? Would it be better if I calculated the average in my php model prior to insertion into my database, and just retrieve my documents via the find() method?

Answer 1

What you essentially need is a single $project piped aggregation - it will calculate the average for you using the arithmetic operators available and will modify the result within the same pipeline to get the field as an expression of the calculation ((A + B + C)/3)/100 (if you want a percentage then you need to multiply instead of dividing i.e. calculation is ((A + B + C)/3)*100). Summarily explained by the following operation:

db.measurements.aggregate([
    {
        "$project": {               
            "device_id": 1,
            "Average of A,B,C": {
                "$divide": { // ( A + B + C )/3 )/100
                    {
                        "$divide": [ // (A + B + C )/3
                            { "$add": [ "$A", "$B", "$C" ] }, // A + B + C
                            3
                        ]
                    },
                    100
                }
            }
        }
    }
])

That being said, it's best to do heavy or complex computational tasks in the application layer and let mongo best do its data persistence tasks.

Answer 2

Starting from MongoDB 3.2, we can use the $avg accumulator operator with the square brackets [] to directly create new array fields in the $project stage to do this efficiently.

Of course we also need the $divide arithmetic aggregation operator to divide the value by 100.

db.measurements.aggregate(
    [ 
        { "$project": { 
            "result": { 
                "$divide": [
                    { "$avg": [ "$a", "$b", "$c" ] }, 
                    100 
                ] 
            } 
        }} 
    ]
)

Translation in PHP gives:

db.measurements.aggregate(
    array(
        array("$project" => array( 
            "result" => array( 
                "$divide" => array(
                    "$avg" => array("$a", "$b", "$c"), 
                    100 
                )
            )
        ))
    )
)