MongoDB : - Merge object with same key into one

chiragprajapati
January 8, 2023
190 views
0 votes
2 Answers

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

Answers

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

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

Please signup or login to give your own answer.

Click here to cancel reply.

MongoDB : – Merge object with same key into one

Answers