Mongo create field with merge of 2 arrays of same collection (like Left join in relational)

Question

I have a collection that has 2 arrays inside. One has fewer props than other, but both are linked by prop 'Code'.

I would like to project a 3rd array that would be the merge of both, but they must be distinct. An example:

[
  {
    'PartnerName': "Company A - Both Complete Equal",
    'MarketExpertese1': [
      { 'Sector': { 'Code': 1, 'Label': 'Sector 1' } },
      { 'Sector': { 'Code': 2, 'Label': 'Sector 2' } }
    ],
    'MarketExpertese2': [
      { 'Sector': { 'Code': 1, 'TotalDays': 1 } },
      { 'Sector': { 'Code': 2, 'TotalDays': 1 } }
    ]
  },
    {
    'PartnerName': "Company B - 1 has more",
    'MarketExpertese1': [
      { 'Sector': { 'Code': 1, 'Label': 'Sector 1' } },
      { 'Sector': { 'Code': 2, 'Label': 'Sector 2' } },
      { 'Sector': { 'Code': 3, 'Label': 'Sector 3' } }
    ],
    'MarketExpertese2': [
      { 'Sector': { 'Code': 1, 'TotalDays': 1 } },
      { 'Sector': { 'Code': 2, 'TotalDays': 1 } }
    ]
  },
  {
    'PartnerName': "Company C - 2 has more",
    'MarketExpertese1': [
      { 'Sector': { 'Code': 1, 'Label': 'Sector 1' } },
      { 'Sector': { 'Code': 2, 'Label': 'Sector 2' } }
    ],
    'MarketExpertese2': [
      { 'Sector': { 'Code': 1, 'TotalDays': 1 } },
      { 'Sector': { 'Code': 2, 'TotalDays': 1 } },
      { 'Sector': { 'Code': 3, 'TotalDays': 1 } }
    ]
  },
  {
    'PartnerName': "Company D - 1 has - 2 Null",
    'MarketExpertese1': [
      { 'Sector': { 'Code': 1, 'Label': 'Sector 1' } }
    ],
    'MarketExpertese2': null
  },
  {
    'PartnerName': "Company E - 1 null - 2 has",
    'MarketExpertese1': null,
    'MarketExpertese2': [
      { 'Sector': { 'Code': 1, 'TotalDays': 1 } }
    ]
  }
]

Here on Company A both arrays have the same number of itens, so the 3rd array would be the merge, the result would be (just added prop TotalDays to array1):

'MarketExpertese3': [
      { 'Sector': { 'Code': 1, 'Label': 'Sector 1', 'TotalDays': 10 } },
      { 'Sector': { 'Code': 2, 'Label': 'Sector 2', 'TotalDays': 20 } }
    ]

In this example both arrays have the same len and the same codes, but sometimes they are different. Some time one is null and other not, sometimes empty, etc...

Sometimes I will have to add TotalDays:0 when array1 has more itens the array 2. Sometimes I will have to populate prop from array 1 into array 2.

Looking arround I am lost, I don´t know if I need to unwind/group/map or unwind/reduce/group/map... I get lost when I need to verify the prop Code in both to check if I need to get array 1 and merge into 2 or or vice versa

Here is a playgrod with the data

cheers

Answer 1

• $set check if any array id null then update to empty array [], for making clear operation

db.collection.aggregate([
  {
    $set: {
      MarketExpertese1: { $ifNull: ["$MarketExpertese1", []] },
      MarketExpertese2: { $ifNull: ["$MarketExpertese2", []] }
    }
  },

• Prepare third array MarketExpertese3, iterate loop using $map of array MarketExpertese2 and sync with MarketExpertese1, $reduce to iterate loop of MarketExpertese1 and get matching Sector object and merge with MarketExpertese2's object using $mergeObjects

  {
    $addFields: {
      MarketExpertese3: {
        $map: {
          input: "$MarketExpertese2",
          as: "m",
          in: {
            Sector: {
              $mergeObjects: [
                "$$m.Sector",
                {
                  $reduce: {
                    input: "$MarketExpertese1",
                    initialValue: {},
                    in: {
                      $cond: [
                        { $eq: ["$$m.Sector.Code", "$$this.Sector.Code"] },
                        "$$this.Sector",
                        "$$value"
                      ]
                    }
                  }
                }
              ]
            }
          }
        }
      }
    }
  },

• Now we need to sync MarketExpertese1 array to MarketExpertese3 newly prepared array,
• $map to iterate loop of MarketExpertese1 array and check of any status is new then return otherwise return null, $filter to remove null from array, concat return array with MarketExpertese3 using $concatArrays

  {
    $addFields: {
      MarketExpertese3: {
        $concatArrays: [
          "$MarketExpertese3",
          {
            $filter: {
              input: {
                $map: {
                  input: "$MarketExpertese1",
                  as: "m",
                  in: {
                    $cond: [
                      {
                        $eq: [
                          {
                            $filter: {
                              input: "$MarketExpertese2",
                              cond: { $eq: ["$$this.Sector.Code", "$$m.Sector.Code"] }
                            }
                          },
                          []
                        ]
                      },
                      "$$m",
                      "$$REMOVE"
                    ]
                  }
                }
              },
              cond: { $ne: ["$$this", null] }
            }
          }
        ]
      }
    }
  }
])

Playground