$project a new field as the minimum of two fields in mongodb

Question

As part of an aggregation pipeline, I'd like to project a new field onto a document which is the minimum of two existing fields.

Given documents like these:

{
    _id: "big1",
    size: "big",
    distances: { big: 0, medium: 0.5, small: 1 }
}
{
    _id: "med1",
    size: "medium",
    distances: { big: 0.5, medium: 0, small: 0.5 }
}
{
    _id: "small1",
    size: "small",
    distances: { big: 1, medium: 0.5, small: 0 }
}

The "distances" subdocument shows how "far" the document's size is from other possible sizes.

I'm looking to accumulate a sorting score for the documents that shows how close it is to a set of parameters. If I were looking for just "big" documents, I could do this:

aggregate([
    {$project: {"score": "$distances.big"}}
    {$sort: {"score": 1}}
]);

But suppose I want to sort equally for "big" or "medium" documents. What I want is something like:

aggregate([
    {$project: {"score": {$min: ["$distances.big", "$distances.medium"]}}},
    {$sort: {"score": 1}}
])

But this doesn't work, as $min only operates on adjacent documents in a $group query.

Is there a way to project a value that is the minimum of two existing fields as a sort parameter?

Answer 1

You can use the $cond operator to perform a comparison that finds the minimum value using the $lt operator:

db.test.aggregate([
    {$project: {score: {$cond: [
        {$lt: ['$distances.big', '$distances.medium']}, // boolean expression
        '$distances.big',   // true case
        '$distances.medium' // false case
    ]}}},
    {$sort: {score: 1}}
])

Result:

[ 
    {
        "_id" : "big1",
        "score" : 0
    }, 
    {
        "_id" : "med1",
        "score" : 0
    }, 
    {
        "_id" : "small1",
        "score" : 0.5
    }
]