CODEWITHSUNDEEP
mongodbaggregation-frameworklookup
Chinmay Waghmare
Chinmay Waghmare

Reputation: 5456

Left join with $lookup and $group

I have 2 collection ServiceProvider and Parents

ServiceProvider:

{
    "_id" : ObjectId("57285bc0588aeca4178b4567"),
    "ServiceProviderID" : 193,
    "Title" : "test1",
    "PostedMessage" : "test1",
    "TotalComments" : 0,
    "TotalShares" : 0,
    "TotalThanks" : 0,
    "AddedOn" : "2016-05-03 08:05:20",
    "LastModifiedOn" : "2016-05-03 08:05:20",
    "PostAttachment" : {
        "ImagePath" : ""
    },
    "PostCommentUserIDs" : [
        "193",
        "3"
    ],
    "PostComments" : [
        {
            "ID" : "14622628001303558386",
            "UserID" : 3,
            "CommentMessage" : "comment by test",
            "TotalThanks" : 0,
            "AddedOn" : "2016-05-03 08:06:40",
            "LastModifiedOn" : "2016-05-03 08:06:40",
            "CommentAttachment" : {
                "ImagePath" : ""
            }
        }
    ],
    "Subscriber" : [
        "193",
        "3"
    ]
}

Parents:

{
    "_id" : ObjectId("572447e6588aec32108b4567"),
    "ID" : "1",
    "UID" : "2uwWMABSjBPWdAlSnglAlsY7A1cD1Q7EmHiM9wehxXyY99AQs6eC7rEt",
    "Name" : "test1",
    "ProfileImagePath" : "Penguins_1#SIZE#.jpg"
}
{
    "_id" : ObjectId("572447e6588aec32108b4568"),
    "ID" : "2",
    "UID" : "NM8H6oYQW33dRPxn48wkcMYyciYqagRiXUO0cLh0Lvws6eC7rEt",
    "Name" : "test2",
    "ProfileImagePath" : "fb_profilepic_879157302148180#SIZE#.jpg"
}
{
    "_id" : ObjectId("572447e6588aec32108b4569"),
    "ID" : "3",
    "UID" : "0Xb1fHqzR3HZVJWQc8aAbB77UkwSnmasdQrtp9qySVdqipsYs6eC7rEt",
    "Name" : "test3",
    "ProfileImagePath" : "images3_3_1460348144#SIZE#.jpeg"
}
{
    "_id" : ObjectId("572447e6588aec32108b456a"),
    "ID" : "4",
    "UID" : "xXyY99NaNixXyY99IfuJx4Is8Fv15t9lMC8RYpSgaAbB77zmB65c7vccvYs6eC7rEt",
    "Name" : "test4",
    "ProfileImagePath" : "Jyoti_4#SIZE#.png"
}
{
    "_id" : ObjectId("572447e6588aec32108b456b"),
    "ID" : "5",
    "UID" : "leyerH3hMY8UA3ONddJHDOHxXyY99IhhOgAImLZoQJ10RRxXyY99Us6eC7rEt",
    "Name" : "test8",
    "ProfileImagePath" : "woman-1_5#SIZE#.jpg"
}
{
    "_id" : ObjectId("572447e6588aec32108b456c"),
    "ID" : "6",
    "UID" : "B414HAxXyY996QezhAmWgJQmjoWUEhsNghXAcKRTEbNNHJXYs6eC7rEt",
    "Name" : "test5",
    "ProfileImagePath" : "Chrysanthemum_6#SIZE#.jpg"
}

I want to join ServiceProvider.PostCommentUserIDs with Parents collection to get the parent information.

I have tried following query:

db.ServiceProvider.aggregate([
   {
      $unwind: "$PostCommentUserIDs"
   },
   {
      $lookup:
         {
            from: "Parents",
            localField: "PostCommentUserIDs",
            foreignField: "ID",
            as: "ParentDetailsArr"
        }
   },
   {
      $match: { "ParentDetailsArr": { $ne: [] }}
   }
])

But it gives me 2 results with the same ServiceProvider._id i.e same document.

