CODEWITHSUNDEEP
javascriptnode.jsmongodbmongooseaggregation-framework
Chicky
Chicky

Reputation: 1257

How to query by reference field in MongoDB?

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'

Upvotes: 4

Views: 16720

Answers (4)

Zulhant Arif
Zulhant Arif

Reputation: 31

if you want to associate 2 or more collections you must define the reference object of the collection

my solution please check this https://mongoosejs.com/docs/populate.html

Upvotes: 1

Ashh
Ashh

Reputation: 46461

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" }}
])

Upvotes: 6

Colin Daniel
Colin Daniel

Reputation: 178

Heres the code I generally use:

Models/users.js


const mongoose = require("mongoose");
const Countries = require("./countries");

const UsersSchema = new mongoose.Schema({
    name: Sting,
    country: Countries.schema
});

module.exports = mongoose.model("Users", UsersSchema);

controllers/users.js

const express = require("express");
const router = express.Router();
const Users = require("../models/users");

router.post("/register", async(req, res) => {
    try{
      const Me = await Users.create({name: req.body.name, country: req.body.country})
      /* If you make the request correctly on the frontend req.body.name is a 
         string and req.body.country is an object with one property, it's name */
      res.status(200).json({
          data: Me
      });
    }
    catch(err){
      res.status(400).json({message:err.message})

Since the subschema is an object you reference it as Me.country.name. Hope this helps!

Upvotes: -1

klhr
klhr

Reputation: 3390

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.

Upvotes: 2

Related Questions