CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Pankaj Kamadiya
Pankaj Kamadiya

Reputation: 96

Filter from two collections

I have two collections Employees, Clients

Employees schema has following record

{
    "_id" : ObjectId("5a852dcd0290f7eca89e9a79"),
    "FirstName" : "raj",
    "LastName" : "patel",
    "Forms" : [{
            "ClientId" : ObjectId("5a8528ed0290f7eca89e9a5f"),
            "ProjectId" : ObjectId("5a856fde0290f7eca89e9a88"),
            "FormId" : ObjectId("5a6eeb9e3bf43426d4d31774")
        }, {
            "ClientId" : ObjectId("5a87f593d59a6fb5249c72ad"),
            "ProjectId" : ObjectId("5a87f593d59a6fb5249c72b1"),
            "FormId" : ObjectId("5a6eec263bf43426d4d31780")
        }, {
            "ClientId" : ObjectId("5a8528ed0290f7eca89e9a5f"),
            "ProjectId" : ObjectId("5a856d850290f7eca89e9a85"),
            "FormId" : ObjectId("5a6eec263bf43426d4d31780")
        }]
}

Clients schema has following record

{
    "_id" : ObjectId("5a8528ed0290f7eca89e9a5f"),
    "CompanyName" : "test",
    "Email" : "[email protected]",
    "PhoneNumber" : "(987)654-3210",
    "Projects" : [{
            "_id" : ObjectId("5a856ca70290f7eca89e9a7f"),
            "Name" : "Feedback",
            "Description" : "Feedback form",
            "Forms" : [
                ObjectId("5a6eeace1a692b18b4a4ae3a"),
                ObjectId("5a688a799109bd17b4e8c8b2")
            ]
        }, {
            "_id" : ObjectId("5a856d700290f7eca89e9a82"),
            "Name" : "complaint",
            "Description" : "sfsddsf !",
            "Forms" : [
                ObjectId("5a66d56ffe50af19647f66dd"),
                ObjectId("5a66d4ccfe50af19647f66db"),
                ObjectId("5a5f18169c50261c24ca5f0a")
            ]
        }, {
            "_id" : ObjectId("5a856d850290f7eca89e9a85"),
            "Name" : "Test Project",
            "Description" : "For testing purpose",
            "Forms" : [
                ObjectId("5a62e2a8f85cf41bd0a8f522"),
                ObjectId("5a6eec263bf43426d4d31780")
            ]
        }, {
            "_id" : ObjectId("5a856fde0290f7eca89e9a88"),
            "Name" : "simple project",
            "Description" : "Project",
            "Forms" : [
                ObjectId("5a6eeb9e3bf43426d4d31774")
            ]
        }
    ]
},{
    "_id" : ObjectId("5a87f593d59a6fb5249c72ad"),
    "CompanyName" : "PNB",
    "Email" : "[email protected]",
    "PhoneNumber" : "(987)654-3210",
    "Projects" : [{
            "_id" : ObjectId("5a87f593d59a6fb5249c72ae"),
            "Name" : "Home Loan",
            "Description" : "Get home loan!",
            "Forms" : [
                ObjectId("5a6eeb9e3bf43426d4d31774")
            ]
        }, {
            "_id" : ObjectId("5a87f593d59a6fb5249c72b1"),
            "Name" : "Car Loan",
            "Description" : "For testing purpose of android app",
            "Forms" : [
                ObjectId("5a6eec263bf43426d4d31780")
            ]
        }
    ]
}

My output is like below

{
    "_id" : "5a852dcd0290f7eca89e9a79",
    "FirstName" : "raj",
    "LastName" : "patel",
    "Clients" : [{
            "_id" : "5a87f593d59a6fb5249c72ad",
            "CompanyName" : "PNB",
            "Email" : "[email protected]",
            "PhoneNumber" : "(987)654-3210",
            "Projects" : [{
                    "_id" : "5a87f593d59a6fb5249c72b1",
                    "Name" : "Car Loan",
                    "Description" : "For testing purpose of android app",
                    "Forms" : [
                        "5a6eec263bf43426d4d31780"
                    ]
                }
            ]
        }, {
            "_id" : "5a8528ed0290f7eca89e9a5f",
            "CompanyName" : "test",
            "Email" : "[email protected]",
            "PhoneNumber" : "(987)654-3210",
            "Projects" : [{
                    "_id" : "5a856fde0290f7eca89e9a88",
                    "Name" : "simple project",
                    "Description" : "Project",
                    "Forms" : [
                        "5a6eeb9e3bf43426d4d31774"
                    ]
                }, {
                    "_id" : "5a856d850290f7eca89e9a85",
                    "Name" : "Test Project",
                    "Description" : "For testing purpose",
                    "Forms" : [
                        "5a62e2a8f85cf41bd0a8f522", // this first element of array should not come in result because it is not in Employee document
                        "5a6eec263bf43426d4d31780"
                    ]
                }
            ]
        }
    ]
}

I want only that Forms which is in Employees document.In above 5a62e2a8f85cf41bd0a8f522 entry should not come because that entry is not in Employees document

For this I am doing aggregation as below,

