Accessing a value in an embedded document in an array

Question

Consider a document containing an array of embedded documents:

{'array': [{'key1': 120.0, 'key2': 69.0}, {'key1': 100.0, 'key2': 50.0}]}

I want to project key2 for the first element of the array.

I naively tried

'$project':
    {
        'item': '$array.0.key2'
    }

which fails (but explains what I want to do better than many words).

Using $arrayElemAt and $let

Since MongoDB 3.2, it is possible to get an item from the list using $arrayElemAt:

'$project':
    {
        'item1': {'$arrayElemAt': ['$array', 0] }
    }

will return item1 as {'key1': 120.0, 'key2': 69.0}.

What I want key2 in there.

I managed to get it using $let:

'$project':
    {
        'item': {
            '$let': {
                'vars': {
                    'tmp': {'$arrayElemAt': ['$array', 0] },
                    },
                'in': '$$tmp.key2'
            }
        },
    }

Is there a simpler way?

This seems painfully verbose. Especially considering I'd like to use this construction in several expressions for the same value (test not zero, then use in division) in the same projection.

 The context

The array stores the successive states of the object represented in the document, sorted by date, reverse order. The 1st element of the array is the last (therefore current) state. I want to sort the documents using a ratio of two values in the current state.

It is possible that the only reasonable solution would be to get the last state out of the array. Or even to pre-calculate the ratio and sort on the pre-calculated value.

Answer 1

In an aggregation pipeline you can repeat steps to keep things cleaner so after:

'$project': {'item1': {'$arrayElemAt': ['$array', 0] }}

you can add:

{$project: {"item1.key2": 1}}

Answer 2

Actually you don't need to use the $let operator to do this. Simply use the $arrayElemAt operator in your $project stage to return the first item in your "array" and the $$ROOT system variable to return the current document. From there you can easily specify the field to $sort by using the dot notation. Additionally you can add another $project stage to the pipeline to discard the "item" field and the "_id" field from the query result.

db.collection.aggregate([
    { "$project": { 
        "item": { "$arrayElemAt": [ "$array", 0 ] },  
        "doc": "$$ROOT"
    }}, 
    { "$sort": { "item.key2": 1 } }, 
    { "$project": { "doc": 1, "_id": 0 } }
])