Question 118453 in Mongodb Question posted in
The official documentation can be found here.

sort data by selected range of date and createdFor from mongodb and nodejs

I want to sort data based on date and the createdFor property. I want the sum of the data in group of createdFor and all specifc itemName for each createdFor.

date =  [Tue Apr 05 2022 11:10:41 GMT-0400 (Eastern Daylight Time), Sat Apr 09 2022 11:10:41 GMT-0400 (Eastern Daylight Time)]

createdfor can be repeate and itemName is different for all createdFor

date =  [Tue Apr 05 2022 11:10:41 GMT-0400 (Eastern Daylight Time), Sat Apr 09 2022 11:10:41 GMT-0400 (Eastern Daylight Time)]

 const dataList = [
    {
      createdFor: "62429fd5f7db3912fea7a716",
      itemName: "a",
      totalQty: 100,
      createAt: "2022-04-05T08:05:41.653+00:00",
    },
    {
      createdFor: "62429fd5f7db3912fea7a716",
      itemName: "b",
      totalQty: 80,
      createAt: "2022-04-08T08:05:41.653+00:00",
    },
    {
      createdFor: "62429fd5f7db3912fea7a716",
      itemName: "b",
      totalQty: 70,
      createAt: "2022-04-07T08:05:41.653+00:00",
    },
    {
      createdFor: "62223a3f43e080995d5b46bc",
      itemName: "c",
      totalQty: 110,
      createAt: "2022-04-09T08:05:41.653+00:00",
    },
    {
      createdFor: "62223a3f43e080995d5b46bc",
      itemName: "c",
      totalQty: 30,
      createAt: "2022-04-06T08:05:41.653+00:00",
    },
    {
      createdFor: "62223a3f43e080995d5b46bc",
      itemName: "c",
      totalQty: 100,
      createAt: "2022-12-30T08:05:41.653+00:00", //here this data excludes because out range of date
    },
  ];
}

**
answer
**

{
  result: [
    {
      createdFor: 62429fd5f7db3912fea7a716,
      stockInfo: [
        { itemName: "a", totalQty: 100 },
        { itemName: "b", totalQty: 150 },
      ],
    },
    {
      createdFor: 62223a3f43e080995d5b46bc,
      stockInfo: [
        { itemName: "c", totalQty: 140 },
      ],
    },
    
  ];
}
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Question like thiis

    
[
  {
    createdFor: "62429fd5f7db3912fea7a716",
    stockOutDetail: {
      stock_name: "a",
      totalQty: 110
    },
    createAt: "2022-04-05T08:05:41.653+00:00",
    
  },
  {
    createdFor: "62429fd5f7db3912fea7a716",
    stockOutDetail: {
      stock_name: "b",
      totalQty: 50
    },
    createAt: "2022-04-08T08:05:41.653+00:00",
    
  },
  {
    createdFor: "62429fd5f7db3912fea7a716",
    stockOutDetail: {
      stock_name: "b",
      totalQty: 70
    },
    createAt: "2022-04-07T08:05:41.653+00:00",
    
  },
  {
    createdFor: "62223a3f43e080995d5b46bc",
    stockOutDetail: {
      stock_name: "c",
      totalQty: 70
    },
    createAt: "2022-04-09T08:05:41.653+00:00",
    
  },
  {
    createdFor: "62223a3f43e080995d5b46bc",
    stockOutDetail: {
      stock_name: "c",
      totalQty: 70
    },
    createAt: "2022-04-06T08:05:41.653+00:00",
    
  },
  {
    createdFor: "62223a3f43e080995d5b46bc",
    stockOutDetail: {
      stock_name: "c",
      totalQty: 70
    },
    createAt: "2022-12-30T08:05:41.653+00:00",
    //here this data excludes because out range of date
    
  },
  
]

  2. 0

    First you can convert the date array to a suitable format as done below

    date =  ['Tue Apr 05 2022 11:10:41 GMT-0400 (Eastern Daylight Time)', 'Sat Apr 09 2022 11:10:41 GMT-0400 (Eastern Daylight Time)']

months = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']

new_dates = []

date.forEach((d) => {
    str = d.split(' ')
    mon = ('0'+(months.indexOf(str[1])+1)).slice(-2)
    new_dates.push(str[3]+'-'+mon+'-'+str[2])
})

    Then you can use the following mongodb query

    db.collection.aggregate([
  {
    "$project": {
      createdFor: 1,
      itemName: 1,
      totalQty: 1,
      createAt: {
        "$substr": [
          "$createAt",
          0,
          10
        ]
      }
    }
  },
  {
    "$match": {
      "$and": [
        {
          createAt: {
            "$gte": "2022-04-05"
          }
        },
        {
          createAt: {
            "$lte": "2022-04-09"
          }
        }
      ]
    }
  },
  {
    "$group": {
      "_id": {
        for: "$createdFor",
        name: "$itemName"
      },
      "sum": {
        "$sum": "$totalQty"
      }
    }
  },
  {
    "$group": {
      "_id": "$_id.for",
      "stockInfo": {
        "$push": {
          itemName: "$_id.name",
          totalQty: "$sum"
        }
      }
    }
  }
])

    Here is the code on mongoplayground for reference

    https://mongoplayground.net/p/KQHPBym_Z2P%5B%5D%5B1%5D

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search