MongoDB merge array of objects with value update by key

Question

I got stuck with aggregation, where I needed to update analytics values inside the array of objects.

I have an aggregation that produces objects like this:

{
  "key": "product1",
  "analytics": [
    {
      "type": "CLICK",
      "value": 2
    },
    {
      "type": "SELL",
      "value": 19.99
    }

  ]
}

The end of the pipeline:

{
$merge: {
      into: 'analytics',
      on: ['key'],
      whenMatched: [ // <- MIGHT BE HERE SOME PREP CODE USING $$new
        {
          $replaceWith: {
            key: '$key',
            analytics: {} // <- OR HERE?
          }
        }
      ],
      whenNotMatched: 'insert'
    }
}

So, the issue is that this pipeline runs once an hour and collects all new data for analytics. It runs fine when adding new records by the whenNotMatched condition. But I need to update the "value" (summarize the old value with the new one) for each "type" if present and add new "types" if they are absent in the analytics array.

For example:

We have the object:

{
  "key": "product1",
  "analytics": [
    {
      "type": "CLICK",
      "value": 2
    },
    {
      "type": "SELL",
      "value": 19.99
    }

  ]
}

And after another pipeline run, we have:

{
  "key": "product1",
  "analytics": [
    {
      "type": "CLICK",
      "value": 5
    },
    {
      "type": "SELL",
      "value": 29.99
    },
    {
      "type": "ABANDON",
      "value": 1
    }
  ]
}

And upon merge we should have:

{
  "key": "product1",
  "analytics": [
    {
      "type": "CLICK",
      "value": 7
    },
    {
      "type": "SELL",
      "value": 49.98
    },
    {
      "type": "ABANDON",
      "value": 1
    }
  ]
}

Answer 1

A complex query.

1. $map - Iterate each element in the $$new.analytics array.

1.1 $cond - Check the iterated element's type exists in the existing analytics array. If yes, proceed to the 1.1.1 section. Otherwise, return the newItem object.

1.1.1. $first - Return the first document from the result of 1.1.1.1.

1.1.1.1. $map - Iterate the matching element of the existing analytics array, return the element, and overwrite the existing value by adding with new value.

  {
    $merge: {
      into: "analytics",
      on: [
        "key"
      ],
      whenMatched: [
        {
          $replaceWith: {
            key: "$key",
            analytics: {
              $map: {
                input: "$$new.analytics",
                as: "newItem",
                in: {
                  $cond: [
                    {
                      $in: [
                        "$$newItem.type",
                        "$analytics.type"
                      ]
                    },
                    {
                      $first: {
                        $map: {
                          input: {
                            $filter: {
                              input: "$analytics",
                              cond: {
                                $eq: [
                                  "$$this.type",
                                  "$$newItem.type"
                                ]
                              }
                            }
                          },
                          as: "oldItem",
                          in: {
                            $mergeObjects: [
                              "$$newItem",
                              {
                                value: {
                                  $sum: [
                                    "$$oldItem.value",
                                    "$$newItem.value"
                                  ]
                                }
                              }
                            ]
                          }
                        }
                      }
                    },
                    "$$newItem"
                  ]
                }
              }
            }
          }
        }
      ],
      whenNotMatched: "insert"
    }
  }

Besides, this is another version that works with the $let operator:

{
    $merge: {
      into: "analytics",
      on: ["key"],
      whenMatched: [
        {
          $replaceWith: {
            key: "$key",
            analytics: {
              $map: {
                input: "$$new.analytics",
                as: "newItem",
                in: {
                  $cond: [
                    {
                      $in: [
                        "$$newItem.type",
                        "$analytics.type"
                      ],
                    },
                    {
                      $let: {
                        vars: {
                          oldItem: {
                            $first: {
                              $filter: {
                                input:
                                  "$analytics"
                                cond: {
                                  $eq: [
                                    "$$this.type",
                                    "$$newItem.type"
                                  ]
                                }
                              }
                            }
                          }
                        },
                        in: {
                          $mergeObjects: [
                            "$$newItem",
                            {
                              value: {
                                $sum: [
                                  "$$oldItem.value",
                                  "$$newItem.value"
                                ]
                              }
                            }
                          ]
                        }
                      }
                    },
                    "$$newItem"
                  ]
                }
              }
            }
          }
        }
      ],
      whenNotMatched: "insert"
    }
  }