I am trying to update documents values, if they already exist in a collection, if they have the same value for a specific object key.

For example, I have the following documents -

{ eventType: "A", browsers: [ { name: "Chrome", count: 10 } ]}
{ eventType: "B", browsers: [ { name: "Chrome", count: 2 } ]}
{ eventType: "A", browsers: [ { name: "Chrome", count: 5 }, { name: "Safari", count: 8 } ]}

In the end, I want the collection to merge documents by their event type, and merge browsers aggregations by the browser names value (in the example below, the "Chrome" count is combined to 15); if value does not exist, add the new object to the browsers array (In the example below, adding the "Safari" object to the existing browsers aggregation):

{ eventType: "A", browsers: [ { name: "Chrome", count: 15 }, { name: "Safari", count: 8 } ]}
{ eventType: "B", browsers: [ { name: "Chrome", count: 2 } ]}

I want to use the following query for updating and inserting a new document if needed, but I am not sure how the browser aggregation can be made.

const doc = { eventType: "A", browsers: [ { name: "Chrome", count: 5 }, { name: "Safari", count: 8 } ]}

collection.findOneAndUpdate({
    eventType: doc.eventType
  }, {

    // What to do in here?

  }, {
    upsert: true,
    new: true
  })

Is there any option to use a custom function, such as the $accumulator in the collection.aggregate use case?

P.S - I am using mongoose for this code

CodePudding user response：

It is not possible to do this in one statement, since your goal is to collect the documents into a new set of documents by eventType, but you can get much of the work done with an aggregation with a merge:

[{$unwind: {
 path: '$browsers',
 includeArrayIndex: 'b',
 preserveNullAndEmptyArrays: true
}}, {$group: {
 _id: '$eventType',
 eventType: {
  $first: '$eventType'
 },
 browsers: {
  $addToSet: '$browsers'
 }
}}, {$addFields: {
 grouped: 1
}}, {$merge: {
 into: 'so_example',
 on: '_id',
 whenMatched: 'replace',
 whenNotMatched: 'discard'
}}]

This would require a second process to a) remove documents that weren't merged and something to reduce the browsers array so that each browser had the sum of the counts.

CodePudding user response：

UPDATE

Create a combined object with all the keys and its data followed by multiple groups to count the occurrences.

Final step to transform back to individual key fields and with updated data.

Working example here - https://mongoplayground.net/p/0ICHoflOke4

db.collectionname.aggregate([
  {
    "$project": {
      "eventType": 1,
      "categories": {
        "$objectToArray": {
          "$mergeObjects": [
            {
              "browsers": "$browsers"
            },
            {
              "os": "$os"
            }
          ]
        }
      }
    }
  },
  {
    "$unwind": "$categories"
  },
  {
    "$unwind": "$categories.v"
  },
  {
    "$group": {
      "_id": {
        "eventType": "$eventType",
        "categoryType": "$categories.k",
        "name": "$categories.v.name"
      },
      "count": {
        "$sum": "$categories.v.count"
      }
    }
  },
  {
    "$group": {
      "_id": {
        "eventType": "$_id.eventType",
        "categoryType": "$_id.categoryType"
      },
      "category": {
        "$push": {
          "name": "$_id.name",
          "count": "$count"
        }
      }
    }
  },
  {
    "$group": {
      "_id": "$_id.eventType",
      "categories": {
        "$mergeObjects": {
          "$arrayToObject": [
            [
              [
                "$_id.categoryType",
                "$category"
              ]
            ]
          ]
        }
      }
    }
  },
  {
    "$set": {
      "eventType": "$_id"
    }
  },
  {
    "$project": {
      "_id": 0
    }
  },
  {
    "$replaceRoot": {
      "newRoot": {
        "$mergeObjects": [
          "$categories",
          "$$ROOT"
        ]
      }
    }
  },
  {
    "$project": {
      "categories": 0
    }
  },
  {
    "$out": "collectionname"
  }
])

You could use $out aggregation operator to aggregate and overide the collection with the updated data.

Working example here - https://mongoplayground.net/p/OsY3PgiVIfE

db.collectionname.aggregate([
  {
    "$unwind": "$browsers"
  },
  {
    "$group": {
      "_id": {
        "eventType": "$eventType",
        "name": "$browsers.name"
      },
      "count": {
        "$sum": "$browsers.count"
      }
    }
  },
  {
    "$group": {
      "_id": "$_id.eventType",
      "browsers": {
        "$push": {
          "name": "$_id.name",
          "count": "$count"
        }
      }
    }
  },
  {
    "$set": {
      "eventType": "$_id"
    }
  },
  {
    "$project": {
      "_id": 0
    }
  },
  {
    "$out": "collectionname"
  }
])