db.Employees.aggregate([{
        $match: {
            _id: ObjectId("5a852dcd0290f7eca89e9a79")
        }
    }, {
        $unwind: "$Forms"
    }, {
        $lookup: {
            from: "Clients",
            localField: "Forms.ClientId",
            foreignField: "_id",
            as: "Clients"
        }
    }, {
        $unwind: "$Clients"
    }, {
        $unwind: "$Clients.Projects"
    }, {
        $unwind: "$Clients.Projects.Forms"
    }, {
        $redact: {
            $cond: {
                if : {
                    $and: [{
                            $eq: ["$Forms.ProjectId", "$Clients.Projects._id"]
                        }, {
                            $eq: ["$Forms.FormId", "$Clients.Projects.Forms"]
                        }
                    ]
                },
            then: "$$KEEP",
            else : "$$PRUNE"
        }
    }
}, {
    $group: {
        _id: {
            _id: "$_id",
            ClientId: "$Clients._id",
            ProjectId: "$Forms.ProjectId"
        },
        FirstName: {
            $first: "$FirstName"
        },
        LastName: {
            $first: "$LastName"
        },
        Client: {
            $first: "$Clients"
        },
        Projects: {
            $first: "$Clients.Projects"
        },
        Forms: {
            $push: "$Clients.Projects.Forms"
        }
    }
}, {
    $group: {
        _id: {
            _id: "$_id._id",
            ClientId: "$Clients._id",
        },
        FirstName: {
            $first: "$FirstName"
        },
        LastName: {
            $first: "$LastName"
        },
        Client: {
            $first: "$Client"
        },
        Projects: {
            $push: {
                _id: "$Projects._id",
                Name: "$Projects.Name",
                Description: "$Projects.Description",
                Forms : "$Forms"
            }
        }
    }
}, {
    $group: {
        _id: "$_id._id",
        FirstName: {
            $first: "$FirstName"
        },
        LastName: {
            $first: "$LastName"
        },
        Clients: {
            $push: {
                _id: "$Client._id",
                CompanyName: "$Client.CompanyName",
                Email: "$Client.Email",
                PhoneNumber: "$Client.PhoneNumber",
                Projects: "$Projects"
            }       
          }
       }
    }
]).pretty()

This is working fine for me, but i want only that form which is in Employees document

Upvotes: 0

Views: 66

Answers (1)

mickl
mickl

Reputation: 49945

There is one level of $unwind /$group pair missing here. Currently your $redact stage get's as an input document like this:

{
            "_id" : ObjectId("5a852dcd0290f7eca89e9a79"),
            "FirstName" : "raj",
            "LastName" : "patel",
            "Forms" : {
                    "ClientId" : ObjectId("5a8528ed0290f7eca89e9a5f"),
                    "ProjectId" : ObjectId("5a856d850290f7eca89e9a85"),
                    "FormId" : ObjectId("5a6eec263bf43426d4d31780")
            },
            "Clients" : {
                    "_id" : ObjectId("5a8528ed0290f7eca89e9a5f"),
                    "CompanyName" : "test",
                    "Email" : "[email protected]",
                    "PhoneNumber" : "(987)654-3210",
                    "Projects" : {
                            "_id" : ObjectId("5a856d850290f7eca89e9a85"),
                            "Name" : "Test Project",
                            "Description" : "For testing purpose",
                            "Forms" : [
                                    ObjectId("5a62e2a8f85cf41bd0a8f522"),
                                    ObjectId("5a6eec263bf43426d4d31780")
                            ]
                    }
            }
    }

So you're matching FormId with an array of forms which will return true if any element of that array matches. You have to add 

{
    $unwind: "$Clients.Projects.Forms"
}

to match values with each other.

EDIT: Entire working aggregation:

db.Employees.aggregate([{
        $match: {
            _id: ObjectId("5a852dcd0290f7eca89e9a79")
        }
    }, {
        $unwind: "$Forms"
    }, {
        $lookup: {
            from: "Clients",
            localField: "Forms.ClientId",
            foreignField: "_id",
            as: "Clients"
        }
    }, {
        $unwind: "$Clients"
    }, {
        $unwind: "$Clients.Projects"
    }, {
        $unwind: "$Clients.Projects.Forms"
    }, {
        $redact: {
            $cond: {
                if : {
                    $and: [{
                            $eq: ["$Forms.ProjectId", "$Clients.Projects._id"]
                        }, {
                            $eq: ["$Forms.FormId", "$Clients.Projects.Forms"]
                        }
                    ]
                },
            then: "$$KEEP",
            else : "$$PRUNE"
        }
    }
}, {
    $group: {
        _id: {
            _id: "$_id",
            ClientId: "$Clients._id",
            ProjectId: "$Clients.Projects._id"
        },
        FirstName: {
            $first: "$FirstName"
        },
        LastName: {
            $first: "$LastName"
        },
        Client: {
            $first: "$Clients"
        },
        Projects: {
            $first: "$Clients.Projects"
        },
        Forms: {
            $push: "$Clients.Projects.Forms"
        }
    }
}, {
    $group: {
        _id: {
            _id: "$_id._id",
            ClientId: "$_id.ClientId",
        },
        FirstName: {
            $first: "$FirstName"
        },
        LastName: {
            $first: "$LastName"
        },
        Client: {
            $first: "$Client"
        },
        Projects: {
            $push: {
                _id: "$Projects._id",
                Name: "$Projects.Name",
                Description: "$Projects.Description",
                Forms : "$Forms"
            }
        }
    }
}, {
    $group: {
        _id: "$_id._id",
        FirstName: {
            $first: "$FirstName"
        },
        LastName: {
            $first: "$LastName"
        },
        Clients: {
            $push: {
                _id: "$Client._id",
                CompanyName: "$Client.CompanyName",
                Email: "$Client.Email",
                PhoneNumber: "$Client.PhoneNumber",
                Projects: "$Projects"
            }       
          }
       }
    }
]).pretty()

Upvotes: 1

Related Questions