$lookup 管道中的 $match 始终返回所有文档，而不是过滤 [英] $match in $lookup pipeline always returns all the documents, not filtering

问题描述

我有两个系列.

第一集

 {  _id:"601d07fece769400012f1280",
   FieldName:"Employee",
   Type:"Chapter" }

第二集

_id : "601d11905617082d7049153a",
SurveyId : "601d118e5617082d70491539",
TemplateName : "",
Elements : [

       {
            _id : "601d07fece769400012f1280",
            FieldName : "Employee",
            Type : "Chapter"
       },

       {
          _id : "601d07fece769400012f1281",
          FieldName : "Contract",
          Type : "Chapter"
       }]

当我进行查找时

        '$lookup': {
          'from': 'SecondCollection', 
          'localField': 'FieldName', 
          'foreignField': 'Elements.FieldName', 
          'as': 'SurveyInfo'
        }

我会得到正确的结果，但我得到SecondCollection 匹配管道的 $lookup 阶段中的文档总大小超过 16793600 字节"；有时.

I will get the correct result, but I get the "Total size of documents in SecondCollection matching pipeline's $lookup stage exceeds 16793600 bytes" sometimes.

所以我改变了我的方法，将第二个集合与管道连接起来，所以我只得到了我需要的字段.

So I changed my approach to join the second collection with the pipeline, so I get only the field I need.

 "from": 'SecondCollection',
"let": { "fieldname": "$fieldname" },
"pipeline": [
  { "$match": 
    { "$expr": 
      { "$eq": ["$elements.fieldname", "$$fieldname"] }}},
  { "$project": { "SurveyId": 1}}
],
"as": 'SurveyInfo'

现在的问题是这会返回所有 SecondCollection 文档.不返回匹配的文档.

Now the problem is this returns all the SecondCollection documents. Not returning the matching documents.

我想得到以下结果

_id:"601d07fece769400012f1280",
FieldName:"Employee",
Type:"Chapter",
SurveyInfo: [
      {
       _id:"601d11905617082d7049153a",
       SurveyId:"601d118e5617082d70491539"
      }
 ]

我无法弄清楚这个问题.请帮帮我.

I am not able to figure out the issue. Please help me.

推荐答案

一些修复，

• 您必须尝试 $in 而不是 $eq 因为 $Elements.FieldName 将返回字符串数组
• 需要更正let和$match条件中的字段名称

• You have to try $in instead of $eq because $Elements.FieldName will return array of string
• Need to correct fields name in let and $match condition

db.FirstCollection.aggregate([
  {
    "$lookup": {
      "from": "SecondCollection",
      "let": { "fieldname": "$FieldName" },
      "pipeline": [
        { "$match": { "$expr": { "$in": ["$$fieldname", "$Elements.FieldName"] } } },
        { "$project": { "SurveyId": 1 } }
      ],
      "as": "SurveyInfo"
    }
  }
])

游乐场

要匹配嵌套条件，您可以尝试，

To match nested condition, you can try,

• $reduce 迭代 Elements.Children.FieldName 嵌套数组的循环，我们将使用 $ 将嵌套级别数组合并到单个 sting 数组中concatArrays

• $reduce to iterate loop of Elements.Children.FieldName nested array and we are going to merge nested level array in single array of sting, using $concatArrays

db.FirstCollection.aggregate([
  {
    "$lookup": {
      "from": "SecondCollection",
      "let": { "fieldname": "$FieldName" },
      "pipeline": [
        {
          "$match": {
            "$expr": {
              "$in": [
                "$$fieldname",
                {
                  $reduce: {
                    input: "$Elements.Children.FieldName",
                    initialValue: [],
                    in: { $concatArrays: ["$$this", "$$value"] }
                  }
                }
              ]
            }
          }
        },
        { "$project": { "SurveyId": 1 } }
      ],
      "as": "SurveyInfo"
    }
  }
])

游乐场

这篇关于$lookup 管道中的 $match 始终返回所有文档，而不是过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！