I am trying to write a single mongosh query on my customers database to:
- Sum of the spend1 and spend2 fields;
- Return only the top 10 userNames and totalSpends;
- Descending order;
- Limit of 10 collections.
Here is a couple of the collections in the database:
Atlas atlas-8qnlsq-shard-0 [primary] customers> db.customers.find();
[
{
_id: ObjectId("6387eed206cb28022d634cc4"),
userId: '500',
userName: 'robert.mcnaught',
firstName: 'Rab',
lastName: 'McNaught',
spend1: 10,
spend2: 15
},
{
_id: ObjectId("6387eef106cb28022d634cc5"),
userId: '501',
userName: 'robert.mcnaught',
firstName: 'Rab',
lastName: 'McNaught',
spend1: 10,
spend2: 15
}
]
From googling and manuals, I have come up with the following query structure. I can’t find a similar example for a complex query like this online. I don’t fully understand the nesting of multiple operators. I am also not sure whether I need aggregate() and $sum together in the same query. My totalSum isn’t a field in my data, so I’m not sure if it is correct to use totalSum in this manner.
db.customers.find().limit(10).aggregate(
{
{$sort:{"$project":{
"spend1":"$spend1",
"spend2":"$spend2",
“totalSum”:{“$add”:[“$spend1”,”$spend2”]},
},-1});
General pointers on query structure would help me in figuring this out.