MongoDB Charts - Aggregation Pipeline to get most recent value and CummulativeSum of a field

[Dilip_Rajkumar]

Hi,
I am trying to build two charts with MongoDB Charts, a Number Card and a DataTable. I have two doubts pertaining to this.

1.) Getting the last value for “Equity” from the last document in a collection to display in number card:
Assume I have a time series document of the format where new records are inserted asynchronously at random time intervals

[
{ time: ISODate("22-04-2022T23:00:00Z") , equity : 25100 }
{ time: ISODate("22-04-2022T23:01:00Z") , equity : 25109 }
{ time: ISODate("22-04-2022T23:01:05Z") , equity : 25090 }
.....
]

I would like to print the most recent equity value (25090) in this card. The card should also refresh every 5 secs or 10 secs and display the most recent equity value from the last inserted document. Currently I don’t see how to do this with the aggregation options, as there is no display last value of series option. Perhaps I should write some query to do this? If so, can a MongoDB expert help me with this?

2.) Cummulative sum of profit_csf column:
The next requirement is to create a new calculated field called balance which computes the cummulative sum of a profit_csf column in this datatable below:

<iframe style="background: #21313C;border: none;border-radius: 2px;box-shadow: 0 2px 10px 0 rgba(70, 76, 79, .2);" width="640" height="480" src="https://charts.mongodb.com/charts-project-0-nvgoj/embed/charts?id=62680f50-aa03-494b-8bdf-e57c312d4fe5&maxDataAge=3600&theme=dark&autoRefresh=true"></iframe>

I followed this stackoverflow article but $setWindowFields is not recognized as valid pipeline operator in MongoDB Charts - Calculated Fields. I also tried:

{ $accumulator: {
    sortBy: { time: 1 },
    output: {
      cumulative: {
        $sum: "$profit_csf",
        window: { documents: [ "unbounded", "current" ] }
      }
    }
  }}

but, accumulator is also not accepted in the Atlas Free Tier that i am using. Can you kindly provide a solution to create a new calculated field called Balance that is basically a cummulative sum of the profit_csf column?

Best Regards,
Dilip

[tomhollander]

Hi @Dilip_Rajkumar -

The first scenario can be accomplished by using the Top Item chart type (under the Text category) instead of the Number chart. Top Item lets you specify a sort order and choose the specific fields you want from the matching document.

For the second scenario: You can use $setWindowFields in Charts, but since it’s an aggregation stage it needs to be used in the query bar, not within a single calculated field.

HTH
Tom

[Dilip_Rajkumar]

Hi Tom,
Thank you for your reply. When I type the following query in the query bar,

[{$setWindowFields:{
	sortBy:{
		time:1
	}
	,output:{
		balance:{
			$sum:"$profit_real",window:{
				documents:["unbounded","current"]
			}
		}
	}
}
}]

The query compiles without any error, but the Balance is showing up as a column filled with zeros:

I also need to filter the rows based on the entry column value by using {entry:1} in the query bar. So the $setWindowFields cummulative sum aggregation query needs to be combined with this {entry:1}
Can you kindly guide me by providing the right query for doing this cummulative sum?

Best Regards,
Dilip

[tomhollander]

Hi Dilip -

A simple query (in curly braces) is just a shorthand for a $match aggregation stage. So you can combine the two like this:

[
  { $setWindowFields: { ... } },
  { $match: { entry: 1 } }
]

Unfortunately I’ve never used $setWindowFields myself so I can’t help debug why it’s not giving you the expected result.

Tom

[Asya_Kamsky]

@Dilip_Rajkumar

Balance is showing up as a column filled with zeros

Is the field profit_real an actual field in your documents? Or is it a field you are computing - if so then I believe it might be computed after your $setWindowFields stage runs. If it’s being computed (it appears to be a sum of profit and commission) then just replace $profit_real with expression that computes it - like {$sum:{$add:["$profit","$commission"]}}

Asya

[tomhollander]

Well spotted @Asya_Kamsky. You can tell that profit_real is a calculated field as it is shown in italics in th left pane. You could either do what you suggested in the $setWindowFields stage, or move the calculated field to the top by defining it in the query bar using $set instead of using the calculated field feature (which does the exact same thing, but at a different stage in the pipeline).

@Dilip_Rajkumar note you can use the “View Aggregation Pipeline” from the … menu to see the complete pipeline being generated by the chart definition, which can be a helpful way of debugging these complex problems.

Tom

[Dilip_Rajkumar]

@tomhollander

{ $match: { entry: 1 } }

Thank you Asya, the modified query works now for calculating the cummulative balance