CODEWITHSUNDEEP
node.jsmongodbmongoose
Liam
Liam

Reputation: 965

Mongodb $lookup/populate and summarize

I'm trying to join two schema and summarize the total price. This is the schema:

const Product = new mongoose.Schema({
  name: { type: String, required: true },
  price: Number
})

const Order = new mongoose.Schema({
  fullname: { type: String, required: true },
  address: { type: String, required: true },
  products: [
    {
      product: {
        type: mongoose.Schema.Types.ObjectId,
        ref: 'Product',
      },
      quantity: Number,
    },
  ],
})

I want to create aggregation to get orders with total price.so it could be like:

[
  {
    fullname: 'jhon doe',
    address: 'NY 1030',
    products: [
      {
        product: {
          name: 'Piano',
          price: 50
        },
        quantity: 10,
      },
    ],
    price: 500
  }
]

I try to use aggregation framework without any success, any idea?

Upvotes: 0

Views: 154

Answers (1)

Ana Lava
Ana Lava

Reputation: 787

Updated

As the question needs the price to be calculated by sum of the multiplication of quantity and product price, It can be done with below code:

db.getCollection('orders').aggregate([
  { $unwind: { path: '$products' } },
  {
    $lookup: {
      from: 'products',
      localField: 'products.product',
      foreignField: '_id',
      as: 'p',
    },
  },
  { $unwind: { path: '$p' } },
  {
    $group: {
      _id: '$_id',
      price: { $sum: { $multiply: ['$p.price', '$products.quantity'] } },
      fullname: { $first: '$fullname' },
      address: { $first: '$address' },
      products: { $push: { product: '$p', quantity: '$products.quantity' } },
    },
  },
])

----------------------------------------------------------------------------------------------------------------------------

You can use $lookup in aggregation as below:

db.getCollection('orders').aggregate([
  {
    $lookup: {
      from: 'products',
      localField: 'products.product',
      foreignField: '_id',
      as: 'p',
    },
  },
  { $unwind: { path: '$p' } },
  {
    $project: {
      fullname: 1,
      address: 1,
      products: {
        product: '$p',
        quantity: 1,
      },
      price: 1,
    },
  },
])

Upvotes: 2

Related Questions