javascript 在mongodb中按ID过滤不同对象的求和值

eqqqjvef 于 2023-03-06 发布在 Java

关注(0)|答案(1)|浏览(109)

我需要通过employeeId来计算工作时间的总和。我的意思是，对象3和5（下面的集合）属于同一个employeeId（63fefac1ce76e418acc00ee7）。我应该怎么做才能得到工作时间的数量？
我试着用 Mongoose 聚合物，但至今没有成功。

- 收藏**

{
    _id: new ObjectId("64036f7ac0829fe25c6ef435"),
    clockIn: 2023-03-04T06:28:06.433Z,
    clockOut: 2023-03-04T10:30:08.601Z,
    employeeId: ObjectId("63feed3c60a04fbc61c52f91"),
    createdAt: 2023-03-04T16:19:06.436Z,
    updatedAt: 2023-03-04T16:19:08.601Z,
    __v: 0
  },
  {
    _id: new ObjectId("64036f8fc0829fe25c6ef43b"),
    clockIn: 2023-03-04T06:27:27.013Z,
    clockOut: 2023-03-04T15:02:28.142Z,
    employeeId: ObjectId("63feece460a04fbc61c52f89"),
    createdAt: 2023-03-04T16:19:27.014Z,
    updatedAt: 2023-03-04T16:19:28.142Z,
    __v: 0
  },
  {
    _id: new ObjectId("64036fb0c0829fe25c6ef442"),
    clockIn: 2023-03-04T06:32:00.570Z,
    clockOut: 2023-03-04T15:01:01.676Z,
    employeeId: ObjectId("63fefac1ce76e418acc00ee7"),
    createdAt: 2023-03-04T16:20:00.571Z,
    updatedAt: 2023-03-04T16:20:01.677Z,
    __v: 0
  },
  {
    _id: new ObjectId("64036fd3c0829fe25c6ef448"),
    clockIn: 2023-03-04T07:35:35.630Z,
    clockOut: 2023-03-04T16:49:27.038Z,
    employeeId: ObjectId("63fef8c7ce76e418acc00e98"),
    createdAt: 2023-03-04T16:20:35.631Z,
    updatedAt: 2023-03-04T16:49:27.044Z,
    __v: 0
  },
  {
    _id: new ObjectId("64037df808650d277693f106"),
    clockIn: 2023-03-03T06:31:56.290Z,
    clockOut: 2023-03-03T15:03:58.632Z,
    employeeId: ObjectId("63fefac1ce76e418acc00ee7"),
    createdAt: 2023-03-04T17:20:56.302Z,
    updatedAt: 2023-03-04T17:20:58.633Z,
    __v: 0
  }`

这是我的Axios Get，但结果带来了每天的工作小时数，而不是根据需要对天数求和。

- 得到**

timesheetRouter.get('/timesheet/:clockIn/:clockOut/show', async (req, res) => {
  try {
    const clockInOutTimesheet = await Timesheet.find().populate('employeeId', 'name employeeCode department fulltime')
    return res.status(200).json(clockInOutTimesheet)
  } catch (error) {
    console.log(error)
    return res.status(500).json({message: "Error code 1001"})
  }
})

结果应该是这样的：

employeeId: 63feed3c60a04fbc61c52f91 ==> 4.03 hours
employeeId: 63feece460a04fbc61c52f89 ==> 8.08 hours
employeeId: 63fefac1ce76e418acc00ee7 ==> (7.98 + 8.03) = 16.01 hours
employeeId: 63fef8c7ce76e418acc00e98 ==> 8.73 hours

JavaScript

来源：https://stackoverflow.com/questions/75639813/sum-values-from-different-objects-filtering-by-id-in-mongodb

1条答案

按热度按时间

ppcbkaq51#

需要将日期更改为ISODate
按empoyeeId和$sum平均值分组（分钟）
转换为小时

db.collection.aggregate([
  {
    $group: {
      _id: "$employeeId",
      averageTime: {
        $sum: {
          $avg: {
            $dateDiff: {
              startDate: {
                $dateFromString: {
                  dateString: "$clockIn",
                },
              },
              endDate: {
                $dateFromString: {
                  dateString: "$clockOut",
                },
              },
              unit: "minute",
            },
          },
        },
      },
    },
  },
  {
    $project: {
      hours: {
        $round: [
          {
            $divide: ["$averageTime", 60],
          },
          2,
        ],
      },
    },
  },
]);

MONGO-PLAYGROUND

赞(0）回复(0）举报 2023-03-06

我来回答

javascript 在mongodb中按ID过滤不同对象的求和值

1条答案

相关问题

热门标签

最新问答