Mongodb Aggregation :- Get specific fields from $lookup nested array-CodePudding

I am trying to get specific fields from the array I got after aggregate, lookup and some cond

Below you can see my query

 const attendanceData = await User.aggregate([
    {
      $match: {
        lastLocationId: Mongoose.Types.ObjectId(typeId),
        isActive: true,
      },
    },
    {
      $project: {
        _id: 1,
        workerId: 1,
        workerFirstName: 1,
        workerSurname: 1,
      },
    },
    {
      $lookup: {
        from: "attendances",
        localField: "_id",
        foreignField: "employeeId",
        as: "attendances",
      },
    },
    {
      $set: {
        attendances: {
          $filter: {
            input: "$attendances",
            cond: {
              $and: [
                {
                  $gte: ["$$this.Date", new Date(fromDate)],
                },
                {
                  $lte: ["$$this.Date", new Date(toDate)],
                },
                {
                  $eq: ["$$this.createdAs", dataType],
                },
                {
                  $eq: ["$$this.status", true],
                },
                {
                  $eq: ["$$this.workerType", workerType],
                },
              ],
            },
          },
        },
      },
    },
    { $skip: 0 },
    { $limit: 10 },
  ]);

The data as a response i get below

{
  "attendanceSheet": [
    {
      "_id": "60dd77c14524e6c116e16aaa",
      "workerFirstName": "MEGHRAJ",
      "workerSurname": "JADHAV",
      "workerId": "2036",
      "attendances": [
        {
          "_id": "6130781085b5055a15c32f2u",
          "workerId": "2036",
          "workerFullName": "MEGHRAJ JADHAV",
          "workerType": "Employee",
          "Date": "2022-10-01T00:00:00.000Z",
          "createdAs": "ABSENT"
        },
        {
          "_id": "6130781085b5055a15c32f2u",
          "workerId": "2036",
          "workerFullName": "MEGHRAJ JADHAV",
          "workerType": "Employee",
          "Date": "2022-10-02T00:00:00.000Z",
          "createdAs": "ABSENT"
        }
      ]
    },
    {
      "_id": "60dd77c24524e6c116e16c0f",
      "workerFirstName": "SANJAY",
      "workerSurname": "DUTTA",
      "workerId": "2031",
      "attendances": [
        {
          "_id": "6130781a85b5055a15c3455y",
          "workerId": "2031",
          "workerFullName": "SANJAY DUTTA",
          "workerType": "Employee",
          "Date": "2022-10-02T00:00:00.000Z",
          "createdAs": "ABSENT"
        }
      ]
    }
  ]
}

But I want data something like this below only few fields in not every fields

{
  "attendanceSheet": [
    {
      "_id": "60dd77c14524e6c116e16aaa",
      "workerFirstName": "MEGHRAJ",
      "workerSurname": "JADHAV",
      "workerId": "2036",
      "attendances": [
        {
          "_id": "6130781085b5055a15c32f2u",
          "Date": "2022-10-01T00:00:00.000Z",
          "createdAs": "ABSENT"
        },
        {
          "_id": "6130781085b5055a15c32f2u",
          "Date": "2022-10-02T00:00:00.000Z",
          "createdAs": "ABSENT"
        }
      ]
    },
    {
      "_id": "60dd77c24524e6c116e16c0f",
      "workerFirstName": "SANJAY",
      "workerSurname": "DUTTA",
      "workerId": "2031",
      "attendances": [
        {
          "_id": "6130781a85b5055a15c3455y",
          "Date": "2022-10-02T00:00:00.000Z",
          "createdAs": "ABSENT"
        }
      ]
    }
  ]
}

CodePudding user response：

You could simplify/refactor your aggregation pipeline by putting all the matching in a "$lookup" "pipeline".

db.users.aggregate([
  {
    "$match": {
      "lastLocationId": ObjectId("0123456789abcdef01234567"),
      "isActive": true
    }
  },
  {
    "$project": {
      "workerId": 1,
      "workerFirstName": 1,
      "workerSurname": 1
    }
  },
  {
    "$lookup": {
      "from": "attendances",
      "localField": "_id",
      "foreignField": "employeeId",
      "as": "attendances",
      // do all the matching here
      "pipeline": [
        {
          "$match": {
            "Date": {
              // fromDate, toDate
              "$gte": ISODate("2022-09-01T00:00:00Z"),
              "$lte": ISODate("2022-09-30T23:59:59Z")
            },
            // dataType
            "createdAs": "ABSENT",
            "status": true,
            // workerType
            "workerType": "Employee"
          }
        },
        {
          "$project": {
            "Date": 1,
            "createdAs": 1
          }
        }
      ]
    }
  },
  {$skip: 0},
  {$limit: 10}
])

Try it on mongoplayground.net.

CodePudding user response：

One option to get from what you have to the requested output is to $map and $reduce:

db.collection.aggregate([
  {
    $set: {
      attendanceSheet: {
        $map: {
          input: "$attendanceSheet",
          as: "external",
          in: {
            $mergeObjects: [
              "$$external",
              {
                attendances: {
                  $reduce: {
                    input: "$$external.attendances",
                    initialValue: [],
                    in: {
                      $concatArrays: [
                        "$$value",
                        [
                          {
                            _id: "$$this._id",
                            createdAs: "$$this.createdAs",
                            Date: "$$this.Date"
                          }
                        ]
                      ]
                    }
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
])

See how it works on the playground example

CodePudding user response：

The below modification worked for me

const attendanceData = await User.aggregate([
    {
      $match: {
        lastLocationId: Mongoose.Types.ObjectId(typeId),
        isActive: true,
      },
    },
    {
      $project: {
        _id: 1,
        workerId: 1,
        workerFirstName: 1,
        workerSurname: 1,
      },
    },
    {
      $lookup: {
        from: "attendances",
        localField: "_id",
        foreignField: "employeeId",
        as: "attendances",
      },
    },
    {
      $set: {
        attendances: {
          $filter: {
            input: "$attendances",
            cond: {
              $and: [
                {
                  $gte: ["$$this.Date", new Date(fromDate)],
                },
                {
                  $lte: ["$$this.Date", new Date(toDate)],
                },
                {
                  $eq: ["$$this.createdAs", dataType],
                },
                {
                  $eq: ["$$this.status", true],
                },
                {
                  $eq: ["$$this.workerType", workerType],
                },
              ],
            },
          },
        },
      },
    },
    {
      $set: {
        attendances: {
          $reduce: {
            input: "$attendances",
            initialValue: [],
            in: {
              $concatArrays: [
                "$$value",
                [
                  {
                    _id: "$$this._id",
                    createdAs: "$$this.createdAs",
                    Date: "$$this.Date",
                  },
                ],
              ],
            },
          },
        },
      },
    },
    { $skip: 0 },
    { $limit: 10 },
  ]);