CODEWITHSUNDEEP
mongodbmongoosemongodb-queryaggregation-framework
subhraedqart
subhraedqart

Reputation: 115

Getting array of group _id while using MongoDB

I need to calculate some record using group in mongoDB but in my case array of _id are coming. I am explaining my query below.

let query = {
      $group: {
             _id:  "$Products.ProductName",
             dispatchcount: { $sum: 1 },
             totalDispatchValue: {$sum:"$TotalAmount.TotalPayment"},
             totalDiscountValue: {$sum: "$TotalDiscount.TotalDiscountAmount"}
      }
}
pipeLine.push(query);
const orders = await dispatchOrdersCol.aggregate(pipeLine);

Actual Output:

[
            {
                "_id": [
                    "Unisex Navy Blue Belt"
                ],
                "dispatchcount": 1,
                "totalDispatchValue": 27922,
                "totalDiscountValue": 4084
            },
            {
                "_id": [
                    "Writing Ruled Note Book",
                    "Physics Record Book",
                    "Chemistry Record Book",
                    "Computer Science Record Book"
                ],
                "dispatchcount": 1,
                "totalDispatchValue": 2190,
                "totalDiscountValue": 0
            },
            {
                "_id": [
                    "PU2-Physics Part-1 Text Book",
                    "PU2-Physics Part-2 Text Book",
                    "PU2-Mathematics Part - 1 Text Book",
                    "PU2-Chemistry Part - 1 Text Book",
                    "PU2-English Text Book",
                    "PU2-Mathematics Part - 2 Text Book",
                    "PU2-Chemistry Part - 2 Text Book",
                    "PU2-English Work Book",
                    "PU2-TEXT BOOK",
                    "PU2-Sanskrit Text Book",
                    "Boys White & Blue Striped Half Shirt",
                    "Boys Navy Blue Regular Fit Trousers",
                    "Illume Calf-length Cotton Black Socks  "
                ],
                "dispatchcount": 1,
                "totalDispatchValue": 4131,
                "totalDiscountValue": 150
            },
            {
                "_id": [
                    "PU2-TEXT BOOK"
                ],
                "dispatchcount": 1,
                "totalDispatchValue": 1679,
                "totalDiscountValue": 0
            }
        ]

Here for _id key there are multiple values coming and some of the values also repeating in next record.

Mongoose Model:

const Model = new Schema({
        DispatchId: { type: Number, required: true },
        OrderNumber: { type: String, unique: true, required: true },
        OrderStatus: { type: String },
        OrderType: { type: String },        
        DispatchPriority: { type: String },

        Comments: { type: Array },
        Products: { type: Array },

        Customer: { type: Object },     
        TotalDiscount: { type: Object },
        TotalShipping: { type: Object },
        TotalCoupon: { type: Object },
        TotalAmount: { type: Object },
        PaymentDetails: { type: Object },
        ClientDetails: { type: Object },

        DispatchCreatedAt: { type: String },
        DispatchUpdatedAt: { type: String },

        IsActive: { type: Boolean }
    }, 
    {
        timestamps: { 
            createdAt: 'CreatedAt', 
            updatedAt: 'UpdatedAt' 
        },
        collection: DISPATCH_ORDERS_COLLECTION
    }
);

Here I need to calculate the total Payment and total Discount as per the product name.But in my case one product is also coming two times in two record.

Upvotes: 1

Views: 50

Answers (1)

whoami - fakeFaceTrueSoul
whoami - fakeFaceTrueSoul

Reputation: 17925

In your documents Products is an array, (might be an array of Objects with a field ProductName) when you do "$Products.ProductName" it will give you an array of products names. So $group has to group on arrays with exactly same elements inside them(Even a mis-match of one element between two arrays would be treated as two values on _id in group stage). So you need to do $unwind on Products array prior to $group stage :

In-correct Query :

db.collection.aggregate([
  /** You don't need this addFields stage, just given to test, you can remove `$group` stage & check the `products` field value */
  {
    $addFields: {
      products: "$products.productName"
    }
  },
  {
    $group: {
      _id: "$products" // arrays
    }
  }
])

Test : mongoplayground

Correct Query :

db.collection.aggregate([
  {
    $unwind: "$products"
  },
  {
    $addFields: {
      products: "$products.productName"
    }
  },
  {
    $group: {
      _id: "$products" // Single value
    }
  }
])

Test : mongoplayground

Ref : $unwind

Upvotes: 1

Related Questions