'Merge two array objects together in Mongodb from $Lookup

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
}

And the other: 

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

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.

The desired result would be: 

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
}

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?

mongodb


Solution 1:[1]

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()

Solution 2:[2]

The example on top worked for me. It solved the problem!

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 iglizar

Related Questions