Create a view from mongo collection-CodePudding

I have a mongo collection with records like

and so on. The sample record in JSON format

[{
 empId:'123',
 empName:'Emp1',
 shiftHours:'Regular'
},
{
 empId:'123',
 empName:'Emp1',
 shiftHours:'Morning'
}
]

Basically an employee can work in regular shift(9am-6 pm) or morning shift (6 am-3 pm) or night shift (9pm-6 am). The hours are just example here but the idea is that the working hours are categorized in 3 shifts. I want to create a view with flat structure per employee like this

and so on.

I am trying to understand what's the best way to create such a flat view (coming from SQL background, I think a procedure/function has to be written) but not sure what's the best way to do so using No-Sql (Mongo db). Any suggestions?

CodePudding user response：

$group by empId and conditionally $sum by shiftHours.

db.collection.aggregate([
  {
    $group: {
      _id: "$empId",
      empName: {
        $first: "$empName"
      },
      Morning: {
        $sum: {
          "$cond": {
            "if": {
              $eq: [
                "$shiftHours",
                "Morning"
              ]
            },
            "then": 1,
            "else": 0
          }
        }
      },
      Regular: {
        $sum: {
          "$cond": {
            "if": {
              $eq: [
                "$shiftHours",
                "Regular"
              ]
            },
            "then": 1,
            "else": 0
          }
        }
      },
      Evening: {
        $sum: {
          "$cond": {
            "if": {
              $eq: [
                "$shiftHours",
                "Evening"
              ]
            },
            "then": 1,
            "else": 0
          }
        }
      }
    }
  },
  {
    $set: {
      Morning: {
        $cond: [
          {
            $gt: [
              "$Morning",
              0
            ]
          },
          "Y",
          "N"
        ]
      },
      Regular: {
        $cond: [
          {
            $gt: [
              "$Regular",
              0
            ]
          },
          "Y",
          "N"
        ]
      },
      Evening: {
        $cond: [
          {
            $gt: [
              "$Evening",
              0
            ]
          },
          "Y",
          "N"
        ]
      }
    }
  }
])

Mongo Playground