Aggregation to get balances at point in time

Working with Data
1

I’m trying to build an aggregation to get two stats:

  • the number of customers with a positive balance at a set date
  • the total value of balances at a set date

My customer/balance data is structured as:

customerRef: “Customer1”,
accountManager: “Dave Smith”,
history: [
{
date: 2023-12-01,
balance: 120.42
},
{
date: 2024-01-12,
balance: 150.21
}
]

Grateful for any guidance on this.

2

Hello @Ed_French, Welcome to the MongoDB community forum,

You can use $facet stage to achieve your two stats, for ex:

// First stage: Your match condition at a set date
// { $match: { .. your inputs }
// Last stage: (you can rename the stat name whatever you choose)
{
  $facet: [
    firstState: [
      // { add required pipeline stages }
    ],
    secondState: [
      // { add required pipeline stages }
    ]
  ]
}
1 Like
3

Thanks but how can I get the total balance across all customers on a set date e.g. 2023-12-15 ?

4

You can do match condition and next stage use $group stage to get total balance.