Trouble with document join query in MongoDB

Question

I am trying to join the following two documents based on the related fields, namely users._id and addressschemas.userId.

example users document;

{
    "_id" : ObjectId("58f74901b3ec8e2d0bc898d5"),
    "fullName" : "public user",
    "firstName" : "public",
    "lastName" : "user",
    "email" : "user@gmail.com",
    "password" : "$2a$10$thYCJS62ejUxxzIlpIfiXeRxswQPzU4sVhc4PeoGxAXN3/IBxnxeO",
    "mobile" : "9876543210",
    "countryCode" : "+91",
    "accountEnabled" : true,
    "__v" : 0
}

example addressschemas document;

{
    "_id" : ObjectId("58f9b18880a35c281078f42b"),
    "userId" :  ObjectId("58f74901b3ec8e2d0bc898d5"),
    "deliveryName" : "test",
    "deliveryAddress" : "new address",
    "deliveryState" : "Haryana",
    "deliveryCity" : "Gurgaon",
    "deliveryZipCode" : "122001",
    "deliveryCountryCode" : "+91",
    "deliveryMobile" : "9876543211",
    "deliveryEmail" : "subadmin2@email.com",
    "__v" : 0
}

The desired result from before-mentioned join should be the following;

{

    "_id" : ObjectId("58f74901b3ec8e2d0bc898d5"),
    "fullName" : "public user",
    "firstName" : "public",
    "lastName" : "user",
    "email" : "user@gmail.com",
    "password" : "$2a$10$thYCJS62ejUxxzIlpIfiXeRxswQPzU4sVhc4PeoGxAXN3/IBxnxeO",
    "mobile" : "9876543210",
    "countryCode" : "+91",
    "accountEnabled" : true,

    "userId" :ObjectId("58f74901b3ec8e2d0bc898d5"),
    "deliveryName" : "test",
    "deliveryAddress" : "new address",
    "deliveryState" : "Haryana",
    "deliveryCity" : "Gurgaon",
    "deliveryZipCode" : "122001",
    "deliveryCountryCode" : "+91",
    "deliveryMobile" : "9876543211",
    "deliveryEmail" : "subadmin2@email.com"
}

And the query that I am using to achieve this;

db.getCollection('users').aggregate([
  {
    $match: {
      _id: ObjectId("58f74901b3ec8e2d0bc898d5")
    }
  },
  {
    $lookup: {
      from: "addressschemas",
      localField: "_id",
      foreignField: "usersId",
      as: "results"
    }
  },
  {
    $project: {
      addressschemas: {
        $filter: {
          input: "$_id",
          cond: {
            $eq: ['$$addressschemas.userId', '$users._id']
          }
        }
      }
    }
  }
])

But the result of the above query is empty, can anyone tell me the problem with it?

Answer 1

You can get an approximate result with the following query;

db.getCollection('users').aggregate([
  {
    $match: {
      _id: ObjectId("58f74901b3ec8e2d0bc898d5")
    }
  },
  {
    $lookup: {
      from: "addressschemas",
      localField: "_id",
      foreignField: "userId",
      as: "address"
    }
  },
  {
    $unwind : "$address" 
  },
  {
    $project: {
      __v: 0,
      "address.__v": 0,
      "address._id": 0,
      "address.userId": 0
    }
  }
])

Which will result in the following document, using your test data;

{
  "_id": ObjectId("58f74901b3ec8e2d0bc898d5")
  "fullName": "public user",
  "firstName": "public",
  "lastName": "user",
  "email": "user@gmail.com",
  "password": "$2a$10$thYCJS62ejUxxzIlpIfiXeRxswQPzU4sVhc4PeoGxAXN3/IBxnxeO",
  "mobile": "9876543210",
  "countryCode": "+91",
  "accountEnabled": true,
  "address": {
    "deliveryName": "test",
    "deliveryAddress": "new address",
    "deliveryState": "Haryana",
    "deliveryCity": "Gurgaon",
    "deliveryZipCode": "122001",
    "deliveryCountryCode": "+91",
    "deliveryMobile": "9876543211",
    "deliveryEmail": "subadmin2@email.com"
  }
}

Since $lookup creates a new field together with the original fields of users document, you'd have the values of addressschemas in an array under this new address field, therefore I am using an $undwind operation to get rid of this array. There is no easy way to move them up one level, to the root, as it is in your desired result and I think it is better to represent the address data like this.

Obviously I am assuming the user document contains a singular address only, if that is not the case, the $unwind operation will result a multiple of the above objects with each containing a different addess field, but from your desired result, I deduced that you have one-to-one relationship between these two documents.

Answer 2

MongoDB defines relationship in two ways

(1) References (2) Embedded documents

References are preferable in application where many to many relationship exists among entities.

Embedded documents are preferable in application where one to many relationship exists among entities.

In above mentioned scenario it seems that one to many relationship exists between user and addressschemas.

Hence addresses of respective user can be defined as an embedded document within user document thereby facilitating retrieval of user document in single trip to database server.

E.g

{
    "_id": ObjectId("58f74901b3ec8e2d0bc898d5"),
    "fullName": "public user",
    "firstName": "public",
    "lastName": "user",
    "email": "user@gmail.com",
    "password": "$2a$10$thYCJS62ejUxxzIlpIfiXeRxswQPzU4sVhc4PeoGxAXN3/IBxnxeO",
    "mobile": "9876543210",
    "countryCode": "+91",
    "accountEnabled": true,
    "__v": 0,
    "addressschemas": [
        {
            "deliveryName": "test",
            "deliveryAddress": "new address",
            "deliveryState": "Haryana",
            "deliveryCity": "Gurgaon",
            "deliveryZipCode": "122001",
            "deliveryCountryCode": "+91",
            "deliveryMobile": "9876543211",
            "deliveryEmail": "subadmin2@email.com"
        }
    ]
}