MongoDB总有条件组和推送记录

Question

我正在尝试运行有条件的小组并推动。我不知道MongoDB聚合或nodejs是可能的。

我有以下出勤收集和轮班收藏。还为我的当前尝试创建了游乐场 mongo Playground 。

出勤

[
    {
      "_id": "62206c873b2eaac4b15d42f9",
      "employee": "6220624b3b2eaac4b15d42e8",
      "createdAt": "2022-04-28T04:11:13.036Z"
    },
    {
      "_id": "62206b173b2eaac4b15d42f1",
      "employee": "6220624b3b2eaac4b15d42e8",
      "createdAt": "2022-04-28T04:22:04.573Z"
    },
    {
      "_id": "626cb9329a6fe1422b61d4e4",
      "employee": "622062023b2eaac4b15d42e6",
      "createdAt": "2022-04-30T04:21:06.965Z"
    },
    {
      "_id": "626e19409a6fe1422b61d4f5",
      "employee": "622062023b2eaac4b15d42e6",
      "createdAt": "2022-05-01T05:23:12.342Z"
    },
    {
      "_id": "626cb9329a6fe1422b61d4e6",
      "employee": "622062023b2eaac4b15d42e6",
      "createdAt": "2022-05-01T15:42:39.827Z"
    },
    {
      "_id": "626cb9329a6fe1422b61d4e5",
      "employee": "622062023b2eaac4b15d42e6",
      "createdAt": "2022-05-02T09:46:51.439Z"
    }
  ]

shifts

[
    {
      "_id": "626965ee9a6fe1422b61d4b1",
      "employee": "6220624b3b2eaac4b15d42e8",
      "date": "2022-04-28",
      "name": "Day",
      "isNight": false
    },
    {
      "_id": "626965ee9a6fe1422b61d4b2",
      "employee": "622062023b2eaac4b15d42e6",
      "date": "2022-04-30",
      "name": "Night",
      "isNight": true
    },
    {
      "_id": "626965ee9a6fe1422b61d4b3",
      "employee": "622062023b2eaac4b15d42e6",
      "date": "2022-05-01",
      "name": "Night",
      "isNight": true
    }
  ]

我的要求是，如果isning === true然后将下一个出勤记录推到上一个日期。

我所需的JSON应该像Bellow这样的东西。

[
    {
        "_id": {
            "createdAt": "2022-04-28",
            "employee": "6220624b3b2eaac4b15d42e8"
        },
        "attendances": [
            {
                "_id": "62206c873b2eaac4b15d42f9",
                "createdAt": "2022-04-28T04:11:13.036Z",
                "employee": "6220624b3b2eaac4b15d42e8"
            },
            {
                "_id": "62206b173b2eaac4b15d42f1",
                "createdAt": "2022-04-28T04:22:04.573Z",
                "employee": "6220624b3b2eaac4b15d42e8"
            }
        ],
        "shifts": [
            {
                "_id": "626965ee9a6fe1422b61d4b1",
                "date": "2022-04-28",
                "employee": "6220624b3b2eaac4b15d42e8",
                "isNight": false,
                "name": "Day"
            }
        ]
    },
    {
        "_id": {
            "createdAt": "2022-04-30",
            "employee": "622062023b2eaac4b15d42e6"
        },
        "attendances": [
            {
                "_id": "626cb9329a6fe1422b61d4e4",
                "createdAt": "2022-04-30T04:21:06.965Z",
                "employee": "622062023b2eaac4b15d42e6"
            },
            {
                "_id": "626e19409a6fe1422b61d4f5",
                "createdAt": "2022-05-01T05:23:12.342Z",
                "employee": "622062023b2eaac4b15d42e6"
            }
        ],
        "shifts": [
            {
                "_id": "626965ee9a6fe1422b61d4b2",
                "date": "2022-04-30",
                "employee": "622062023b2eaac4b15d42e6",
                "isNight": true,
                "name": "Night"
            }
        ]
    },
    {
        "_id": {
            "createdAt": "2022-05-01",
            "employee": "622062023b2eaac4b15d42e6"
        },
        "attendances": [
            {
                "_id": "626cb9329a6fe1422b61d4e6",
                "createdAt": "2022-05-01T15:42:39.827Z",
                "employee": "622062023b2eaac4b15d42e6"
            },
            {
                "_id": "626cb9329a6fe1422b61d4e5",
                "createdAt": "2022-05-02T09:46:51.439Z",
                "employee": "622062023b2eaac4b15d42e6"
            }
        ],
        "shifts": [
            {
                "_id": "626965ee9a6fe1422b61d4b2",
                "date": "2022-04-30",
                "employee": "622062023b2eaac4b15d42e6",
                "isNight": true,
                "name": "Night"
            }
        ]
    }
]

我该如何处理mongodb或nodejs？

Answer 1

我不确定这是您想要的，但是可以进行更新。这是否回答您的问题：