{
    "_id" : ObjectId("57285bc0588aeca4178b4567"),
    "ServiceProviderID" : 193,
    "Title" : "test1",
    "PostedMessage" : "test1",
    "TotalComments" : 0,
    "TotalShares" : 0,
    "TotalThanks" : 0,
    "AddedOn" : "2016-05-03 08:05:20",
    "LastModifiedOn" : "2016-05-03 08:05:20",
    "PostAttachment" : {
        "ImagePath" : ""
    },
    "PostCommentUserIDs" : "193",
    "PostComments" : [
        {
            "ID" : "14622628001303558386",
            "UserID" : 3,
            "CommentMessage" : "comment by test",
            "TotalThanks" : 0,
            "AddedOn" : "2016-05-03 08:06:40",
            "LastModifiedOn" : "2016-05-03 08:06:40",
            "CommentAttachment" : {
                "ImagePath" : ""
            }
        }
    ],
    "Subscriber" : [
        "193",
        "3"
    ],
    "ParentDetailsArr" : [
        {
            "_id" : ObjectId("572447e6588aec32108b45c6"),
            "ID" : "193",
            "UID" : "tvzRNsSiOVca8DoOEV9tMkzU4e0Rx4aMMDcQw3fb88Us6eC7rEt",
            "Name" : "test1",
            "ProfileImagePath" : null
        },
        {
            "_id" : ObjectId("572856a4588aec01178b45c6"),
            "ID" : "193",
            "UID" : "tvzRNsSiOVca8DoOEV9tMkzU4e0Rx4aMMDcQw3fb88Us6eC7rEt",
            "Name" : "test1",
            "ProfileImagePath" : "images_193_1462255293#SIZE#.jpg"
        }
    ]
}
{
    "_id" : ObjectId("57285bc0588aeca4178b4567"),
    "ServiceProviderID" : 193,
    "Title" : "test1",
    "PostedMessage" : "test1",
    "TotalComments" : 0,
    "TotalShares" : 0,
    "TotalThanks" : 0,
    "AddedOn" : "2016-05-03 08:05:20",
    "LastModifiedOn" : "2016-05-03 08:05:20",
    "PostAttachment" : {
        "ImagePath" : ""
    },
    "PostCommentUserIDs" : "3",
    "PostComments" : [
        {
            "ID" : "14622628001303558386",
            "UserID" : 3,
            "CommentMessage" : "comment by test",
            "TotalThanks" : 0,
            "AddedOn" : "2016-05-03 08:06:40",
            "LastModifiedOn" : "2016-05-03 08:06:40",
            "CommentAttachment" : {
                "ImagePath" : ""
            }
        }
    ],
    "Subscriber" : [
        "193",
        "3"
    ],
    "ParentDetailsArr" : [
        {
            "_id" : ObjectId("572447e6588aec32108b4569"),
            "ID" : "3",
            "UID" : "0Xb1fHqzR3HZVJWQc8aAbB77UkwSnmasdQrtp9qySVdqipsYs6eC7rEt",
            "Name" : "test2",
            "ProfileImagePath" : "images3_3_1460348144#SIZE#.jpeg"
        },
        {
            "_id" : ObjectId("572856a4588aec01178b4569"),
            "ID" : "3",
            "UID" : "0Xb1fHqzR3HZVJWQc8aAbB77UkwSnmasdQrtp9qySVdqipsYs6eC7rEt",
            "Name" : "test2",
            "ProfileImagePath" : "images3_3_1460348144#SIZE#.jpeg"
        }
    ]
}

My question is how to apply group by _id OR how to reconstruct the above query so that I get the Parent info array with each document.

Upvotes: 1

Views: 1164

Answers (1)

libik
libik

Reputation: 23029

This is what unwind does. As written in https://docs.mongodb.org/v3.0/reference/operator/aggregation/unwind/

Consider an inventory with the following document:

{ "_id" : 1, "item" : "ABC1", sizes: [ "S", "M", "L"] }

The following aggregation uses the $unwind stage to output a document for each element in the sizes array:

db.inventory.aggregate( [ { $unwind : "$sizes" } ] )

The operation returns the following results:

{ "_id" : 1, "item" : "ABC1", "sizes" : "S" }
{ "_id" : 1, "item" : "ABC1", "sizes" : "M" }
{ "_id" : 1, "item" : "ABC1", "sizes" : "L" }

The standard case is, that you unwind resource you want to lookup/sort or do any other operation with it and then you group-by it to get to the "same state" as before.

In your case, you want probably to group it as following

{$group : { _id : "$_id" } }

You will very likely need to use $push afterwards (google it)

Upvotes: 2

Related Questions