如何在MongoDB与Node.js中按指定ID执行集合关联查询

Hey there! Since you're new to MongoDB, let's break down exactly how to fetch all the job names linked to user_id: "101" using MongoDB's aggregation pipeline—this is the go-to method for handling relational-like joins between collections.

First, let's clarify the collection names #

Let’s assume your first collection (with user-job mappings) is called user_jobs, and the second collection (with job details) is called jobs. Replace these with your actual collection names if they’re different.

The Aggregation Query #

Here’s the full query that will get you the result you want:

db.user_jobs.aggregate([
  // Step 1: Filter only records where user_id is "101"
  {
    $match: { user_id: "101" }
  },
  // Step 2: Join with the jobs collection using job_id as the matching field
  {
    $lookup: {
      from: "jobs",
      localField: "job_id",
      foreignField: "job_id",
      as: "job_details"
    }
  },
  // Step 3: Unwind the job_details array (each user-job record links to one job)
  {
    $unwind: "$job_details"
  },
  // Step 4: Extract just the job name from the joined data
  {
    $project: {
      _id: 0,
      job_name: "$job_details.name"
    }
  },
  // Step 5: Collect all unique job names into a single set
  {
    $group: {
      _id: null,
      job_names: { $addToSet: "$job_name" }
    }
  },
  // Step 6: Clean up the output to only show the job names array
  {
    $project: {
      _id: 0,
      job_names: 1
    }
  }
])

What Each Step Does #

• $match: Narrows down our data to only the records we care about (user 101's job mappings) to make the rest of the pipeline more efficient.
• $lookup: This is MongoDB's equivalent of a LEFT JOIN. It pulls in all matching documents from the jobs collection where job_id matches, storing them in the job_details array.
• $unwind: Since each user_jobs document links to exactly one job, we flatten the job_details array to get a single object per entry.
• $project: We strip out unnecessary fields and rename the job's name to something clear.
• $group: We gather all the job names into a single set (which automatically handles duplicates if there are any).
• Final $project: We remove the default _id field from the output so we just get the list of job names.

Expected Output #

Running this query will return a result like:

{ "job_names" : ["Software engineer", "Project manager", "FullStack Developer"] }

If You're Using an ODM (Like Mongoose) #

If you’re working with Mongoose in Node.js, the approach is almost identical—you just call the aggregate method on your model:

const UserJob = require('./models/userJob'); // Your user-job model
const Job = require('./models/job'); // Your job model

UserJob.aggregate([
  { $match: { user_id: "101" } },
  { $lookup: { from: 'jobs', localField: 'job_id', foreignField: 'job_id', as: 'job' } },
  { $unwind: '$job' },
  { $group: { _id: null, names: { $addToSet: '$job.name' } } },
  { $project: { _id: 0, names: 1 } }
])
.then(result => {
  console.log(result[0].names); // Logs the array of job names
})
.catch(err => {
  console.error('Error fetching job names:', err);
});

内容的提问来源于stack exchange，提问作者Sasi Rekha