How to Join Two MongoDB Collections on the Same Field Name with the same value?-CodePudding

I have two collections in my MongoDB Database that I want to join then on the same field value when I search for a number. Here is my collections structure:

First collection's name is users:

{
user_id : "1234",
phone_number: " 9600000000000"
}

Second collection's name is users_info:

{
name : "John Smith",
phone_number: " 9600000000000",
age: "45",
address: "DC"
}

I want to create a query joining those two collections where phone_number is equal to a value. The result to be similar to the following:

{
user_id : "1234",
name :"John Smith",
phone_number: " 9600000000000",
age: "45",
address: "DC"
}

CodePudding user response：

use the $unwind stage to flatten the user_info array, and the $project stage to reshape.

db.users.aggregate([
{
$lookup: {
from: "users_info",
localField: "phone_number",
foreignField: "phone_number",
as: "user_info"
}
},
{
$unwind: "$user_info"
},
{
$project: {
user_id: 1,
name: "$user_info.name",
phone_number: "$user_info.phone_number",
age: "$user_info.age",
address: "$user_info.address"
}
}
])

CodePudding user response：

Here's one way to do it.

db.users.aggregate([
  {
    "$match": {
      "phone_number": " 9600000000000"
    }
  },
  {
    "$lookup": {
      "from": "users_info",
      "localField": "phone_number",
      "foreignField": "phone_number",
      "as": "user_info",
      "pipeline": [
        {"$unset": "_id"}
      ]
    }
  },
  {
    "$replaceWith": {
      "$mergeObjects": [
        "$$ROOT",
        {"$first": "$user_info"}
      ]
    }
  },
  {
    "$unset": ["_id", "user_info"]
  }
])

Try it on mongoplayground.net.

If your MongoDB server version is before 4.2 when "$unset" became available, you can use "$project" instead, like this.

db.users.aggregate([
  {
    "$match": {
      "phone_number": " 9600000000000"
    }
  },
  {
    "$lookup": {
      "from": "users_info",
      "localField": "phone_number",
      "foreignField": "phone_number",
      "as": "user_info",
      "pipeline": [
        {
          "$project": {
            "_id": 0
          }
        }
      ]
    }
  },
  {
    "$replaceWith": {
      "$mergeObjects": [
        "$$ROOT",
        {"$first": "$user_info"}
      ]
    }
  },
  {
    "$project": {
      "_id": 0,
      "user_info": 0
    }
  }
])

Try it on mongoplayground.net.