How to build dynamic mongoose queries

Disclaimer: This post assumes you have knowledge of Node js, and mongoose.

The challenge:

Say you need to build an internal product where a user needs to be able to search for data by different criteria e.g. a grading app for teachers where students can be searched based on class, age, continuous assessment results, school fees(partial payment or full payment).

On the back end:

-We would build a Student model
-A controller function for querying the database
-A router to listen for get requests on a specified path.

Our Student model:
We would need to define a model that encapsulates all the data we want each student attending our 'school of code' to have.

const mongoose = require('mongoose');

const student = mongoose.Schema({
  name: String,
  age: Number,
  class: String,
  school_fees: { 
               type: String, 
               enum: ['Not Paid', 'Full Payment', 'Partial Payment'], 
               default: 'Not Paid' 
              },
  test_1: String,
  test_2: String
});

module.exports = mongoose.model('Student', student);

Now imagine we have a select drop-down tag named 'Search By' on the front end which gives a list of criteria to search students by(i.e. our 'search_field') and clicking on either of the options displays a search bar by which you can type in your search value (let's call it 'search_value').

We would have to get the 'search_field' and 'search_value' on the frontend and use it to query the database as such({search_field: search_value}). Now we don't know beforehand what the client will pick.

For example: It could be Search by ('age'):(10). Age would be our search_field and 10 would be our search_value.

Our Controller function:

const Student = require('../models/student.js');

exports.getStudentBy = async (req, res) => {
  try {
    // get search_criteria from query parameter
    // build a query object with it
    // send data to the frontend

    const { search_field, search_value } = req.query;

    const queryObj = {};

    if (search_field !== '' && search_value !== '') {
      queryObj[search_field] = search_value;
    }

    console.log('::queryObj:::', queryObj);

    const student = await Student.find(queryObj);

    if (!student) {
      return res.status(404).json({
        status: 'failure',
        message: `Student with the given ${search_field}:${search_value} not found`
      });
    }

    res.status(200).json({
      status: 'success',
      data: student
    });
  } catch (error) {
    res.status(500).json({
      status: 'failure',
      error: error.message
    });
  }
};

What's happening here?

First we get the search field and search value from the request object by using es6 object destructuring.

 const { search_field, search_value } = req.query;

Next, we need to build a query object that would contain our query parameters in key-value pairs.

A gentle aside, assuming you need to search for students by class, you could do something like this:

const students = await Student.find({class: 'grade 2'});

The above would be easy if we knew what the client was going to pick ahead of time such as an input field and a 'button' beside it that says 'search by class'. But now we have a variety of inputs dependent on the client's choice.

So...

We build an object that dynamically sets the search field as the object's key, and sets the search value as the object's value.

Remember:

const object = {
                 key: value
               }

So we check that the search field and search value aren't empty strings. Then we set the search field to the query Object's key and the search value to the corresponding value.

 const queryObj = {};

    if (search_field !== '' && search_value !== '') {
      queryObj[search_field] = search_value;
    }

In case this looks confusing, google the difference between using the dot notation for adding properties to an object and using bracket notation. Don't use dot notation as this will not give you what you want

Next, we place this query object in our find method, and 'await' the result

     const student = await Student.find(queryObj);

Then we handle the asynchronous responses appropriately by placing the code in a try-catch block.

Our Router:

  const express = require('express');
  const { getStudentBy } = require('../controllers/student.js');

  const router = express.Router();

  router.get(`${API}/search`, getStudentBy);

  module.exports = router;

Here's where it all comes together, the router. We listen for get requests on the specified api path.

This tutorial is by no means exhaustive or well written. In fact it was a bit rushed, but I just had to post this because I just figured out how to do this and I'm stoked to share it!

Please leave your comments, contributions, corrections and likes!

Comments

[Evan Jameson]

I can’t even begin to tell you how timely this post is.

I was curious, when it comes to defining the schema for your model do you need to be completely explicit?

I have a large dataset where some of my keys can be different values and some key:value pairs vary in size. (I’m scraping a website for my data that varies heavily between pages) I'm using "Schema.Types.Mixed" to account for this:

gist.github.com/EvanJameson/b44815...

Could this lead to major issues down the road? Do you think I need to restructure how I structure the data I scrape?

[Audiophile]

Hi Evan thanks for reaching out. Now I've personally never had to use Mixed schema types in my work, but reading the mongoose docs on Schema.Types.Mixed explicitly states that while you may be able change the value of the field to whatever you like, Mongoose will lose the ability to auto detect and save those changes, meaning you can't just do a 'model.save()' after making changes to the mixed field on your schema, you need to call model.markModified('field_path'); before your model.save(). Depending on the number of controller functions you have or may need in future, the possibility of forgetting to add that line for every modification to your mixed field type is pretty high. I would suggest that you make the schema for your model as explicit as possible. It'll save you alot of headache down the line when you need to perform complex queries, because you wouldn't need to keep checking your model for changes in its value type. I hope this helps.

[Evan Jameson]

Hi again! I followed your guide here and had a question:

When it comes to "${API}/search" where is API defined? Is the specified API path based on the request? Learning a lot from scratch here so this may be a rather rudimentary question.

Prior to following your guide I was getting the user specified value via req.query.params and using the path format "/:athleteName" where the request path would look like

-url here-/athletes/-name here-

What should the request look like when following your guide?

You can see my route here:

gist.github.com/EvanJameson/aebe11...

You can see my controller here:

gist.github.com/EvanJameson/2592d1...

[Audiophile]

Hi Evan! I apologize for not being clear on the API path. In JavaScript it is advisable to always store values that are least likely to change in variables for two reasons: 1.) ease of use in multiple places 2.) It reduces typos. So usually if I have an endpoint like:

 'http://localhost:5000/api/students/search'

