mongodb aggregate subtract by key

Question

DB : mongoDB

i have following query result.

(ex1)
[
  {
    "array": [
      {
        "_id": 1,
        "createdAt": NumberLong(1675408092026),
        "pType": "A"
      },
      {
        "_id": 2,
        "createdAt": NumberLong(1675408097462),
        "pType": "B"
      },
      {
        "_id": 3,
        "createdAt": NumberLong(1675408101259),
        "pType": "A"
      },
      {
        "_id": 4,
        "createdAt": NumberLong(1675408104682),
        "pType": "B"
      }
    ]
  }
]

OR

(ex2)
[
  {
    "array": [
      {
        "_id": 1,
        "createdAt": NumberLong(1675408092026),
        "pType": "A"
      },
      {
        "_id": 2,
        "createdAt": NumberLong(1675408095026),
        "pType": "A"
      },
      {
        "_id": 3,
        "createdAt": NumberLong(1675408097462),
        "pType": "B"
      },
      {
        "_id": 4,
        "createdAt": NumberLong(1675408101259),
        "pType": "A"
      },
      {
        "_id": 5,
        "createdAt": NumberLong(1675408104682),
        "pType": "B"
      },
      {
        "_id": 6,
        "createdAt": NumberLong(1675408108682),
        "pType": "B"
      },
      {
        "_id": 7,
        "createdAt": NumberLong(1675408118682),
        "pType": "A"
      }
    ]
  }
]

I want to subtract the 'createdAt' value of pType 'A' from the 'createdAt' value of 'B'
And I want to add up the subtracted value.

(ex2)
1675408097462(_id:2) - 1675408092026(_id:1)
+
1675408104682(_id:4) - 1675408101259(_id:3)

(ex2)
1675408097462(_id:3) - 1675408095026(_id:2)
+
1675408104682(_id:5) - 1675408101259(_id:4)

i want to following result using with mongodb 'aggregate'
please help me.

The expected result is...

(ex1)
{
   "sum_of_diff": "8859"
}


(ex2)
{
   "sum_of_diff": "5859"
}

thank you

Answer 1

One option is to use $reduce with $mergeObjects:

db.collection.aggregate([
  {$project: {
      sum_of_diff: {$reduce: {
          input: "$array",
          initialValue: {lastA: {_id: -1}, sum: 0},
          in: {$mergeObjects: [
              "$$value",
              {$cond: [
                  {$eq: ["$$this.pType", "A"]},
                  {lastA: "$$this"},
                  {$cond: [
                      {$eq: [{$subtract: ["$$this._id", 1]}, "$$value.lastA._id"]},
                      {sum: {
                          $add: [
                            "$$value.sum",
                            {$subtract: ["$$this.createdAt", "$$value.lastA.createdAt"]}
                          ]
                      }},
                      {}
                  ]}
              ]}
          ]}
      }}
  }},
  {$project: {sum_of_diff: "$sum_of_diff.sum", _id: 0}}
])

See how it works on the playground example