skip to Main Content

I am trying to merge an object inside an array with the same date but with a different key name for the status key.

I have 2 collections users and canteens

The query I am trying to get the result but am not able to figure out how to merge the object with the same Date

OUTPUT

User.aggregate([
    { $sort: { workerId: 1 } },
    {
      $lookup: {
        from: "canteens",
        localField: "_id",
        foreignField: "employeeId",
        pipeline: [
          {
            $match: {
              Date: {
                $gte: new Date(fromDate),
                $lte: new Date(toDate),
              },
            },
          },
          {
            $project: {
              Date: 1,
              status: 1,
            },
          },
        ],
        as: "canteens",
      },
    },
    {
      $project: {
        _id: 1,
        workerId: 1,
        workerFirstName: 1,
        workerSurname: 1,
        workerDepartment: 1,
        workerDesignation: 1,
        locationName: 1,
        canteenData: "$canteens",
      },
    },
  ]);
[
  {
    "_id": "60e6fd3616dd663e84a925e2",
    "workerFirstName": "Firstaname",
    "workerSurname": "lastname",
    "workerId": "1",
    "locationName": "location",
    "workerDesignation": "designation",
    "workerDepartment": "department",
    "canteenData": [
      {
        "_id": "63b285b9e92eee614feb7be1",
        "status": "LUNCH",
        "Date": "2023-01-02T00:00:00.000Z"
      },
      {
        "_id": "63b2db8db10c24487201e0a2",
        "status": "DINNER",
        "Date": "2023-01-02T00:00:00.000Z"
      },
      {
        "_id": "63b39b247adbeb50bfbe3503",
        "status": "BREAK FAST",
        "Date": "2023-01-03T00:00:00.000Z"
      },
      {
        "_id": "63b3d248c076184fb07ff2c4",
        "status": "LUNCH",
        "Date": "2023-01-03T00:00:00.000Z"
      },
      {
        "_id": "63b42b8ccb57a4cb7af34015",
        "status": "DINNER",
        "Date": "2023-01-03T00:00:00.000Z"
      },
      {
        "_id": "63b4ef71e038498fe6634506",
        "status": "BREAK FAST",
        "Date": "2023-01-04T00:00:00.000Z"
      }
    ]
  },
{
    "_id": "60e6fd3616dd663e84a925e2",
    "workerFirstName": "Firstaname1",
    "workerSurname": "lastname1",
    "workerId": "2",
    "locationName": "location",
    "workerDesignation": "designation",
    "workerDepartment": "department",
    "canteenData": [
      {
        "_id": "63b285b9e92eee614feb7be1",
        "status": "LUNCH",
        "Date": "2023-01-02T00:00:00.000Z"
      },
      {
        "_id": "63b2db8db10c24487201e0a2",
        "status": "DINNER",
        "Date": "2023-01-02T00:00:00.000Z"
      },
      {
        "_id": "63b39b247adbeb50bfbe3503",
        "status": "BREAK FAST",
        "Date": "2023-01-03T00:00:00.000Z"
      },
      {
        "_id": "63b3d248c076184fb07ff2c4",
        "status": "LUNCH",
        "Date": "2023-01-03T00:00:00.000Z"
      },
      {
        "_id": "63b42b8ccb57a4cb7af34015",
        "status": "DINNER",
        "Date": "2023-01-03T00:00:00.000Z"
      },
      {
        "_id": "63b4ef71e038498fe6634506",
        "status": "BREAK FAST",
        "Date": "2023-01-04T00:00:00.000Z"
      }
    ]
  }
]

The output I am trying to get


[
  {
    "_id": "60e6fd3616dd663e84a925e2",
    "workerFirstName": "Firstanem",
    "workerSurname": "lastname",
    "workerId": "1",
    "locationName": "location",
    "workerDesignation": "designation",
    "workerDepartment": "department",
    "canteenData": [
      {
        "_id": "63b285b9e92eee614feb7be1",
        "status1": "LUNCH",
        "status2": "DINNER",
        "Date": "2023-01-02T00:00:00.000Z"
      },
      {
        "_id": "63b39b247adbeb50bfbe3503",
        "status1": "BREAK FAST",
        "status2": "LUNCH",
        "status3": "DINNER",
        "Date": "2023-01-03T00:00:00.000Z"
      },
      {
        "_id": "63b4ef71e038498fe6634506",
        "status1": "BREAK FAST",
        "Date": "2023-01-04T00:00:00.000Z"
      }
    ]
  },
{
    "_id": "60e6fd3616dd663e84a925e2",
    "workerFirstName": "Firstanem1",
    "workerSurname": "lastname1",
    "workerId": "2",
    "locationName": "location",
    "workerDesignation": "designation",
    "workerDepartment": "department",
    "canteenData": [
      {
        "_id": "63b285b9e92eee614feb7be1",
        "status1": "LUNCH",
        "status2": "DINNER",
        "Date": "2023-01-02T00:00:00.000Z"
      },
      {
        "_id": "63b39b247adbeb50bfbe3503",
        "status1": "BREAK FAST",
        "status2": "LUNCH",
        "status3": "DINNER",
        "Date": "2023-01-03T00:00:00.000Z"
      },
      {
        "_id": "63b4ef71e038498fe6634506",
        "status1": "BREAK FAST",
        "Date": "2023-01-04T00:00:00.000Z"
      }
    ]
  }
]

2

Answers


  1. One option is to add 2 steps into your $lookup pipeline aggregation:

      {$group: {
          _id: "$Date",
          _idVal: {$first: "$_id"},
          data: {$addToSet: "$status"}
      }},
      {$replaceRoot: {
          newRoot: {
            $mergeObjects: [
              {_id: "$_idVal", Date: "$_id"},
              {$arrayToObject: {
                  $reduce: {
                    input: "$data",
                    initialValue: [],
                    in: {$concatArrays: [
                        "$$value",
                        [{k: {$concat: [
                                "status",
                                {$toString: {$add: [{$size: "$$value"}, 1]}}
                              ]},
                          v: "$$this"}]
                    ]}
                  }
              }}
            ]
          }
      }}
    

    See how it works on the playground example

    Login or Signup to reply.
  2. It’s not easy to create status1, status2, ... variables dynamically + how do we know BREAK FAST should be status1 and not status2.

    Alternative solution: We $group inside correlated subqueries and push all status values into an array

    db.users.aggregate([
      {
        "$lookup": {
          "from": "canteens",
          "localField": "_id",
          "foreignField": "employeeId",
          pipeline: [
            {
              // Put your custom filters here
              $match: {}
            },
            {
              $group: {
                _id: "$Date",
                //pick "first" canteens _id
                id: {
                  $first: "$_id"
                },
                status: {
                  $push: "$status"
                }
              }
            },
            {
              $project: {
                _id: "$id",
                Date: "$_id",
                status: 1
              }
            },
            
          ],
          as: "canteenData",
        }
      }
    ])
    

    MongoPlayground

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search