I have the following schemas made with mongoose:
const soldProductsSchema = new Schema({
id: {
type: String,
required: [true, 'Id - soldProductsSchema es obligatorio']
},
name: {
type: String,
required: [true, 'Name - soldProductsSchema es obligatorio']
},
brand: {
type: String,
required: [true, 'Brand - soldProductsSchema es obligatorio']
},
quantity: {
type: Number,
required: [true, 'Quantity - soldProductsSchema es obligatorio']
},
unitPrice: {
type: Number,
required: [true, 'UnitPrice - soldProductsSchema es obligatorio']
},
subtotal: {
type: Number,
required: [true, 'Subtotal - soldProductsSchema es obligatorio']
}
})
const saleSchema = new Schema({
nroFact: {
type: Number,
required: [true, 'NroFact es obligatorio']
},
total:{
type: Number,
required:[true, 'Total es obligatorio']
},
soldProducts:{
type: [soldProductsSchema], // <---- Array de productos
required: [true, 'Soldproducts es obligatorio']
}
})
In which I basically want to register a sale and store a list of products corresponding to it in the form of an array. Mongodb gives me the facility to store an array of objects in a field, unlike relational databases where we would clearly break the 1st. regular shape.
But I come from SQl and if I want to obtain a list of products ordered according to the number of units sold, I would do the following (taking into account that they are two different tables):
SELECT name, sum(quantity)
FROM soldProducts
GROUP BY name
ORDER BY sum(quantity) ASC
Taking into account my schematics, I would like to be able to obtain the same result, knowing that the products and their quantities sold are in separate sales.
I read the mongo documentation, and the $sum() example has an example with documents, and I don't know how to apply it to document properties that contain arrays of products.
I hope the question has been understood.
I even doubt if my modeling is correct to allow me to get the result I need. I am ready to listen to your suggestions.
Many thanks in advance.
You must do it with
Aggregation operations
, which processes multiple documents and returns calculated results.To do this, you can use
Aggregation pipelines
that it consists of one or more stages that process documents. Each stage performs an operation on the input documents. Documents generated from one stage are passed on to the next stage.In
.aggregate()
you must send a list with the different stages you need. It first groups the documents by pizza name and calculates the total amount by assigning it to totalQuantity, and lastly sorts by totalQuantity (1 for ascending order and -1 for descending order).Check this link Aggregation Pipeline