My base url will be: localhost:5000/api/students while my search endpoint would be: /search. Now if I have 10 different endpoints doing different things, having to type my base url multiple times would slow me down. So instead I do something like:

 const API = 'http://localhost:5000/api/students'

And then for each route path, I use es6 string literals to concatenate the base url and the search endpoint like so:

 router.get(`${API}/search`)

So even if I have 20 routes to build, all I have to do is use string literals to concatenate the base url and route path:

 router.get(`${API}/route1`)

 router.get(`${API}/route2`)
 .
 .
 .
router.get(`${API}/route20`

Now for the '/:athleteName' convention, there's nothing wrong in doing it that way, it's part of RESTful convention to show other developers that it is a query parameter. Although when testing the endpoint with Postman there is a slight difference in the way you would do it.

You see adding '/:athleteName' means that when sending your request(in Postman) you would have to type something like:

 'http://localhost:5000/api/athletes/Evan'

in your get request. You may not be able to add it to the query params field in Postman to get something that looks more like:

 'http://localhost:5000/api/athletes?athleteName=Evan'

You can see that the two requests are different even though you would eventually get the same result either way.

Here's the thing, your 'req' and 'res' are objects that have properties attached to each of them. The request object(req) has a 'query' property that is also an object. For example:

 const req = {
            query:{ athleteName }
             }

So if I wanted to get the athleteName(in my controller function) from a get request without having to use the '/:athleteName' convention, I would just get it this way:

  const athleteName = req.query.athleteName;

   or with destructuring

const { athleteName } = req.query;

Doing it this way means I can write routes like 'localhost:5000/api/athlete/athlete...' without having to use the colons. Subtle difference but doesn't stop your code from working, only changes how you would test with Postman(at least in my case it did). I hope this helps.

[Phoebe M]

I love how this reply was its own mini tutorial! lol so thorough

[Audiophile]

Thanks Phoebe!

[Sebastianus Sembara]

hey you would help using with ts, its works but i get the following error
Type 'ParsedQs' cannot be used as an index type. in this code queryObj[search_field] = search_value;