Project an element returned with “$arrayElemAt”

问题:

Forgive me if I confuse some terminology here, but I'm performing a join operation in an aggregation using the '$lookup' operator as shown here:

db.collection('items').aggregate([{$match: {}},
{
    $lookup: {
        from: 'usr',
        localField: 'usr._id',
        foreignField: '_id',
        as: '__usr'
    }
}, {
    $project: {
        info: 1,
        timestamp: 1,
        usr: {
            "$arrayElemAt": [ "$__usr", 0 ]
        }
    }
}], (err, result) => {
    res.json(result);
    db.close();
});

I'm performing a projection on the aggregation result, and I'm using '$arrayElemAt' to extract the single 'usr' match from the resulting array. For obvious reasons, I don't want to return the entire 'usr' record which contains sensitive information. What I'm looking to do is perform a projection on the element returned using the '$arrayElemAt' operation. The only way I've been able to accomplish this is to use an additional projection of the original projection, like so:

db.collection('items').aggregate([{$match: {}},
{
    $lookup: {
        from: 'usr',
        localField: 'usr._id',
        foreignField: '_id',
        as: '__usr'
    }
}, {
    $project: {
        info: 1,
        timestamp: 1,
        usr: {
            "$arrayElemAt": [ "$__usr", 0 ]
        }
    }
}, {
    $project: {
        info: 1,
        timestamp: 1,
        usr: {
            "username": 1
        }
    }
}], (err, result) => {
    res.json(result);
    db.close();
});

Is there a way to accomplish this without the duplicated projection?

回答1:

You can assign the $arrayElemAt returned value to a variable using the $let expression and use dot notation to access the subdocument field in the in expression.

"usr": {
    "$let": {
        "vars": { 
            "field": { 
                "$arrayElemAt": [ "$__usr", 0 ]
            }
        },
        "in": "$$field.username"
    }

回答2:

It looks like this is still, as of August 2018, the best way to accomplish the projection of a document joined via $lookup to a single embedded document and filter it's fields is still via a secondary $project pipeline stage. While the method shown by @styvane above does work, and is indeed very novel, it adds even more complexity to the pipeline so I'm hesitant to accept this as the answer.