$lookup 的 Foreign 字段可以是嵌套文档的字段吗? [英] The Foreign field of $lookup could be the field of nested document?

问题描述

$lookup 用于执行左外连接到同一数据库中的未分片集合，以从加入"集合中过滤文档以在 Mongo 中进行处理.

$lookup is used to perform a left outer join to an unsharded collection in the same database to filter in documents from the "joined" collection for processing in Mongo.

{
   $lookup:
     {
       from: <collection to join>,
       localField: <field from the input documents>,
       foreignField: <field from the documents of the "from" collection>,
       as: <output array field>
     }
}

foreignField 可能是from 集合的嵌套文档的字段吗?

Could the foreignField be the field of the nested document of from collection?

例如，有如下两个集合.

For example, there are two collections as following.

历史合集

 [{
  id:'001',
  history:'today worked',
  child_id:'ch001'
},{
  id:'002',
  history:'working',
  child_id:'ch004'
},{
  id:'003',
  history:'now working'
  child_id:'ch009'
}],

childsgroup 集合

[{
  groupid:'g001', name:'group1'
  childs:[{
      id:'ch001',
      info:{name:'a'}
  },{
      id:'ch002',
      info:{name:'a'}
  }]
},{
  groupid:'g002', name:'group1'
  childs:[{
      id:'ch004',
      info:{name:'a'}
  },{
      id:'ch009',
      info:{name:'a'}
  }]
}]

那么，这个aggregation代码可以这样执行吗?

so, this aggregation code could be executed like this?

db.history.aggregate([
   {
     $lookup:
       {
         from: "childsgroup",
         localField: "child_id",
         foreignField: "childs.$.id",
         as: "childinfo"
       }
  }
])

所以我想得到这样的结果.

So I want to get the result like this.

     [{
      id:'001',
      history:'today worked',
      child_id:'ch001',
      childinfo:{
          id:'001',
          history:'today worked',
          child_id:'ch001'
       }
    },  .... ]

这不可能吗?

推荐答案

$lookup 没有位置运算符，但您可以在 MongoDB 3.6 中使用自定义 pipeline 来定义自定义连接 条件:

There's no positional operator for $lookup but you can use custom pipeline in MongoDB 3.6 to define custom join conditions:

db.history.aggregate([
    {
        $lookup: {
            from: "childsgroup",
            let: { child_id: "$child_id" },
            pipeline: [
                { $match: { $expr: { $in: [ "$$child_id", "$childs.id" ] } } },
                { $unwind: "$childs" },
                { $match: { $expr: { $eq: [ "$childs.id", "$$child_id" ] } } },
                { $replaceRoot: { newRoot: "$childs" } }
            ],
            as: "childInfo"
        }
    }
])

首先添加 $match 以提高性能:我们只想从 childsgroup 中找到包含匹配 child_id 的那些文档，然后我们可以匹配$unwind 阶段之后的子文档.

First $match added to improve performance: we want to find only those documents from childsgroup that contain matching child_id and then we can match subdocuments after $unwind stage.

这篇关于$lookup 的 Foreign 字段可以是嵌套文档的字段吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！