Reduce a sub array into an object with a custom formula, MongoDB aggregation

Question

I'm looking for a way to reduce an array of elements into an object based on a custom requirement.

Considering a collection like:

[
 { 
   name: "David",
   checkins: [
       {
          _id: "612e162d439cb04934d13f9e",
          in_at: "2021-09-12T08:02:00.000Z",
          out_at: "2021-09-12T09:03:00.000Z"
       },
       {
          _id: "612e162d439cb04934d13f9f",
          in_at: "2021-09-12T10:04:00.000Z",
          out_at: "2021-09-12T11:05:00.000Z"
       },
       {
          _id: "612e162d439cb04934d13f9g",
          in_at: "2021-09-12T12:02:00.000Z",
          out_at: "2021-09-12T14:03:00.000Z"
       }
   ] 
 },
 { 
   name: "Wilson",
   checkins: [
       {
          _id: "612e162d439cb04934d13f9e",
          in_at: "2021-09-12T08:02:00.000Z",
          out_at: "2021-09-12T09:03:00.000Z"
       },
       {
          _id: "612e162d439cb04934d13f9f",
          in_at: "2021-09-12T11:04:00.000Z",
          out_at: "2021-09-12T12:05:00.000Z"
       },
       {
          _id: "612e162d439cb04934d13f9g",
          in_at: "2021-09-12T13:02:00.000Z",
          out_at: "2021-09-12T14:03:00.000Z"
       }
   ]  
 }
]

Now what I need is that to reduce checkins array into a single object and attach it to a new property checkin. Just need to merge the first element of the array and the last element of the array. if only have one element, just need to use itself, if array is empty, checkin has to be null. output object should be:

{
   _id: 1st_element_id,
   in_at: 1st_element_in_at,
   out_at: last_emenet_out_at
}

So the as per the example, the expected result is:

[
        {
            name: "David",
            checkin: {
                _id: "612e162d439cb04934d13f9e",
                in_at: "2021-09-12T08:02:00.000Z",
                out_at: "2021-09-12T14:03:00.000Z"
            }
        },
        {
            name: "Wilson",
            checkin: {
                _id: "612e162d439cb04934d13f9e",
                in_at: "2021-09-12T08:02:00.000Z",
                out_at: "2021-09-12T14:03:00.000Z"
            }
        }
]

Appreciate any help, Thanks!

turivishal · Accepted Answer · 2021-09-28T18:09:26.843

$first to get first element of array
$last to get last element of array
$mergeObjects to merge first and last out_at property objects

db.collection.aggregate([
  {
    $addFields: {
      checkin: {
        $mergeObjects: [
          {
            $first: "$checkins"
          },
          {
            out_at: {
              $last: "$checkins.out_at"
            }
          }
        ]
      }
    }
  }
])

Playground

Reduce a sub array into an object with a custom formula, MongoDB aggregation

1 Answers1