I am trying to build a database with a name and 2 price tags, and I would like to automatically calculate the mean of those 2 price tags and get the rank of each of them by name. Is there a way of doing it other the using sort then trying to find the correct item’s name?
Hi @Long_Hei_Yu ,
Are the 2 fields of prices in the same document or you n two different documents?
Can you share some examples of current data?
In 5.0 there is a new window function to run on specific groups a function which can be calculting a mean/avg :
Thanks
Pavel
Yes, they are in the same document.
Is the following aggregation is what you are looking for?
db.products.insertOne({"name": "shoes", Price : {"Price1" : 10, "Price2" : 100}})
{ acknowledged: true,
insertedId: ObjectId("621762ff79f46c297ee79fec") }
db.products.aggregate([{$addFields: {
'Price.avgPrice': {
$avg: [
'$Price.Price1',
'$Price.Price2'
]
}
}}])
{ _id: ObjectId("621762ff79f46c297ee79fec"),
name: 'shoes',
Price: { Price1: 10, Price2: 100, avgPrice: 55 } }
Here I calculate an average of Prices and add it as “avgPrice” sub field…
Thanks
Pavel
Yes, i think thats what im looking for and I would also like to get the rank of the items from name.
for example, I would like to search for item named: shoes, and it will return the rank of the price1, price2 and avgPrice
Do you need something like that:
db.products.aggregate([{$match: {
name: 'shoes'
}}, {$addFields: {
'Price.avgPrice': {
$avg: [
'$Price.Price1',
'$Price.Price2'
]
}
}}, {$setWindowFields: {
partitionBy: '$name',
sortBy: {
'Price.avgPrice': -1
},
output: {
avgPriceRank: {
$denseRank: {}
}
}
}}, {$setWindowFields: {
partitionBy: '$name',
sortBy: {
'Price.Price1': -1
},
output: {
Price1Rank: {
$denseRank: {}
}
}
}}, {$setWindowFields: {
partitionBy: '$name',
sortBy: {
'Price.Price2': -1
},
output: {
Price2Rank: {
$denseRank: {}
}
}
}}])
{ _id: ObjectId("621762ff79f46c297ee79fec"),
name: 'shoes',
Price: { Price1: 10, Price2: 100, avgPrice: 55 },
avgPriceRank: 1,
Price1Rank: 1,
Price2Rank: 1 }
{ _id: ObjectId("621770633c52b33b2cd35fb8"),
name: 'shoes',
Price: { Price1: 3, Price2: 5, avgPrice: 4 },
avgPriceRank: 2,
Price1Rank: 2,
Price2Rank: 2 }
Thanks,
Pavel
Thanks for all the help 
1 Like
It appears that this is not what I’m fully looking for.
Im trying to search for the rank for different items name. Here is what I’m trying to do.
However, this will mean loading in all data every time a search is performed.
const mongoose = require('mongoose');
const itemlist = require('@schemas/items-schema');
module.exports = async (itemName) => {
const items = await itemlist.aggregate([
{ $addFields: {
'price.total': {
$sum: [
'$price.price1',
'$price.price2',
],
},
} }, { $setWindowFields: {
sortBy: {
'price.price1': -1,
},
output: {
'rank.price1': {
$denseRank: {},
},
},
} }, { $setWindowFields: {
sortBy: {
'price.price2': -1,
},
output: {
'rank.price2': {
$denseRank: {},
},
},
} }, { $setWindowFields: {
sortBy: {
'price.total': -1,
},
output: {
'rank.total': {
$denseRank: {},
},
},
} }]);
let item;
users.forEach(function(d) {
if (d.item == itemName) {
return item = d;
}
});
return item;
};
Why did you remove the first stage that $match itemName? use you own field name …
If you use and index the itemName it will only use it on this item.
Thanks
Pavel
Yes, I did try that. However, it only returns rank1 for all my items
const mongoose = require('mongoose');
const itemlist = require('@schemas/items-schema');
module.exports = async (itemName) => {
const items = await itemlist.aggregate([
{ $match: {
name: itemName
}},
{ $addFields: {
'price.total': {
$sum: [
'$price.price1',
'$price.price2',
],
},
} }, { $setWindowFields: {
partitionBy: '$name',
sortBy: {
'price.price1': -1,
},
output: {
'rank.price1': {
$denseRank: {},
},
},
} }, { $setWindowFields: {
partitionBy: '$name',
sortBy: {
'price.price2': -1,
},
output: {
'rank.price2': {
$denseRank: {},
},
},
} }, { $setWindowFields: {
partitionBy: '$name',
sortBy: {
'price.total': -1,
},
output: {
'rank.total': {
$denseRank: {},
},
},
} }]);
return items;
};
Sorry for the confusion but here the data we are looking at.
So I am trying to get the overall rank of the items of “item1”
I am expecting
rank.price1: 3
rank.price2: 2
rank.total: 3
Oh so you need rank of an item overall all items? Or whithin a specific Item?
If the first than $partitionBy should be “null” and matching can only be done after ranking.
[{$addFields: {
'price.total': {
$sum: [
'$price.price1',
'$price.price2'
]
}
}}, {$setWindowFields: {
partitionBy: null,
sortBy: {
'price.price1': -1
},
output: {
'rank.price1': {
$denseRank: {}
}
}
}}, {$setWindowFields: {
partitionBy: null,
sortBy: {
'price.price2': -1
},
output: {
'rank.price2': {
$denseRank: {}
}
}
}}, {$setWindowFields: {
partitionBy: null,
sortBy: {
'price.total': -1
},
output: {
'rank.total': {
$denseRank: {}
}
}
}}, {$match: {
name: 'item1'
}}]
Since ranking needs to be done across all items its not a sufficient query and I will recommend periodically materialise it with $merge and query the materialised view. Do you have any way to narrow down the items before calculating ranks? Are there any specific categories or such?
Unfortunately, the data has already been narrowed down. So for example, let’s say if I have around 1,000,000 items does that affect the performance a lot?
Well yes it will process all items in memory each query.
What is the business intention of this rank? Just to sort?
Ita better to use sorting of fields with indexes rather than ranks if thats the purpose…
Oh well, this was actually designed so users can see the rank of the product of what they search for rank of how many items were sold and the price, etc.
So you just need to sort and get an array back , based on the sort order (index) you can know the rank no?
Yes, since I would like the rank of multiple fields, so seems like the above will do the trick.
or i should separate the data fields into different databases so they can be auto sorted and return the rank of it?
This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.