如何在筛选后的查找中将特定字段投影到数组中 [英] How to project specific fields in array on filtered lookup

问题描述

我正在使用$ lookup合并两个集合，并且能够在'joined'集合上应用过滤器，并在开始的集合上使用投影，但是我没有设法在合并的集合上同时合并过滤器和投影集合尝试使用$ redact和$ project的几种方法.我一直在仔细研究stackoverflow，但找不到这种组合. 这里有一个例子: 集合meta:

I am combining two collection using $lookup and I am able to apply a filter on the 'joined' collection and use a projection on the starting collection , but I did not manage to combine both a filter and a projection on the joined collection trying several approach using $redact and $project. I have looked intensively on stackoverflow, but I could not find this combination. Here come an example: collection meta:

{ "Exp": "A","test": "OK","date": "3"}
{ "Exp": "B","test": "OK","date": "5"}
{ "Exp": "C","test": "Failed","date": "9"}

集合合并(待加入):

{ "Exp": "A","M1": "2","M2": "test","T": "1"}
{ "Exp": "A","M1": "2","M2": "val", "T": "2"}
{ "Exp": "A","M1": "2", "M2": "val","T": "3"}
{ "Exp": "B","M1": "1", "M2": "test","M4": "1","T": "1"}
{ "Exp": "B","M1": "1","M2": "val","M4": "1","T": "2"}
{ "Exp": "B","M1": "1","M2": "val","M4": "1","T": "3"}
{ "Exp": "C","M1": "2","M2": "test","M3": "2","T": "1"}
{ "Exp": "C","M1": "2","M2": "val","M3": "2","T": "2"}
{ "Exp": "C","M1": "2","M2": "val","M3": "2","T": "3"}

查询是:使用'Exp'加入'meta'和'merge'，并仅选择meta.test ="OK"和merge.M2 ="val"的那些，但仅显示meta.Exp，meta .test and merge.M1，merge.M2和merge.T.

And the query is: Join 'meta' and 'merge' using 'Exp', and select only those where meta.test="OK" and merge.M2="val", but show only meta.Exp, meta.test and merge.M1, merge.M2, and merge.T.

这是我走了多远:

db.meta.aggregate([
{ $match: { test: "OK" }},
{ $lookup:
  { from: "merge",
    localField: "Exp",
    foreignField: "Exp",
    as: "kin"
  }
 },
 { $project:
   { "Exp": true,
    "test": true,
    kin :
     { $filter:
      { input: "$kin",
        as: "kin",
        cond: { $eq: [ "$$kin.M2", "val" ]} 
      }
     }
    }
  }
])

，但是尝试在merge.M1，merge.M2和merge.T以及过滤器上添加其他投影会导致失败.结果应该是:

but trying to include an additional projection on merge.M1, merge.M2, and merge.T together with the filter keeps failing. The result should be:

{  "Exp" : "B",
  "test" : "OK",
   "kin" : [ 
      {  "M1" : "1",
         "M2" : "val",
         "T" : "2"}, 
      {  "M1" : "1",
         "M2" : "val",
         "T" : "3"}]
 }
 { "Exp" : "A",
   "test" : "OK",
   "kin" : [ 
      { "M1" : "2",
        "M2" : "val",
        "T" : "2"}, 
      { "M1" : "2",
        "M2" : "val",
        "T" : "3"}
    ]
 } 

感谢提示！ 乔迪

推荐答案

由同事提出的另一种方法是使用第二个$ projection.我认为这比$ map更有效，因为它不会遍历数组的每个元素.诀窍是要重复第一个$ projection中的预测.

An alternative way, proposed by a colleaque is using a second $projection. I think this is more efficient than $map, because it does not go over each element of the array. The trick is to repeat the projections from the first $projection.

db.meta.aggregate([
  { $match: { test: "OK" }},
  { $lookup: { from: "merge", localField: "Exp", foreignField: "Exp", as: 
     "kin" }},
  { $project:
    { "Exp": true, "test": true, "date": true,
      kin : { $filter: { input: "$kin", as: "kin", cond: { $eq: [ 
      "$$kin.M2", 
      "val" ]}}
     }
   }
  },
  { $project: { 
    "Exp": true, "test": true, "date": true,
    "kin.M1": true, "kin.M2": true, "kin.T": true }
   }
])

这篇关于如何在筛选后的查找中将特定字段投影到数组中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！