MongoDb aggregate conditional group and push record

Question

I was trying run a conditional group and push. I don’t know if it is possible with MongoDb aggregate or with NodeJs.

I have attendance collection and shifts collection like below. And also created Playground for my current try Mongo Playground.

Attendances

[
    {
      "_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
    }
  ]

My requirement is, if isNight === true then push the next attendance record to previous date.

My required JSON should something like 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"
            }
        ]
    }
]

How can I do with MongoDb or NodeJs?

Answer 1

It solved the problem for me.

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"
            }
        ]
    }
]

Script.js

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 Console output

Before changes.

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

2 Console output

After changes.

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

Answer 2

I'm not sure if this is what you want, but it can be updated. does this answer your question:

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 example