从 $Lookup 在 Mongodb 中合并两个数组对象 [英] Merge two array objects together in Mongodb from $Lookup

问题描述

我有两个 Mongodb 3.6 集合，其中一个引用另一个作为下面的示例数据，其中 certs.$Entities.Id: entity._id:

I have two Mongodb 3.6 collections where one references another as an example data below where certs.$Entities.Id: entities._id:

certs: {
    _id: ObjectId('xxx'),
    Category: "Certification",
    Entities: [{Id: ObjectId('abc'), prev: true, Own_type: "Contact"}
              {Id: ObjectId('def), prev: false, Own_Type: "Operator"}]
    ...bunch more fields
}

另一个:

entities: {_id: ObjectId('abc'), 
           FName: 'Bob', 
           LName: 'Smith', 
           ...bunch more fields}, 
          {_id: ObjectId('def'), 
           FName: 'Bill', 
           LName: 'Jones',
           ...bunch more fields}

我想使用简单的查找从实体获取信息到证书中，通常会这样做，但它会生成两个对象或覆盖 cert.Entities.这个问题是我需要能够保留 cert.Entities 中的数据并将其与 entities 合并.

I want to use a simple lookup to get the information from the entities into certs and would typically do that, but it produces two objects or overwrites cert.Entities. This issue is that I need to be able to preserve the data within the cert.Entities and merge that with entities.

想要的结果是:

certs: {
    _id: ObjectId('xxx'),
    Category: "Certification",
    Entities: [{Id: ObjectId('abc'), 
                prev: true, 
                Own_type: "Contact",
                FName: 'Bob', 
                LName: 'Smith', 
                ...bunch more fields}
              {Id: ObjectId('def), 
               prev: false, 
               Own_Type: "Operator",
               FName: 'Bill', 
               LName: 'Jones',
               ...bunch more fields
               }]
    ...bunch more fields
}

我通过执行以下聚合完成了此操作，但它看起来不对，因为我必须在结果中需要的几个字段上使用 $first 累加器.

I have accomplished this by doing the following aggregation, but it doesn't look right as I have to use the $first accumulator on several fields I need to have in the result.

certs.aggregate([{
    $unwind: {
      path: '$Entities',
      preserveNullAndEmptyArrays: false
    }
  },
  {
    $lookup: {
      from: 'entities',
      localField: 'Entities.Id',
      foreignField: '_id',
      as: 'Ent'
    }
  },
  {
    $unwind: {
      path: '$Ent',
      preserveNullAndEmptyArrays: false
    }
  },
  {
    $addFields: {
      Ent2: {
        $mergeObjects: ['$Ent', '$Entities']
      }
    }
  },
  {
    $group: {
      _id: '$_id',
      Entities: {
        $push: '$Ent2'
      },
      Cert_ID: {
        $first: '$Cert_ID'
      },
      Cert_Details: {
        $first: '$Cert_Details'
      },
      Allocations: {
        $first: '$Allocations'
      },
      Flowmeters: {
        $first: '$Flowmeters'
      },
      Category: {
        $first: '$Category'
      },
      Water_Sources: {
        $first: '$Water_Sources'
      },
      Active: {
        $first: '$Active'
      },
      County: {
        $first: '$County'
      },
      Legal: {
        $first: '$Legal'
      },
      GWMA: {
        $first: '$GWMA'
      },
      Alias: {
        $first: '$Alias'
      },
      Usage: {
        $first: '$Usage'
      }
    }
  }])

有人可以给我一些建议来改进这个汇总或给我一个替代管道吗?

Can someone give me some advice to improve on this aggregate or give me an alternate pipeline?

推荐答案

$lookup<之后需要$map 和 $filter 来替换加入的实体/代码>

you need to $map and $filter to replace joined entities after $lookup

db.certs.aggregate([
    {$match : {_id : "xxx"}}, 
    {$lookup : {from:"entities", localField:"Entities",foreignField:"_id", as: "joins"}},
    {$addFields : {Entities: 
        {$map : {
            input : "$Entities", 
            as : "e", 
            in : {$arrayElemAt :[{
                $filter : {
                    input : "$joins", 
                    as : "j", 
                    cond : {$eq :["$$e", "$$j._id"]}
                }},0]}
            }}
    }},
    {$project : {joins:0}}
]).pretty()

EDIT-1

保留两个数组元素的字段

to preserve fields from both the array elements

db.certs.aggregate([
    {$match : {_id : "xxx"}}, 
    {$lookup : {from:"entities", localField:"Entities._id",foreignField:"_id", as: "joins"}},
    {$addFields : {Entities: 
        {$map : {
            input : "$Entities", 
            as : "e", 
            in : {$mergeObjects: [
                "$$e",
                {$arrayElemAt :[{$filter : {input : "$joins",as : "j", cond : {$eq :["$$e", "$$j._id"]}}},0]}
                ]
            }}}
    }},
    {$project : {joins:0}}
]).pretty()

这篇关于从 $Lookup 在 Mongodb 中合并两个数组对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！