I'm pretty new to Mongo. I have two collections that look as follows.
Order collection
[
{
id: 1,
price: 249,
store: 1,
status: true
},
{
id: 2,
price: 230,
store: 1,
status: true
},
{
id: 3,
price: 240,
store: 1,
status: true
},
{
id: 4,
price: 100,
store: 2,
status: true
},
{
id: 5,
price: 150,
store: 2,
status: true
},
{
id: 6,
price: 500,
store: 3,
status: true
},
{
id: 7,
price: 70,
store: 4,
status: true
},
]
Store Collections
[
{
id: 1,
name: "Store A",
status: true
},
{
id: 2,
name: "Store B",
status: true
},
{
id: 3,
name: "Store C",
status: true
},
{
id: 4,
name: "Store D",
status: false
}
]
How to find the top store from the list of orders, that should be based on the total sales in each store.
I have tried with the following
db.order.aggregate([
{
"$match": {
status: true
}
},
{
"$group": {
"_id": "$store",
"totalSale": {
"$sum": "$price"
}
}
},
{
$sort: {
totoalSale: -1
}
}
])
I got the sorted list of stores from the above snippets. But I want to add store details along with total sales.
For more : https://mongoplayground.net/p/V3UH1r6YRnS
Expected Output
[
{
id: 1,
name: "Store A",
status: true,
totalSale: 719
},
{
id: 1,
name: "Store c",
status: true,
totalSale: 500
},
{
_id: 2,
id: 1,
name: "Store B",
status: true,
totalSale: 250
},
{
_id: 4,
name: "Store D",
status: true,
totalSale: 70
}
]
CodePudding user response:
$lookup
-store
collection joinsorder
collection and generate new fieldstore_orders
.$set
- Filterorder
withstatus: true
fromstore_orders
.$set
-totalSale
field sum forstore_orders.price
.$sort
- SorttotalSale
by descending.$unset
- Removestore_orders
field.
db.store.aggregate([
{
$lookup: {
from: "order",
localField: "id",
foreignField: "store",
as: "store_orders"
}
},
{
$set: {
"store_orders": {
$filter: {
input: "$store_orders",
as: "order",
cond: {
$eq: [
"$$order.status",
true
]
}
}
}
}
},
{
$set: {
"totalSale": {
"$sum": "$store_orders.price"
}
}
},
{
$sort: {
totalSale: -1
}
},
{
$unset: "store_orders"
}
])
CodePudding user response:
You can start from store
collection, $lookup
the order
collection, $sum
the totalSales
, then wrangle to your expected form
db.store.aggregate([
{
"$lookup": {
"from": "order",
let: {
id: "$id"
},
pipeline: [
{
$match: {
$expr: {
$eq: [
"$$id",
"$store"
]
}
}
},
{
$group: {
_id: null,
totalSale: {
$sum: "$price"
}
}
}
],
"as": "totalSale"
}
},
{
$unwind: "$totalSale"
},
{
$addFields: {
totalSale: "$totalSale.totalSale"
}
},
{
$sort: {
totalSale: -1
}
}
])
Here is the Mongo playground for youre reference.