时间戳

首页 > 解决方案 > MongoDB：如何聚合和 $group 然后过滤特定日期

node.js - MongoDB：如何聚合和 $group 然后过滤特定日期

问题描述

员工模式

const employeeSchema = new mongoose.Schema(
  {
    name: {
      type: String,
      required: true,
      trim: true,
    },
    email: {
      type: String,
      unique: true,
      required: true,
      trim: true,
      lowercase: true,
      validate(value) {
        if (!validator.isEmail(value)) {
          throw new Error('Email is invalid');
        }
      },
    },
    password: {
      type: String,
      required: true,
      trim: true,
      minLength: 6,
      validate(value) {
        if (value.toLowerCase().includes('password')) {
          throw new Error("Password can not contain a word 'password'.");
        }
      },
    },
    birthdate: {
      type: Date,
      required: true,
    },
    cellphone: {
      type: String,
      required: true,
      trim: true,
    },
    gender: {
      type: String,
      enum: ['남성', '여성'],
      required: true,
    },
    hourly_wage: {
      type: Number,
      trim: true,
      default: 0,
    },
    timeClocks: [
      {
        type: new mongoose.Schema({
          start_time: {
            type: Date,
            required: true,
          },
          end_time: {
            type: Date,
          },
          wage: {
            type: Number,
            required: true,
          },
          total: {
            type: Number,
          },
          totalWorkTime: {
            type: Number
          }
        }),
      },
    ],
    role: {
      type: String,
      enum: ['staff'],
      default: 'staff',
    },
    stores: [
      {
        location: {
          type: mongoose.Schema.Types.ObjectId,
          required: true,
          ref: 'Location',
        },
      },
    ],
    status: {
      //현재 재직상태
      type: String,
      enum: ['재직자', '퇴직자'],
      default: '재직자',
    },
    tokens: [
      {
        token: {
          type: String,
          required: true,
        },
      },
    ],
  },
  {
    timestamps: true,
  }
);

到目前为止我做了什么

const employees = shifts.map((d) => d.owner._id);

    //timeclock
    const temp = await Employee.aggregate([
      {
        $match: {
          _id: { $in: employees },
        },
      },
      {
        $sort: { 'timeClocks.start_time': 1 },
      },
      {
        $unwind: { path: '$timeClocks', preserveNullAndEmptyArrays: true },
      },
      {
        $group: {
          _id: '$_id',
          name: { $first: '$name' },
          timeClock: {
            $push: '$timeClocks',
          },
        },
      },
    ]);

我的结果

{
    "shifts": [
        {
            "_id": "60e05b188be53900280bcdf2",
            "date": "2021-07-09T00:00:00.000Z",
            "day": "Fri",
            "start": "2021-07-09T09:41:00.000Z",
            "end": "2021-07-09T21:42:00.000Z",
            "owner": {
                "_id": "60cd9a3cb4ddcc00285b0df9",
                "name": "Dr. dd"
            },
            "location": "60cd99b1b4ddcc00285b0df3",
            "__v": 0
        }
    ],
    "timeClock": [
        {
            "_id": "60cd9a3cb4ddcc00285b0df9",
            "name": "Dr. dd",
            "timeClock": [
                {
                    "_id": "60def63d19648a00286f0539",
                    "start_time": "2021-05-04T02:19:00.000Z",
                    "end_time": "2021-05-04T14:42:00.000Z",
                    "wage": 8720,
                    "total": 107735,
                    "totalWorkTime": 743
                },
                {
                    "_id": "60def63f19648a00286f053d",
                    "start_time": "2021-05-02T08:12:00.000Z",
                    "end_time": "2021-05-02T22:24:00.000Z",
                    "wage": 8720,
                    "total": 123540,
                    "totalWorkTime": 852
                },
                {
                    "_id": "60def64119648a00286f0541",
                    "start_time": "2021-05-10T20:14:00.000Z",
                    "end_time": "2021-05-10T22:17:00.000Z",
                    "wage": 8720,
                    "total": 17835,
                    "totalWorkTime": 123
                },
        }
   ]

预期结果(2021-05-10)

{
    "shifts": [
        {
            "_id": "60e05b188be53900280bcdf2",
            "date": "2021-07-09T00:00:00.000Z",
            "day": "Fri",
            "start": "2021-07-09T09:41:00.000Z",
            "end": "2021-07-09T21:42:00.000Z",
            "owner": {
                "_id": "60cd9a3cb4ddcc00285b0df9",
                "name": "Dr. dd"
            },
            "location": "60cd99b1b4ddcc00285b0df3",
            "__v": 0
        }
    ],
    "timeClock": [
        {
            "_id": "60cd9a3cb4ddcc00285b0df9",
            "name": "Dr. dd",
            "timeClock": {
                    "_id": "60def64119648a00286f0541",
                    "start_time": "2021-05-10T20:14:00.000Z",
                    "end_time": "2021-05-10T22:17:00.000Z",
                    "wage": 8720,
                    "total": 17835,
                    "totalWorkTime": 123
                },
        }
    ]

我通过参数接收“日期字符串”示例（“URL/2021-05-10”）并尝试查询具有相同日期时间时钟的所有员工。还试图发回我查询的所有内容，而不是与 timeClocks 不同的日期。如何过滤掉不同的日期？

标签： node.jsmongodbmongoose

解决方案

您现在有字符串2021-05-10，您需要$match在您的组之前有一个阶段，以便您可以过滤掉timeClock. 就像是：

{ $match: { 'timeClocks.start_time': new Date('2021-05-10') } }

根据您的要求修改匹配阶段，例如添加$gte或$lte类似的东西。

推荐阅读