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

Question

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

@rickhg12hs can you please show me or answer this question so i can see it? — chirag prajapati
– chirag prajapati, Commented Nov 8, 2022 at 10:08
$eq: ["$$this.status", true]? ... Where is "status" in attendances doc? — rickhg12hs
– rickhg12hs, Commented Nov 8, 2022 at 10:15

rickhg12hs · Accepted Answer · 2022-11-08 11:14:44Z

2

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.

answered Nov 8, 2022 at 11:14

rickhg12hs

12k6 gold badges31 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

chirag prajapati Over a year ago

hey, you have amazing Database knowledge, how can I contact you if I needed some answers?

rickhg12hs Over a year ago

@chiragprajapati Thanks for the praise - I had a great professor. I'm not available for employment opportunities at this time, but I surf mongodb tagged questions on SO several times a week. There are several other very skilled answerers here that often answer well asked questions.

nimrod serok · Accepted Answer · 2022-11-08 10:08:48Z

1

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

answered Nov 8, 2022 at 10:08

nimrod serok

16.1k2 gold badges15 silver badges36 bronze badges

1 Comment

chirag prajapati Over a year ago

your answer is correct I used the code from the below attendance, you can see it in the answer section

chirag prajapati · Accepted Answer · 2022-11-08 10:26:18Z

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

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related