Filter Records From Tables in Sequelize

33 min read·Jan 1, 2025

In Sequelize, the Op object provides a list of built-in operators, such as Op.eq for =, Op.and for AND, Op.between for BETWEEN ... AND ..., etc:

const { Op } = require('sequelize');

To filter records based on conditions, you can use the where property combined with a built-in operator of the Op object:

{
  where: {
    <attribute>: {
      [Op.<operator>]: <value>
    }
  }
}

Where:

attribute is the name of the column you want to perform a comparison of (e.g., first_name).
operator is a built-in operator (e.g., eq).
value is the value you want to compare the specified column to (e.g., 3).

Retrieve records in a list

To filter records based on the value or the absence of value of an column in a list of values, you can use the Op.in and Op.notIn operators:

{
  where: {
    <attribute>: {
      [Op.in | Op.notIn]: [<value>, ...]
    }
  }
}

Where:

Op.in is the equivalent of the IN operator.
Op.notIn is the equivalent of the NOT IN operator.

Example

For example, this query will retrieve the first_name, last_name, and department columns, where the value of the department column is in the list containing the 'Sales' and 'Marketing' strings:

await Employee.findAll({
  attributes: ['first_name', 'last_name', 'department'],
  where: {
    department: {
      [Op.in]: ['Sales', 'Marketing']
    }
  }
});

Which is equivalent to this SQL statement:

mysql> SELECT first_name, last_name, department FROM employees WHERE department IN ('Sales', 'Marketing');
+------------+-----------+------------+
| first_name | last_name | department |
+------------+-----------+------------+
| John       | Doe       | Sales      |
| Bob        | Brown     | Sales      |
| Diana      | Miller    | Marketing  |
| Frank      | Moore     | Marketing  |
| Hank       | Anderson  | Sales      |
+------------+-----------+------------+
5 rows in set (0.00 sec)

Retrieve records in a range

To filter records based on the value or the absence of value of an column between a range of values, you can use the Op.between and Op.notBetween operators:

{
  where: {
    <attribute>: {
      [Op.between | Op.notBetween]: [<start>, <end>]
    }
  }
}

Where:

Op.between is the equivalent of the BETWEEN SQL operator.
Op.notBetween is the equivalent of the NOT BETWEEN SQL operator.

Example

For example, this query will retrieve the first_name, last_name, department, and hire_date columns, where the value of the hire_date column is between the '2019-01-01' and '2020-12-31' dates:

await Employee.findAll({
  attributes: ['first_name', 'last_name', 'department', 'hire_date'],
  where: {
    hire_date: {
      [Op.between]: ['2019-01-01', '2020-12-31']
    }
  }
});

Which is equivalent to this SQL statement:

mysql> SELECT first_name, last_name, department, hire_date FROM employees WHERE hire_date BETWEEN '2019-01-01' AND '2020-12-31';
+------------+-----------+-------------+------------+
| first_name | last_name | department  | hire_date  |
+------------+-----------+-------------+------------+
| Jane       | Smith     | Engineering | 2020-03-22 |
| Alice      | Johnson   | HR          | 2019-07-30 |
| Frank      | Moore     | Marketing   | 2020-09-30 |
| Hank       | Anderson  | Sales       | 2019-12-12 |
+------------+-----------+-------------+------------+
4 rows in set (0.00 sec)

Filter records with conditions

The `=` and `!=` operators

To filter records based on a single column that matches or not a value, you can use the Op.eq and Op.ne operators:

{
  where: {
    <attribute>: {
      [Op.eq | Op.ne]: <value>
    },
  }
}

Where:

Op.eq is equivalent to the = SQL comparison operator.
Op.ne is equivalent to the != or <> SQL comparison operator.

Note: To check whether an column is lower or greater than a value, you can use these operators:

Op.gt is equivalent to the > SQL comparison operator.

Op.gte is equivalent to the >= SQL comparison operator.

Op.lt is equivalent to the < SQL comparison operator.

Op.lte is equivalent to the <= SQL comparison operator.

Example

For example, this query will retrieve the first_name, last_name, and department columns, where the id attribute equals 1:

await Employee.findOne({
  attributes: ['first_name', 'last_name', 'department'],
  where: {
    id: {
      [Op.eq]: 1
    }
  }
});

Which is equivalent to this SQL statement:

mysql> SELECT first_name, last_name, department FROM employees WHERE id = 1;
+------------+-----------+------------+
| first_name | last_name | department |
+------------+-----------+------------+
| John       | Doe       | Sales      |
+------------+-----------+------------+
1 rows in set (0.00 sec)

The AND operator

