Observed issues in $merge unable resolve

Working with Data
1

In my use case I’m working with below json format document

{ id1:101,
 id2:1011, 
 id3:1012, 
totals:[ 
         {
        date: ISODate('2020-01-01') ,
         amt: 123, 
        counts: 12}, {date: ISODate('2020-01-02'), amt: 111, counts: 13},  ........]}

Objective is to Update the ‘amt’ and ‘counts’ for which there is a match in ‘date’ in the HISTORY collection. Data from another collection named DELTA, which has exactly same document structure and contains delta data i.e. containing updates for the other fields on the ‘date’ which is present in HISTROY and new dates as well.

Expectation is while merging the data from DELTA to HISTORY it will update the other fields in ‘totals’ array where there is a match between the dates and add new element in array for the new ‘date’ while keeping the other historical data as it is.
Below is my query

db.delta.aggregate([ {$unset:"_id"},
{$merge:{
  into: 'history',
  on: ['id1','id2','id3'],
  whenMatched: 'merge',
  whenNotMatched: 'insert'
}}])

The “whenMatched: ‘merge’” is not working expected. In the DOCS nothing is mentioned about use of arrays for merge .

Do you have any suggestions ??

2

Hello @sukanya_ghosh,

The way to match array elements of the delta and the history collections is, I think, by comparing elements in the arrays. This needs usage of an Aggregation Pipeline option for the whenMatched field of the $merge. The pipeline iterates over the two arrays and does the matching, updating and adding new elements.

I have some sample data, the aggregation merge operation (and it works), and the updated history collection.

The input collections:

delta:

{ _id: 1,
  id1:101,
  id2:1011, 
  id3:1012, 
  totals: [ 
         { date: '2020-01-01' , amt: 4, counts: 2 }, 
         { date: '2020-01-02', amt: 3, counts: 5 },
         { date: '2020-01-03', amt: 11, counts: 11 }
     ]
}

hist:

{ _id: 9,
  id1:101,
  id2:1011, 
  id3:1012, 
  totals: [ 
         { date: '2020-01-01' , amt: 2, counts: 4 }, 
         { date: '2020-01-02', amt: 3, counts: 10 },
         { date: '2020-01-04', amt: 9, counts: 9 }
     ]
}

The aggregation with the merge operation:

NOTE: Before running the following aggregation, make sure there must be unique indexes created on the on field properties of both the collections. That is create a compound unique index: { id1: 1, id2: 1, id3: 1 }.

db.delta.aggregate([ 
  { 
      $merge: {
          into: "hist",
          on: [ "id1", "id2", "id3" ],
          whenMatched: pipe
      }
  }
])

The updated hist collection:

{ _id: 9,
  ...,
  totals: [
    { date: '2020-01-01' , amt: 6, counts: 6 },
    { date: '2020-01-02', amt: 6, counts: 15 },
    { date: '2020-01-03', amt: 11, counts: 11 },
    { date: '2020-01-04', amt: 9, counts: 9 },
  ]
}

NOTE: The order of the elements is not the same as the input.

The pipeline used with the merge operation:

pipe = [
  { 
      $addFields: { 
          newDelta: { 
              $filter: { 
                   input: "$$new.totals",
                   as: "dtot",
                   cond: {
                       $eq: [ 
                           { $size: { 
                                  $filter: { input: "$totals", 
                                             as: "htot", 
                                             cond: { $eq: [ "$$dtot.date", "$$htot.date" ]  } 
                                  } 
                           } }, 
                       0 ]
                   }
              }
          }
      }
  },
  { 
      $addFields: { 
          newDelta: "$$REMOVE", 
          totals: { 
              $reduce: { 
                  input: "$totals", 
                  initialValue:  "$newDelta",
                  in: {
                      $let: {
                          vars: { 
                              match: { 
                                  $arrayElemAt: [ 
                                      { $filter: { 
                                             input: "$$new.totals", 
                                             as: "dtot", 
                                             cond: { $eq: [ "$$this.date", "$$dtot.date" ]  } 
                                      } },
                                  0 ]
                              }
                        },
                        in: { 
                            $cond: [ 
                                { $eq: [ { $ifNull: [ "$$match", "" ] } , "" ] },
                                { $concatArrays: [ "$$value", [ "$$this" ] ] },
                                { $concatArrays: [ "$$value",
                                                   [ { date: "$$this.date",
                                                       amt: { $add: [ "$$this.amt", "$$match.amt" ] },
                                                       counts: { $add: [ "$$this.counts", "$$match.counts" ] }
                                                   } ]
                                ] }
                            ]
                        }
                    }
                }
            }
        }
     }
  }
]