how to group data in mongoose where 3 condition-CodePudding

How to group data in aggregate mongoose with some predefined conditions, the data will be grouped based on the following 3 conditions < 5 Hours, 5-9 Hours, >9 Hours.

Data grouped by timeworkDay. if the timeWorkDay data is less than 5, it will be grouped in timework5, if the data is greater than 5, and less than 9, then the data will be grouped in timework59, and if it is greater than 9, the data will be grouped in timework9

sample data

const data = [
    {
        "_id": "620f3f7a2148a8227855068f",
        "timeWorkDay": 8,
        "created_at": "2022-02-18T06:40:58.932Z",
        "updated_at": "2022-02-18T07:54:59.331Z",
        "__v": 0,
    },
    {
        "_id": "6201e0e52faf8f0228dfad27",
        "created_at": "2022-02-08T03:17:57.018Z",
        "updated_at": "2022-02-08T03:17:57.018Z",
        "__v": 0,
        "timeWorkDay": 12,
    },
    {
        "_id": "61f21be553b6de54a8ef4e0b",
        "id_company": "61e00d1a144fa470f1870154",
        "created_at": "2022-02-03T02:48:57.006Z",
        "updated_at": "2022-02-01T02:04:29.778Z",
        "__v": 0,
        "timeWorkDay": 14,
    },
    {
        "_id": "61fb45647dda6444afd10e2e",
        "created_at": "2022-02-03T02:48:57.006Z",
        "updated_at": "2022-02-22T08:10:39.775Z",
        "__v": "2022-02-06T17:00:00.000Z",
        "timeWorkDay": 5,
    },
    More...
]

And Expected data

const result = [
timeWork5:[
    {
        "_id": "61fb45647dda6444afd10e2e",
        "created_at": "2022-02-03T02:48:57.006Z",
        "updated_at": "2022-02-22T08:10:39.775Z",
        "__v": "2022-02-06T17:00:00.000Z",
        "timeWorkDay": 5,
    },
    More...
],
timeWork59: [
    {
        "_id": "620f3f7a2148a8227855068f",
        "timeWorkDay": 8,
        "created_at": "2022-02-18T06:40:58.932Z",
        "updated_at": "2022-02-18T07:54:59.331Z",
        "__v": 0,
    },
    More...
],
timeWork9: [
    {
        "_id": "6201e0e52faf8f0228dfad27",
        "created_at": "2022-02-08T03:17:57.018Z",
        "updated_at": "2022-02-08T03:17:57.018Z",
        "__v": 0,
        "timeWorkDay": 12,
    },
    {
        "_id": "61f21be553b6de54a8ef4e0b",
        "id_company": "61e00d1a144fa470f1870154",
        "created_at": "2022-02-03T02:48:57.006Z",
        "updated_at": "2022-02-01T02:04:29.778Z",
        "__v": 0,
        "timeWorkDay": 14,
    },
    More...
],
]

I've tried several ways but haven't gotten the results I expected

const execQuery: any = await EmployeeModel.aggregate([
      {
        $group: {
          _id: {
            timeWorkDay5: {
              $cond: [
                {$lte: ["$timeWorkDay", 4]}
              ]
            },
            timeWorkDay9: {
              $cond: [
                {$gte: ["$timeWorkDay", 7]}
              ]
            },
          },
          createdAt: { $first: "$joinDate" },
          count: { $sum: 1 }
        }
      },
      { $sort: { joinDate: -1 } }
    ]);

CodePudding user response：

Think $facet meets your requirement.

const execQuery: any = await EmployeeModel.aggregate([
  {
    $facet: {
      timeWorkDay5: [
        {
          $match: {
            $expr: {
              $lte: [
                "$timeWorkDay",
                4
              ]
            }
          }
        }
      ],
      timeWorkDay59: [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $gte: [
                    "$timeWorkDay",
                    5
                  ]
                },
                {
                  $lt: [
                    "$timeWorkDay",
                    9
                  ]
                }
              ]
            }
          }
        }
      ],
      timeWorkDay9: [
        {
          $match: {
            $expr: {
              $gte: [
                "$timeWorkDay",
                9
              ]
            }
          }
        }
      ]
    }
  }
])

Sample Mongo Playground