To combine two or more conditions, you can use the Op.and operator that requires all conditions to be true to include the record in the result set:

{
  where: {
    [Op.and]: [{ <attribute>: <value> }, ...]
  }
}

Note that to compare the same column against multiple values, you can use the following syntax instead:

{
  where: {
    <attribute>: {
      [Op.and]: [<value>, ...]
    }
  }
}

Example

For example, this query will retrieve the last_name and hire_date columns, where the value of the department column is equal to the 'Engineering' string, and the value of the hire_date column is greater than or equal to the '2021-01-01' date:

await Employee.findAll({
  attributes: ['last_name', 'hire_date'],
  where: {
    [Op.and]: [
      {
        department: {
          [Op.eq]: 'Engineering'
        }
      },
      {
        hire_date: {
          [Op.gte]: '2021-01-01'
        }
      }
    ]
  }
});

Which is equivalent to this SQL statement:

mysql> SELECT last_name, hire_date FROM employees WHERE department = 'Engineering' AND hire_date >= '2021-01-01';
+-----------+------------+
| last_name | hire_date  |
+-----------+------------+
| Davis     | 2021-06-18 |
| Taylor    | 2021-04-02 |
+-----------+------------+
2 rows in set (0.00 sec)

The OR operator

To combine two or more conditions, you can use the Op.or operator that requires at least one condition to be true to include the record in the result set:

{
  where: {
    [Op.or]: [{ <attribute>: <value> }, ...]
  }
}

Note that to compare the same column against multiple values, you can use the following syntax instead:

{
  where: {
    <attribute>: {
      [Op.or]: [<value>, ...]
    }
  }
}

Example

For example, this query will retrieve the last_name, department, and salary columns, where the value of the department column is equal to the 'Sales' or 'HR' string:

await Employee.findAll({
  attributes: ['last_name', 'department', 'salary'],
  where: {
    department: {
      [Op.or]: ['Sales', 'HR']
    }
  }
});

Which is equivalent to this SQL statement:

mysql> SELECT last_name, department, salary FROM employees WHERE department = 'Sales' OR department = 'HR';
+-----------+------------+--------+
| last_name | department | salary |
+-----------+------------+--------+
| Doe       | Sales      | 50000  |
| Johnson   | HR         | 45000  |
| Brown     | Sales      | 52000  |
| Wilson    | HR         | 48000  |
| Anderson  | Sales      | 55000  |
+-----------+------------+--------+
5 rows in set (0.00 sec)

Combine operators

In Sequelize, the Op.and and Op.or operators can be nested to control the order in which they are evaluated, just like with parenthesis in SQL.

Keep in mind that the AND operator has higher precedence than the OR operator, which means that when used together, the AND operator is evaluated first before the OR operator.

For example, based on the operators precedence, this WHERE clause:

WHERE condition_A AND condition_B OR condition_C

Which is equivalent to this clause:

WHERE (condition_A AND condition_B) OR condition_C

Translates to this structure:

{  
  where: {
    [Op.or]: [
      {
        [Op.and]: [
          { <condition_A> },
          { <condition_B> }
        ]
      },
      { <condition_C> }
    ]
  }
}

And this WHERE clause:

WHERE condition_A AND (condition_B OR condition_C)

Translates to this structure:

{
  where: {
    [Op.and]: [
      {
        <condition_A>
      },
      {
        [Op.or]: [
          { <condition_B> },
          { <condition_C> }
        ]
      }
    ]
  }
}

Example

For example, this query will retrieve all the columns, where the value of the department column equals to 'Sales', or the value of the department column equals to 'Marketing' and the value of the hire_date column is greater or equal to the '2021-01-01' date:

await Employee.findAll({
  where: {
    [Op.or]: [
      {
        department: {
          [Op.eq]: 'Sales'
        }
      },
      {
        department: {
          [Op.eq]: 'Marketing'
        },
        hire_date: {
          [Op.gte]: '2021-01-01'
        }
      }
    ]
  }
});

Which is equivalent to this SQL statement:

mysql> SELECT * FROM employees WHERE department = 'Sales' OR department = 'Marketing' AND hire_date >= '2021-01-01';
+----+------------+-----------+------------+------------+--------+
| id | first_name | last_name | department | hire_date  | salary |
+----+------------+-----------+------------+------------+--------+
|  1 | John       | Doe       | Sales      | 2022-01-15 | 50000  |
|  4 | Bob        | Brown     | Sales      | 2018-11-10 | 52000  |
| 10 | Hank       | Anderson  | Sales      | 2019-12-12 | 55000  |
+----+------------+-----------+------------+------------+--------+
3 rows in set (0.00 sec)

Example

