Ad Code

Responsive Advertisement

Ticker

6/recent/ticker-posts

MongoDB one to one join

we are learning MongoDB one to one join with a specific scenario so please read all the below queries and process:


Scenario: 

We have two collection users and address collection and wanted to generate the user's report, and users have multiple addresses in the address collection. But we have to display the only first address of each user from the address collection with help of MongoDB aggregations pipelines.

Sample data and queries :

1. Users Collections:

2. Address Collections


// use book-one-to-one-join;
db.dropDatabase();
// Create index for a products collection
db.products.createIndex({"id": 1});
// Insert 4 records into the products collection
db.users.insertMany([

  {
    "id": "a1b2c3d4",
    "name": "SOnu",
    "userid":1,
    "status":1
  },
  {
    "id": "z9y8x7w6",
    "name": "Monu",
    "userid":2,
    "status":1
  },
  {
    "id": "ff11gg22hh33",
    "name": "Ranjeet",
    "userid":3,
    "status":1
  },
  {
    "id": "pqr678st",
    "name": "Khan",
    "userid":4,
    "status":1
  },

]); 

//address Collection

db.address.insertMany([

  {
    "id": "a1b2c3d434",
    "userId":"1",
    "name": "Delhi",
  },
  {
    "id": "a1b2c3d434",
    "userId":"1",
    "name": "Mumbai",
  },
  {
    "id": "z9y8x7w6ee",
    "userId":"2",
    "name": "Mumbai"
  },
  {
    "id": "ff11gg22hh3343534",
    "userId":"3",
    "name": "Pune"
  },
  {
    "id": "ff11gg22hh3343534",
    "userId":"3",
    "name": "patna"
  },
  {
    "id": "pqr678st534",
    "userId":"4",
    "name": "Delhi",
  },

]); 

//Aggregation Pipeline

var pipeline = [
  // Match only users status 1
  {"$match": {"status": 1}},
  // Join "address" 
  {"$lookup": {
    "from": "address",
    "localField": "userId",
    "foreignField": "userId",
    "as": "user_address",
  }},
  // For this data model, will always be 1 record in right-side
  // of join, so take 1st joined array element
  {"$set": {
    "user_address": {"$first": "$user_address"},
  }},
  {
    "project":{
      "name":1,
      "address":1
    }
  }    
];

//Expectected OUtput:
[
  {
    name: 'SOnu',
    address: "Delhi",
  },
  {
    name: 'Monu',
    address: "Mumbai",
  },
  {
    name: 'Ranjeet',
    address: "Pune",
  },
  {
    name: 'Khan',
    address: "Delhi",
  }
]

We just pull the user details with only first address from address collection insted of multiple address.

Post a Comment

0 Comments