How to query by reference field in MongoDB?

Question

I have two Mongo schemas:

User:

{
  _id: ObjectId,
  name: String,
  country: ObjectId // Reference to schema Country
}

Country:

{
  _id: ObjectId,
  name: String
}

I want to get all users who have the country name "VietNam".

What kind of query (only to the User schema) can I use for this case?

I want it to look like this SQL query:

SELECT * 
FROM User
JOIN Country 
ON User.country = Country._id 
WHERE Country.name = 'VietNam'

Answer 1

You can use below aggregation with mongodb 3.6 and above

db.country.aggregate([
  { "$match": { "name": "VietNam" } },
  { "$lookup": {
    "from": Users.collection.name,
    "let": { "countryId": "$_id" },
    "pipeline": [
      { "$match": { "$expr": { "$eq": [ "$country", "$$countryId" ] } } },
    ],
    "as": "users",
  }},
  { "$unwind": "$users" },
  { "$replaceRoot": { "newRoot": "$users" }}
])

Answer 2

Unlike relational databases, this isn't something that Mongo is good at, and you should generally structure your schemas in a different way when you're using NoSQL. In this case, you could add a countryName field to the user collection (and perhaps an index on country) so that you can query {countryName: "Vietnam"}. (Instead of country-name, it'd likely make more sense to use the iso-2 country code)

If you do need to do a "join" you can use the $lookup operator in the aggregation pipeline -- just keep in mind that aggregations don't scale well and tend to be a little hard to write.