For example, this query will retrieve all the columns, where the value of the department column either equals to 'Sales' or 'Marketing', and the value of the hire_date column is greater or equal to '2021-01-01':

await Employee.findAll({
  where: {
    [Op.and]: [ 
      {
        department: {
          [Op.or]: ['Sales', 'Marketing']
        }
      },
      {
        hire_date: {
          [Op.gte]: '2021-01-01'
        }
      }
    ]
  }
});

Which is equivalent to this SQL statement:

mysql> SELECT * FROM employees WHERE (department = 'Sales' OR department = 'Marketing') AND hire_date >= '2021-01-01';
+----+------------+-----------+------------+------------+--------+
| id | first_name | last_name | department | hire_date  | salary |
+----+------------+-----------+------------+------------+--------+
|  1 | John       | Doe       | Sales      | 2022-01-15 | 50000  |
+----+------------+-----------+------------+------------+--------+
1 row in set (0.00 sec)

Filter records based on patterns

Match basic patterns

To filter records based on basic patterns, you can use one of the Op.like, Op.notLike, Op.startsWith, Op.endsWith, Op.substring operators:

{
  where: {
    <attribute>: {
      [Op.like | Op.notLike | Op.startsWith | Op.endsWith | Op.substring]: 'pattern'
    }
  }
}

Where:

Op.like is equivalent to the LIKE SQL operator.
Op.notLike is equivalent to the NOT LIKE SQL operator.
Op.startsWith is equivalent to the LIKE 'pattern%' SQL expression.
Op.endsWith is equivalent to the LIKE '%pattern' SQL expression.
Op.substring is equivalent to the LIKE '%pattern%' SQL expression.

Example

For example, this query will retrieve the first_name and last_name columns, where the value of the last_name column starts with the letter 'M' followed by zero or more characters:

await Employee.findAll({
  attributes: ['first_name', 'last_name'],
  where: {
    last_name: {
      [Op.startsWith]: 'M'
    }
  }
});

Which is equivalent to this SQL statement:

mysql> SELECT first_name, last_name FROM employees WHERE last_name LIKE 'M%';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Diana      | Miller    |
| Frank      | Moore     |
+------------+-----------+
2 rows in set (0.00 sec)

Match regular expressions

To filter records based on regular expressions, you can use one of the Op.regexp and Op.notRegexp operators:

{
  where: {
    <attribute>: {
      [Op.regexp]: 'pattern'
    }
  }
}

Where:

Op.regexp is equivalent to the REGEXP 'pattern' SQL expression.
Op.notRegexp is equivalent to the REGEXP '^pattern' SQL expression.

Example

For example, this query will retrieve the id, first_name, and last_name columns, where the value of the last_name column contains exactly 5 characters:

await Employee.findAll({
  attributes: ['id', 'first_name', 'last_name'],
  where: {
    last_name: {
      [Op.regexp]: '^.{5}$'
    }
  }
});

Which is equivalent to this SQL statement:

mysql> SELECT id, first_name, last_name FROM employees WHERE last_name REGEXP '^.{5}$';
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  2 | Jane       | Smith     |
|  4 | Bob        | Brown     |
|  5 | Charlie    | Davis     |
|  8 | Frank      | Moore     |
+----+------------+-----------+
4 rows in set (0.03 sec)

Summary

Here's a summary of what you've learned in this lesson:

The Op.eq operator is equivalent to the = SQL operator.
The Op.ne operator is equivalent to the != or <> SQL operator.
The Op.gt operator is equivalent to the > SQL operator.
The Op.gte operator is equivalent to the >= SQL operator.
The Op.lt operator is equivalent to the < SQL operator.
The Op.lte operator is equivalent to the <= SQL operator.
The Op.and operator is equivalent to the AND SQL operator.
The Op.or operator is equivalent to the OR SQL operator.
The Op.in is equivalent to the IN operator.
The Op.between is equivalent to the BETWEEN operator.
The Op.like is equivalent to the LIKE SQL operator.
The Op.regexp is equivalent to the REGEXP 'pattern' SQL expression.

Enjoying the courses?

I've made these courses completely free so anyone can learn from them. If they've helped you and you'd like to actively support the work behind BackendBrewery, you can leave a tip:

Support BackendBrewery

Next lesson

Filter Records From Tables in Sequelize

Retrieve records in a list

Example

Retrieve records in a range

Example

Filter records with conditions

The = and != operators

Example

The AND operator

Example

The OR operator

Example

Combine operators

Example

Example

Filter records based on patterns

Match basic patterns

Example

Match regular expressions

Example

Summary

Enjoying the courses?

The `=` and `!=` operators