db.shifts.aggregate([
  {
    $set: {
      date: {$dateFromString: {dateString: "$date"}},
      dateString: "$date"
    }
  },
  {
    $lookup: {
      from: "attendances",
      let: {
        employee: "$employee",
        date: "$date",
        isNight: "$isNight"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              "$and": [
                {$eq: ["$employee", "$employee"]},
                {$lt: [{$toDate: "$createdAt"},
                    {$dateAdd: {startDate: "$date", unit: "day", amount: 2}}
                  ]
                },
                {$gt: [{$toDate: "$createdAt"}, "$date"]}
              ]
            }
          }
        }
      ],
      as: "attendances"
    }
  },
  {
    $set: {
      attendances: {
        $map: {
          input: "$attendances",
          as: "item",
          in: {
            $mergeObjects: [
              "$item",
              {
                today: {
                  $cond: [
                    {
                      $eq: [
                        {
                          $dateToString: {
                            date: {
                              $toDate: "$item.createdAt"
                            },
                            format: "%Y-%m-%d",
                            timezone: "Asia/Kolkata"
                          }
                        },
                        "$dateString"
                      ]
                    },
                    true,
                    false
                  ]
                }
              }
            ]
          }
        }
      }
    }
  },
  {
    $addFields: {
      attendancesOverNightCount: {
        $reduce: {
          input: "$attendances",
          initialValue: 0,
          in: {
            $add: ["$value", {$cond: [{$eq: ["$this.today", false]}, 1, 0]}]
          }
        }
      }
    }
  },
  {
    $set: {
      attendances: {
        $slice: [
          "$attendances",
          {
            $subtract: [
              {$size: "$attendances"},
              {
                $cond: [
                  {$eq: ["$isNight", true]},
                  {"$subtract": ["$attendancesOverNightCount", 1]},
                  "$attendancesOverNightCount"
                ]
              }
            ]
          }
        ]
      },
      shifts: [
        {
          _id: "$_id",
          employee: "$employee",
          isNight: "$isNight",
          name: "$isNight",
          date: "$dateString"
        }
      ]
    }
  },
  {
    $project: {
      shifts: 1,
      attendances: {
        $map: {
          input: "$attendances",
          as: "item",
          in: {
            createdAt: "$item.createdAt",
            employee: "$item.employee",
            _id: "$item._id"
          }
        }
      },
      _id: {
        createdAt: "$date",
        employee: "$employee"
      }
    }
  }
])

Playground示例示例

Answer 2

它为我解决了问题。

DB.JSON

This is your sample data.

[
    {
        "_id": {
            "createdAt": "2022-04-28",
            "employee": "6220624b3b2eaac4b15d42e8"
        },
        "attendances": [
            {
                "_id": "62206c873b2eaac4b15d42f9",
                "createdAt": "2022-04-28T04:11:13.036Z",
                "employee": "6220624b3b2eaac4b15d42e8"
            },
            {
                "_id": "62206b173b2eaac4b15d42f1",
                "createdAt": "2022-04-28T04:22:04.573Z",
                "employee": "6220624b3b2eaac4b15d42e8"
            }
        ],
        "shifts": [
            {
                "_id": "626965ee9a6fe1422b61d4b1",
                "date": "2022-04-28",
                "employee": "6220624b3b2eaac4b15d42e8",
                "isNight": false,
                "name": "Day"
            }
        ]
    },
    {
        "_id": {
            "createdAt": "2022-04-30",
            "employee": "622062023b2eaac4b15d42e6"
        },
        "attendances": [
            {
                "_id": "626cb9329a6fe1422b61d4e4",
                "createdAt": "2022-04-30T04:21:06.965Z",
                "employee": "622062023b2eaac4b15d42e6"
            },
            {
                "_id": "626e19409a6fe1422b61d4f5",
                "createdAt": "2022-05-01T05:23:12.342Z",
                "employee": "622062023b2eaac4b15d42e6"
            }
        ],
        "shifts": [
            {
                "_id": "626965ee9a6fe1422b61d4b2",
                "date": "2022-04-30",
                "employee": "622062023b2eaac4b15d42e6",
                "isNight": true,
                "name": "Night"
            }
        ]
    },
    {
        "_id": {
            "createdAt": "2022-05-01",
            "employee": "622062023b2eaac4b15d42e6"
        },
        "attendances": [
            {
                "_id": "626cb9329a6fe1422b61d4e6",
                "createdAt": "2022-05-01T15:42:39.827Z",
                "employee": "622062023b2eaac4b15d42e6"
            },
            {
                "_id": "626cb9329a6fe1422b61d4e5",
                "createdAt": "2022-05-02T09:46:51.439Z",
                "employee": "622062023b2eaac4b15d42e6"
            }
        ],
        "shifts": [
            {
                "_id": "626965ee9a6fe1422b61d4b2",
                "date": "2022-04-30",
                "employee": "622062023b2eaac4b15d42e6",
                "isNight": true,
                "name": "Night"
            }
        ]
    }
]

脚本

The node.js script to make changes.

let yourData = require('./DB.json')

// 1 Console output
console.log(yourData[1].shifts);

for (let index in yourData) {
    if (yourData[index].shifts[0].isNight) {
        yourData[index].shifts[0].date = yourData[index].attendances[1].createdAt.split("T")[0];
    }
}

// 2 Console output
console.log(yourData[1].shifts);

1号在更改之前输出

。

[
  {
    _id: '626965ee9a6fe1422b61d4b2',
    date: '2022-04-30',
    employee: '622062023b2eaac4b15d42e6',
    isNight: true,
    name: 'Night'
  }
]

2个更改后的控制台输出

。

[
  {
    _id: '626965ee9a6fe1422b61d4b2',
    date: '2022-05-01',
    employee: '622062023b2eaac4b15d42e6',
    isNight: true,
    name: 'Night'
  }
]