Question 116274 in Mongodb Question posted in
The official documentation can be found here.

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

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": "FIRST NAME1",
      "workerSurname": "SURNAME1",
      "workerId": "1",
      "attendances": [
        {
          "_id": "6130781085b5055a15c32f2u",
          "workerId": "1",
          "workerFullName": "FIRST NAME",
          "workerType": "Employee",
          "Date": "2022-10-01T00:00:00.000Z",
          "createdAs": "ABSENT"
        },
        {
          "_id": "6130781085b5055a15c32f2u",
          "workerId": "1",
          "workerFullName": "FIRST NAME",
          "workerType": "Employee",
          "Date": "2022-10-02T00:00:00.000Z",
          "createdAs": "ABSENT"
        }
      ]
    },
    {
      "_id": "60dd77c24524e6c116e16c0f",
      "workerFirstName": "FIRST NAME2",
      "workerSurname": "Surname",
      "workerId": "2",
      "attendances": [
        {
          "_id": "6130781a85b5055a15c3455y",
          "workerId": "2",
          "workerFullName": "FIRST NAME2",
          "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": "FIRST NAME1",
      "workerSurname": "Surname",
      "workerId": "1",
      "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": "FIRST NAME2",
      "workerSurname": "Surname",
      "workerId": "2",
      "attendances": [
        {
          "_id": "6130781a85b5055a15c3455y",
          "Date": "2022-10-02T00:00:00.000Z",
          "createdAs": "ABSENT"
        }
      ]
    }
  ]
}
Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    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 },
  ]);

  2. 0

    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

    Login or Signup to reply.
  3. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search