筛选$ lookup结果

问题描述

我有2个收藏夹(带有示例文档):

I have 2 collections (with example documents):

报告

{
    id: "R1",
    type: "xyz",
}

报告文件

{
    id: "F1",
    reportid: "R1",
    time: ISODate("2016-06-13T14:20:25.812Z")
},
{
    id: "F14",
    reportid: "R1",
    time: ISODate("2016-06-15T09:20:29.809Z")
}

如您所见，一个report可能有多个reportfiles.

As you can see one report may have multiple reportfiles.

我想执行查询，匹配报告id，按原样返回报告文档，以及一个附加的键，该键将具有最新time的reportfile作为子文档存储(如果没有，因为这可能是多余的)，例如

I'd like to perform a query, matching a report id, returning the report document as is, plus an additional key storing as subdocument the reportfile with the most recent time (even better without reportid, as it would be redundant), e.g.

{
    id: "R1",
    type: "xyz",
    reportfile: {
        id: "F14",
        reportid: "R1",
        time: ISODate("2016-06-15T09:20:29.809Z")
    }
}

我的问题是每种报告类型都有其自己的属性集，因此在聚合管道中使用$project并不是最好的方法.

My problem here is that every report type has its own set of properties, so using $project in an aggregation pipeline is not the best way.

到目前为止我知道了

db.reports.aggregate([{
            $match : 'R1'
        }, {
            $lookup : {
                from : 'reportfiles',
                localField : 'id',
                foreignField : 'reportid',
                as : 'reportfile'
            }
        }
    ])

当然会将带有给定reportid的所有文件的列表作为"reportfile"返回.如何有效过滤该列表以获取所需的唯一元素?

returning of course as ´reportfile´ the list of all files with the given reportid. How can I efficiently filter that list to get the only element I need?

有效地->我尝试使用$unwind作为下一个管道步骤，但是生成的文档太长了，而且毫无意义.

efficiently -> I tried using $unwind as next pipeline step but the resulting document was frighteningly and pointlessly long.

提前感谢您的任何建议！

Thanks in advance for any suggestion!

推荐答案

您需要添加另一个之后，"nofollow"> $project 过渡到您的聚合管道$lookup 阶段.

You need to add another $project stage to your aggregation pipeline after the $lookup stage.

{ "$project": {
    "id": "R1",
    "type": "xyz",
    "reportfile": {
        "$let": {
            "vars": {
                "obj": {
                    "$arrayElemAt": [
                        { "$filter": {
                            "input": "$reportfile",
                            "as": "report",
                            "cond": { "$eq": [ "$$report.time", { "$max": "$reportfile.time" } ] }
                        }},
                        0
                    ]
                }
            },
            "in": { "id": "$$obj.id", "time": "$$obj.time" }
        }
    }
}}

$filter 运算符过滤" 结果并返回一个满足您条件的文档数组.这里的条件是 $eq ，当文档包含 $max 最大值.

The $filter operator "filter" the $lookup result and return an array with the document that satisfy your condition. The condition here is $eq which return true when the document has the $maximum value.

$arrayElemAt 运算符 slice $ filter 的结果，然后使用 $let 运算符.在这里，您可以使用 点符号 .

The $arrayElemAt operator slice the $filter's result and return the element from the array that you then assign to a variable using the $let operator. From there, you can easily access the field you want in your result with the dot notation.

这篇关于筛选$ lookup结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！