mongodb $lookup 结果中的 $match

$match in $lookup result

提问by AlexBerd

I have next mongo code:

我有下一个 mongo 代码：

db.users.aggregate([
    { 
        $match: { 
            $and: [
                { UserName: { $eq: 'administrator' } }, 
                { 'Company.CompanyName': { $eq: 'test' } }
            ]                   
        } 
    },
    { 
        $lookup: { 
            from: "companies", 
            localField: "CompanyID", 
            foreignField: "CompanyID", 
            as: "Company" 
        } 
    },
])

The $lookuppart of the code working great. I got next result:

$lookup代码的一部分工作得很好。我得到了下一个结果：

But if I add $matchto the code, it brings nothing.

但是如果我添加$match到代码中，它不会带来任何东西。

I found that the problem is in the second match: { 'Company.CompanyName': { $eq: 'test' } }, but I can not realize what is wrong with it. Any ideas?

我发现问题出在第二场比赛中： { 'Company.CompanyName': { $eq: 'test' } }，但我无法意识到它有什么问题。有任何想法吗？

UPDATE:

更新：

I had also tried $unwindon the $lookupresult, but no luck:

我还曾试图$unwind对$lookup结果，但没有运气：

db.users.aggregate([
    { 
        $match: { 
            $and: [
                { UserName: { $eq: 'administrator' } }, 
                { 'Company.CompanyName': { $eq: 'edt5' } }
            ] 
        } 
    },
    {   unwind: '$Company' },
    { 
        $lookup: { 
            from: 'companies', 
            localField: 'CompanyID', 
            foreignField: 'CompanyID', 
            as: 'Company' 
        } 
    },
])

回答by chridam

With MongoDB 3.4, you can run an aggregation pipeline that uses the $addFieldspipeline and a $filteroperator to only return the Companyarray with elements that match the given condition. You can then wrap the $filterexpression with the $arrayElemAtoperator to return a single document which in essence incorporates the $unwindfunctionality by flattening the array.

使用 MongoDB 3.4，您可以运行聚合管道，该$addFields管道使用管道和$filter运算符仅返回Company具有与给定条件匹配的元素的数组。然后，您可以$filter使用$arrayElemAt运算符包装表达式以返回单个文档，该文档实质上$unwind通过展平数组来合并功能。

Follow this example to understand the above concept:

按照这个例子来理解上面的概念：

db.users.aggregate([
    { "$match": { "UserName": "administrator" } },
    { 
        "$lookup": { 
            "from": 'companies', 
            "localField": 'CompanyID', 
            "foreignField": 'CompanyID', 
            "as": 'Company' 
        } 
    },
    {
        "$addFields": {
            "Company": {
                "$arrayElemAt": [
                    {
                        "$filter": {
                            "input": "$Company",
                            "as": "comp",
                            "cond": {
                                "$eq": [ "$$comp.CompanyName", "edt5" ]
                            }
                        }
                    }, 0
                ]
            }
        }
    }
])

回答by Jan Franciszek Cie?lak

Below answer is for mongoDB 3.6 or later.

以下答案适用于 mongoDB 3.6 或更高版本。

Given that:

鉴于：

• You have a collection userswith a field CompanyIDand a collection of companieswith a field CompanyID

• you want to lookup Companieson Usersby matching CompanyID, where additionally:

  • each User must match condition: User.UserNameequals administrator
  • each Companyon Usermust match condition: CompanyNameequals edt5

• 你有收集users与现场CompanyID和集合companies与现场CompanyID

• 要查找Companies有关Users的匹配CompanyID，其中另外：

  • 每个用户必须匹配条件：User.UserName等于administrator
  • 每个Company上User必须符合的条件：CompanyName平等edt5

The following query will work for you:

以下查询对您有用：

  db.users.aggregate([
    { $match: { UserName: 'administrator' } },
    {
      $lookup: {
        from: 'companies',
        as: 'Company',
        let: { CompanyID: '$CompanyID' },
        pipeline: [
          {
            $match: {
              $expr: {
                $and: [
                  { $eq: ['$CompanyID', '$$CompanyID'] },
                  { $eq: ['$CompanyName', 'edt5'] },
                ]
              }
            }
          }
        ]
      }
    },
  ])

Explanation:This is the way to perform left join queries with conditions more complex than simple foreign / local field equality match.

说明：这是在条件比简单的外部/本地字段相等匹配更复杂的情况下执行左连接查询的方法。

Instead of using localFieldand foreignField, you use:

而不是使用localFieldand foreignField，你使用：

• letoption where you can map local fields to variables,
• pipelineoption where you can specify aggregation Array.

• let您可以将本地字段映射到变量的选项，
• pipeline您可以在其中指定聚合的选项Array。

In pipelineyou can use $matchfilter, with $expr, where you can reuse variables defined earlier in let.

在pipeline你可以使用$match过滤器，带$expr，在那里你可以重复使用先前在定义的变量let。

More info on $lookup

有关 $lookup 的更多信息

Nice tutorial

不错的教程