Sum of values in array with same key names but with different type values

Working with Data
,
#1

Hi everyone,
I’m newbie using mongodb and I have a complex problem when I’m trying to make a sum. All the data stored in the contracts collection have the following structure regards if is a text, number, date, email etc…

{ "label": "name", "value": "john" }
{ "label": "email", "value": "john@email.com" }
{ "label": "telephone", "value": "55522288338" }
{ "label": "startdate", "value": "04/05/2021" }
{ "label": "selection01", "value": "Station01" }
{ "label": "quantity01", "value": "$5,456.09" }
{ "label": "selection02", "value": "Station03" }
{ "label": "quantity02", "value": "$399.99" }
{ "label": "selection03", "value": "Station01" }
{ "label": "quantity03", "value": "$150.00" }
{ "label": "selection04", "value": "Station02" }
{ "label": "quantity04", "value": "$642.87" }

Every document has around 100 objects with this structure. I need to make a conditional sum base on the label: “selection”. For example:

Sum all the values of quantity01 + quantity02 + quantity03 + quantity04 and the condition is that label needs to be the same, So in the example above the sum will be only quantity01 + quantity03 because selection01 and selection03 has the same value of “station01”. Then because selection02 and selection04 has diferent values they will not sum. So I need the query to return all the fields, plus the result of the three sums. Like this:

{ "label": "name", "value": "john" }
{ "label": "email", "value": "john@email.com" }
{ "label": "telephone", "value": "55522288338" }
{ "label": "startdate", "value": "04/05/2021" }
{ "label": "selection01", "value": "Station01" }
{ "label": "quantity01", "value": "$5,456.09" }
{ "label": "selection02", "value": "Station03" }
{ "label": "quantity02", "value": "$399.99" }
{ "label": "selection03", "value": "Station01" }
{ "label": "quantity03", "value": "$150.00" }
{ "label": "selection04", "value": "Station02" }
{ "label": "quantity04", "value": "$642.87" }

{ "label": "sumStation01", "value": "$5,606.09" }   //sum of quantity01 + quantity03
{ "label": "sumStation02", "value": "$642.87" }
{ "label": "sumStation03", "value": "$399.99" }

I’ve been searching on how I can do this, but all examples have different key fields names, nothing like my document structure.

I will appreciate very much some help to find a way to do this.
Thanks for your help.

#2

The only real help I can give you is that you should redesign your schema.

  1. Your amount should be some kind of numbers.
    1a. The way it is right now means you have to convert them to numbers before doing any calculation.
    1b. You will also have to convert them to number to compare 2 values. (String wise $642.87 is bigger than $5,606.09)
    1c. The $ sign is extremely waste full.
  2. Your fake arrays, using string suffix for indexes, should be real array.
#3

Thanks for your comments Steevej, I would love to do that, but the information comes from a 3rd party service and that’s their schema format. That’s why I can’t change it.

Even the $ sign comes integrated when I received the information via webhook. My example is exactly how every object in the array comes from the service.

#4

Can you name that 3rd party service so that I never ever consider using their products?

The way you receive the data and the way the data is stored in mongodb is 2 things. Please please do not store the received data like this. Transform it into a better format before storing.