Summing an array of numbers

Steven_Meyer1 · 2024-04-29T00:43:24.269Z

This is my data:

{‘_id’: 0, ‘name’: ‘Alice’, ‘sales’: [48, 38, 48, 43, 43, 34]}

{‘_id’: 1, ‘name’: ‘Bob’, ‘sales’: [19, 14, 50, 35, 41]}

{‘_id’: 2, ‘name’: ‘Carol’, ‘sales’: [13, 39, 15, 23]}

{‘_id’: 3, ‘name’: ‘Dan’, ‘sales’: [30, 19, 27, 31, 46, 22]}

{‘_id’: 4, ‘name’: ‘Eve’, ‘sales’: [30, 40, 35, 42, 39, 34, 46]}

{‘_id’: 5, ‘name’: ‘Fred’, ‘sales’: [49, 36, 34, 13, 10, 28]}

{‘_id’: 6, ‘name’: ‘Gloria’, ‘sales’: [19, 14, 39, 24, 46]}

{‘_id’: 7, ‘name’: ‘Harry’, ‘sales’: [12, 17, 25, 24, 19]}

{‘_id’: 8, ‘name’: ‘Ingrid’, ‘sales’: [33, 23, 35, 40, 13, 16, 49, 26]}

{‘_id’: 9, ‘name’: ‘Jack’, ‘sales’: [13, 10, 31]}

(Total of 10 documents)

The output was generated with this code:

from pymongo import MongoClient
import pymongo

#CONNECTION_STRING = "string"

this_database = 'example_db'
this_collection = 'examples'

client = MongoClient(CONNECTION_STRING)
dbname = client[this_database]
collection_name = dbname[this_collection]

q100 = [{"$project": {
    "name":1,
    "sales":1
}}]

cursor = collection_name.aggregate(q100)

res_list = list(cursor)

for i, x in enumerate(res_list):
    print(x)
    print()

I want to print out total sales for each sales person. So output should be something along these lines:

{‘_id’: 0, ‘name’: Alice, ‘totSales’: 254}

{‘_id’: 1, ‘name’: Bob, ‘totSales’: 159}

{‘_id’: 2, ‘name’: Carol, ‘totSales’: 90}

{‘_id’: 3, ‘name’: Dan, ‘totSales’: 175}

{‘_id’: 4, ‘name’: Eve, ‘totSales’: 266}

{‘_id’: 5, ‘name’: Fred, ‘totSales’: 170}

{‘_id’: 6, ‘name’: Gloria, ‘totSales’: 142}

{‘_id’: 7, ‘name’: Harry, ‘totSales’: 97}

{‘_id’: 8, ‘name’: Ingrid, ‘totSales’: 235}

{‘_id’: 9, ‘name’: Jack, ‘totSales’: 54}

Tried various things. The last one had this aggregation query:

q110 = [{"$project":{
    "name":1,
    "totSales":{"$add":"sales"}
}}]

Which resulted in the following error message:

raise OperationFailure(errmsg, code, response, max_wire_version)
pymongo.errors.OperationFailure: Failed to optimize pipeline :: caused by :: $add only supports numeric or date types, not string, full error: {‘ok’: 0.0, ‘errmsg’: ‘Failed to optimize pipeline :: caused by :: $add only supports numeric or date types, not string’, ‘code’: 14, ‘codeName’: ‘TypeMismatch’, ‘$clusterTime’: {‘clusterTime’: Timestamp(1714348628, 1), ‘signature’: {‘hash’: b’3\x01\xc7\x0e\xcaM\xf3\xe4Yj\xd7\xf2g\*\x17\x07Q\x19b’, ‘keyId’: 7299174040537661447}}, ‘operationTime’: Timestamp(1714348628, 1)}

Anyone got any suggestions?

Craig_Crevola · 2024-04-29T05:55:08.168Z

HI Steven

You can use reduce in the project:

[{
  $project: {
    name: 1,
    totSales: {
      $reduce: {
        input: "$sales",
        initialValue: 0,
        in: {
          $add : ["$$value", "$$this"]
        }
      }
    }
  }
}]

See: $reduce (aggregation) - MongoDB Manual v7.0

Steven_Meyer1 · 2024-04-29T23:57:14.254Z

Sorry, wrong formatting. Still new at this.

Hope this works out better:

[{"$project": {
    "name":1,
    "totSales": {
        "$reduce": {
            "input":"$sales",
            "initialValue":0,
            "in": {
                "$add": ["$$value", "$$this"]
            }
        }

    }
}}]

Steven_Meyer1 · 2024-04-29T23:57:29.813Z

Many thanks.

This worked:

[{“$project”: {
“name”:1,
“totSales”: {
“$reduce”: {
“input”:“$sales”,
“initialValue”:0,
“in”: {
“$add”: [“$$value”, “$$this”]
}
}
}
}}]

(Python insists on enclosing almost everything in double quotes)

Steven_Meyer1 · 2024-04-30T04:28:47.533Z

Thought this might interest you.

After getting your response I decided to see what ChatGPT had to say.

Here’s a link to the exchange:

drive.google.com

chatGPT_mongodb.txt

Google Drive file.