Tuesday 16 May 2017

MongoDB join two collection using aggregration

I have two collection Floor and Access. I want to remove unmatched items and format like below "expected Format". Floor: { "_id" : ObjectId("5913087adb4242dc9b4d9cbd"), "No" : 0, "RoomDetails" : [ { "roomName" : "Testing Room", "roomId" : "59143b7a938345f2a1625d4a" }, { "roomName" : "Conf Room", "roomId" : "59143bb0938345f2a1625d4b" }, { "roomName" : "Marketting", "roomId" : "59158e6bde86ee986f818b52" } ] }

/* 2 */
{
    "_id" : ObjectId("5913087adb4242dc9b4d9cc1"),
    "No" : 4,
    "RoomDetails" : [ 
        {
            "roomName" : "Fun room",
            "roomId" : "59158b6f735fb89404a75f59"
        }, 
        {
            "roomName" : "HR room",
            "roomId" : "59158b78735fb89404a75f5a"
        }, 
        {
            "roomName" : "Managers room",
            "roomId" : "59158b87735fb89404a75f5b"
        }, 
        {
            "roomName" : "accounts",
            "roomId" : "59158b93735fb89404a75f5c"
        }
    ]
}

Access:

{
    "_id" : ObjectId("590afc16887c66231db0af86"),
    "userName" : “David”,
    "userId" : "c990d3d7-2093-4b94-8db5-fe258b684152",
    "deviceId" : "",
    "location" : [ 
        "59143b7a938345f2a1625d4a", 
        "59143bb0938345f2a1625d4b", 
        "59158b6f735fb89404a75f59", 
            ]
}

**Expected Result:**

[
  {
    "floorId": "5913087adb4242dc9b4d9cbd",
    "floorNo": 0,
    "RoomDetails": [
      {
        "roomName": "Testing Room",
        "roomId": "59143b7a938345f2a1625d4a"
      },
      {
        "roomName": "Conf Room",
        "roomId": "59143bb0938345f2a1625d4b"
      }
    ]
  },
  {
    "floorId": "5913087adb4242dc9b4d9cc1",
    "floorNo": 4,
    "RoomDetails": [
      {
        "roomName": "Fun room",
        "roomId": "59158b6f735fb89404a75f59"
      },
      {
        "roomName": "HR room",
        "roomId": "59158b78735fb89404a75f5a"
      },
      {
        "roomName": "Managers room",
        "roomId": "59158b87735fb89404a75f5b"
      },
      {
        "roomName": "accounts",
        "roomId": "59158b93735fb89404a75f5c"
      }
    ]
  }
]

I tried below query but didn't get expected result. Could you help me to get the expected result.

  { "$match": { "userId": "c990d3d7-2093-4b94-8db5-fe258b684152" } },
      {"$unwind": "$location"},
      { 
        "$lookup": { 
            "from": 'Floor', 
            "localField": 'location', 
            "foreignField": 'RoomDetails.roomId', 
            "as": 'locationDetails' 
        }

      },
      { "$unwind": "$locationDetails"},
      {
        "$group": {

            "_id": "$locationDetails._id",
            "floor": { "$first": "$locationDetails.No" },
            "roomId": {"$push": "$location"},
             "roo": { "$first": "$locationDetails.RoomDetails" }

        }

       },
       {"$sort":{"floor":1}}

Thanks.



via IOS Developer

No comments:

Post a Comment