How do you sum one field by another field in an array of objects after grouping with dateTrunc?-CodePudding

I'm using a time series collection in mongodb. The collection stores error counts for various APIs with the granularity of 1 minute. The API names are unknown and can change, so ideally you would not use a query that relies on static API names. Here's an example dataset:

[
  {
    _time: ISODate("2022-03-22T00:00:00.000Z"),
    errors: [
      {
        api: "shipping",
        count: 10
      },
      {
        api: "inventory",
        count: 100
      }
    ]
  },
  {
    _time: ISODate("2022-03-22T00:01:00.000Z"),
    errors: [
      {
        api: "shipping",
        count: 20
      },
      {
        api: "inventory",
        count: 200
      }
    ]
  },
  {
    _time: ISODate("2022-03-22T00:02:00.000Z"),
    errors: [
      {
        api: "inventory",
        count: 300
      }
    ]
  },
  {
    _time: ISODate("2022-03-22T00:03:00.000Z"),
    errors: [
      {
        api: "inventory",
        count: 400
      },
      {
        api: "account",
        count: 1
      }
    ]
  }
]

I'm able to group into the correct time buckets using

db.collection.aggregate([
  {
    $group: {
      _id: {
        $dateTrunc: {
          date: "$_time",
          unit: "minute",
          binSize: 2
        }
      }
    }
  }
])

I want to be able to:

Group documents into time buckets (dateTrunc).
Sum the error counts in each time bucket.

The result should be:

[
  {
    _time: ISODate("2022-03-22T00:00:00.000Z"),
    errors: [
      {
        api: "shipping",
        count: 30
      },
      {
        api: "inventory",
        count: 300
      }
    ]
  },
  {
    _time: ISODate("2022-03-22T00:02:00.000Z"),
    errors: [
      {
        api: "inventory",
        count: 700
      },
      {
        api: "account",
        count: 1
      }
    ]
  }
]

Or something like this could work too:

[
  {
    _time: ISODate("2022-03-22T00:00:00.000Z"),
    shipping: 30,
    inventory: 300
  },
  {
    _time: ISODate("2022-03-22T00:02:00.000Z"),
    inventory: 700,
    account: 1 
  }
]

CodePudding user response：

You are in the right direction.

Output 1

$set - Add truncTime field.
$unwind - Deconstruct errors array into multiple documents.
$group - Group by truncTime and errors.api. And perform sum for errors.count.
$group - Group by _id.truncTime.
$project - Decorate the output document.

db.collection.aggregate([
  {
    $set: {
      truncTime: {
        $dateTrunc: {
          date: "$_time",
          unit: "minute",
          binSize: 2
        }
      }
    }
  },
  {
    $unwind: "$errors"
  },
  {
    $group: {
      _id: {
        _time: "$truncTime",
        api: "$errors.api"
      },
      count: {
        $sum: "$errors.count"
      }
    }
  },
  {
    $group: {
      _id: "$_id._time",
      errors: {
        $push: {
          api: "$_id.api",
          count: "$count"
        }
      }
    }
  },
  {
    $project: {
      _id: 0,
      _time: "$_id",
      errors: 1
    }
  }
])

Sample Mongo Playground (Output 1)

Output 2

$set - Same as above stage 1.
$unwind - Same as above stage 2.
$group - Same as above stage 3.
$group - Same as above step 4. Modify the fields in the errors array as k and v.
$replaceRoot - Replace input document to new document. Merge each document with key-value pair of errors array.
$unset - Remove _id and errors fields.

  {
    $group: {
      _id: "$_id._time",
      errors: {
        $push: {
          k: "$_id.api",
          v: "$count"
        }
      }
    }
  },
  {
    $replaceRoot: {
      newRoot: {
        $mergeObjects: [
          "$$ROOT",
          {
            _time: "$_id"
          },
          {
            $arrayToObject: "$errors"
          }
        ]
      }
    }
  },
  {
    $unset: [
      "_id",
      "errors"
    ]
  }

Sample Mongo Playground (Output 2)