How to group something by date in mongoose?

user_12702965 0
Viewed 140

I want to group this kind of data by date. I already populate the arrays but i don't know where to aggegrate the mongoose data

Here is my example mongo data

[{
        "_id": "602badd505335124d77c07eb",
        "createTime": "2021-02-06T14:38:49.000Z",
        "services": [{
                "service_id": "602bad6105335124d77c07ea",
                "service_type": "Normal",
                "service_name": "Water",
                "price": 230
            },
            {
                "service_id": "602bad6105335124d77c07ea",
                "service_type": "Normal",
                "service_name": "Water",
                "price": 234
            }
        ],
        "items": [
            null,
            null
        ],
        "total_amount": 16000
    },
    {
        "_id": "602badfe05335124d77c07f0",
        "createTime": "2021-02-07T14:38:49.000Z",
        "services": [{
                "service_id": "602bad6105335124d77c07ea",
                "service_type": "Normal",
                "service_name": "Water",
                "price": 230
            },
            {
                "service_id": "602bad6105335124d77c07ea",
                "service_type": "Normal",
                "service_name": "Water",
                "price": 234
            }
        ],
        "items": [
            null,
            null
        ],
        "total_amount": 16000
    },
    {
        "_id": "602badfe05335124d77c07f3",
        "createTime": "2021-02-07T14:38:49.000Z",
        "services": [{
                "service_id": "602bad6105335124d77c07ea",
                "service_type": "Normal",
                "service_name": "Water",
                "price": 230
            },
            {
                "service_id": "602bad6105335124d77c07ea",
                "service_type": "Normal",
                "service_name": "Water",
                "price": 234
            }
        ],
        "items": [
            null,
            null
        ],
        "total_amount": 16000
    },
    {
        "_id": "602bae8705335124d77c07f5",
        "createTime": "2021-02-08T14:38:49.000Z",
        "services": [{
                "service_id": "602bad6105335124d77c07ea",
                "service_type": "Normal",
                "service_name": "Water",
                "price": 230
            },
            {
                "service_id": "602bad6105335124d77c07ea",
                "service_type": "Normal",
                "service_name": "Water",
                "price": 234
            }
        ],
        "items": [],
        "total_amount": 16000
    }
]

Here is the current code i am using to produce the result. As you can see I am populating two objects. What I want to do is I want to return an array group by date (createdTime)

router
  .route("/")
  .get((req, res) => {
    const match = {
      shop_id: req.params.shop_id,
    };
    let beforeString, afterString, params, date, filter_by;

    params = req.query
    filter_by = params.filter_by
    date = new Date(params.date)
    if (filter_by =="date"){
      beforeString = date
      afterString = new Date(date.getTime() + 86400000) 
    }
    
    match.createdAt = {
      $gte: moment(beforeString, "YYYY/MM/DD"),
      $lt: moment(afterString, "YYYY/MM/DD"),
    };

    Income.find(match)
      .populate("services.service_id")
      .populate("items.item_id")
      .then((result) => {
        res.status(200).json(result);
      }).catch((err) => res.status(400).json("error" + err));
  });

The actual result I want is something like this,

{
    "2021-02-06": [{
        "_id": "602badd505335124d77c07eb",
        "createTime": "2021-02-06T14:38:49.000Z",
        "services": [{
                "service_id": "602bad6105335124d77c07ea",
                "service_type": "Normal",
                "service_name": "Water",
                "price": 230
            },
            {
                "service_id": "602bad6105335124d77c07ea",
                "service_type": "Normal",
                "service_name": "Water",
                "price": 234
            }
        ],
        "items": [
            null,
            null
        ],
        "total_amount": 16000
    }],
    "2021-02-07": [{
            "_id": "602badfe05335124d77c07f0",
            "createTime": "2021-02-07T14:38:49.000Z",
            "services": [{
                    "service_id": "602bad6105335124d77c07ea",
                    "service_type": "Normal",
                    "service_name": "Water",
                    "price": 230
                },
                {
                    "service_id": "602bad6105335124d77c07ea",
                    "service_type": "Normal",
                    "service_name": "Water",
                    "price": 234
                }
            ],
            "items": [
                null,
                null
            ],
            "total_amount": 16000
        },
        {
            "_id": "602badfe05335124d77c07f3",
            "createTime": "2021-02-07T14:38:49.000Z",
            "services": [{
                    "service_id": "602bad6105335124d77c07ea",
                    "service_type": "Normal",
                    "service_name": "Water",
                    "price": 230
                },
                {
                    "service_id": "602bad6105335124d77c07ea",
                    "service_type": "Normal",
                    "service_name": "Water",
                    "price": 234
                }
            ],
            "items": [
                null,
                null
            ],
            "total_amount": 16000
        }
    ]
}
mongoose express node.js
2 Answers
  • Native Sorting :-
const arr = await Income.find(match).populate("services.service_id").populate("items.item_id");

const sortedArray = arr.sort((a, b) => a.createTime - b.createTime); // inc order
const sortedArray2 = arr.sort((a, b) => b.createTime - a.createTime); // dec order
user_121369000

You can do achieve this through an aggregation pipeline where you first $match all relevant documents by your date filter, then $group your documents by the date of your createTime-field, and finally $sort by each of the group's _id.

Income.aggregate([
  { $match: match },
  {
    $group: {
      _id: {
        $dateToString: {
          format: "%Y-%m-%d",
          date: {
            "$dateFromString": {
              "dateString": "$createTime"
            }
          }
        },
        
      },
      results: {
        $push: "$$ROOT"
      },
      
    }
  },
  {
    $sort: {
      _id: 1
    }
  }
])

Here's a simplified working example on mongoplayground: https://mongoplayground.net/p/AKFWBlMu5P5

user_37616280
Related