开发者

Mongodb array values subtract then sum

开发者 https://www.devze.com 2022-12-07 17:21 出处:网络
I have a collection contains 2 statuses of orders "Shipped" and "Delivered". I want to calculate the average in hours

I have a collection contains 2 statuses of orders "Shipped" and "Delivered". I want to calculate the average in hours

Formula (Delivered 1 - Shipped 1) + (Delivered 2 - Shipped 2) + (Delivered N - Shipped N)/N

here is my collection

    {
    trackingHistory: [
      {
        status: 'Shipped',
        time: ISODate("2022-11-22T06:30:49.000Z")
      },
      {
        status: 'Delivered',
        time: ISODate("2022-11-25T15:30:00.000Z")
      }
    ]
  },
  {
    trackingHistory: [
      {
        status: 'Shipped',
        time: ISODate("2022-11-22T09:29:45.000Z")
      },
      {
        status: 'Delivered',
        time: ISODate("2022-11-23T19:26:00.000Z")
      }
    ]
  }

here is my code

    db.client_order_news.aggregate([
  { $match : { 
      receiverCity : 'New York',
      created_at:{$gte:ISODate("2022-11-01T00:00:00.398Z"),$lt:ISODate("2022-11-30T23:59:59.398Z")},
      "trackingHistory. status":"Shipped",
      "trackingHistory.status":"Delivered"
        } },
        { $project : { _id : 0, trackingHistory : {$filter: {
            input: '$trackingHistory',
            as: 'tracking',
            cond: {$or: [{ $eq: ['$$tracking.status', "Shipped"] }, { $eq: ['$$tracking.status',"Delivered"] }]}
        }}, } },
       {$project: { "$sum": ["$price", { "$subtract": ["$deposits.amount&qu开发者_如何学编程ot;] } ] }}
]).pretty()
0

精彩评论

暂无评论...
验证码 换一张
取 消