MSDotNetBuddy

2024/04/24

MongoDb Interview Question:Join multiple collections

Assume we have three collections (authors, authorInfo, and userRole) with the 
following data:

db.authors.insertMany([
    {
        "_id" : ObjectId("5d0127aaee66e13d34c7c389"),
        "address" : "32 Makram Ebeid Street",
        "isActive" : true,
        "authorId" : "121"
    }
])

db.authorInfo.insertMany([
    {
        "_id" : ObjectId("5d0f726bac65f929d0fa98b2"),
        "authorId" : "121",
        "description" : "A description"
    }
])


db.userRole.insertMany([
    {
        "_id" : ObjectId("5d012a08ee66e13d34c7c38f"),
        "userId" : "121",
        "role" : "manager"
    }
])

How can we join the authors from all three collections

Answers:

db.authors.aggregate( [
   {
     $lookup:
       {
         from: "authorInfo",
         localField: "authorId",
         foreignField: "authorId",
         as: "authorInfo"
       }
  },{
    $unwind: "$authorInfo"
  },
  {
     $lookup:
       {
         from: "userRole",
         localField: "authorId",
         foreignField: "userId",
         as: "userRole"
       }
  },
  {
    $unwind:"$userRole"
  },{
    $project:{
        _id:1,
        address:1,
        isActive:1,
        authorId:1,
        description:"$authorInfo.description",
        role:"$userRole.role"
    }
  }
])

Note:
$lookup does not work in case of sharded collection.

MongoDb Interview Question:Filter Object Array

Problem:

Assume there is a collection named users that looks like the one below. How can
you get all houses in the “Rabia” neighborhood?

db.userHouses.insertMany([
    {
        "_id" : ObjectId("5d011c94ee66e13d34c7c388"),
        "userName" : "kevin",
        "email" : "kevin@toptal.com",
        "password" : "affdsg342",
        "houses" : [
            {
                "name" : "Big Villa",
                "neighborhood" : "Zew Ine"
            },
            {
                "name" : "Small Villa",
                "neighborhood" : "Rabia"
            }
        ]
    },
    {
        "_id" : ObjectId("5d011c94ee66e13d34c7c387"),
        "userName" : "sherif",
        "email" : "sharief@toptal.com",
        "password" : "67834783ujk",
        "houses" : [
            {
                "name" : "New Mansion",
                "neighborhood" : "Nasr City"
            },
            {
                "name" : "Old Villa",
                "neighborhood" : "Rabia"
            }
        ]
    },

])

Solution:

db.userHouses.find(
    {"houses.neighborhood":"Rabia"},
    {
        "houses":{$elemMatch:{"neighborhood" : "Rabia"}},
        "_id":0
    }

)

or

db.userHouses.aggregate([
    { $match: { 'houses.neighborhood': 'Rabia' } },
    {
        $project: {
            filteredHouses: {   // This is just an alias 
                $filter: {
                    input: '$houses',
                    as: 'houseAlias',
                    cond: { $eq: ['$$houseAlias.neighborhood', 'Rabia'] }
                }
            },
            _id: 0
        }
    }

])

Search This Blog

2024/04/24

MongoDb Interview Question:Join multiple collections

MongoDb Interview Question:Filter Object Array