Filtering
Perform filtering on your data using the where methods.
Mongez Aggregate class provides various methods to filter your data.
where
The where method is used to filter documents based on a condition. It could take multiple forms:
/**
* Add where stage
*/
public where(column: string, value: any): this;
public where(column: string, operator: WhereOperator, value: any): this;
public where(column: Record<string, any>): this;
Let's have a look at each one of them:
Simple where stage
If you just want to filter documents based on a single value equality, you can do this:
const users = await aggregate.where("id", 10).get();
Just pass the column name and the value you want to filter by.
Where with operator
Alternatively, if you want to make more conditions, you can use the operator, here are the available operators:
const whereOperators = [
"=",
"!=",
"not",
">",
">=",
"<",
"<=",
"in",
"nin",
"notIn",
"all",
"exists",
"type",
"mod",
"regex",
"geoIntersects",
"geoWithin",
"near",
"between",
"notBetween",
"nearSphere",
"elemMatch",
"size",
"like",
"notLike",
"startsWith",
"endsWith",
];
It could be any of the previous operators, let's take some examples:
const users = await aggregate.where("id", ">", 10).get();
const users = await aggregate.where("id", "in", [1, 2, 3]).get();
const users = await aggregate.where("id", "between", [1, 10]).get();
Where with object
Another way to filter documents is to pass an object to the where method, this object should have the column name as a key, and the value should be the value you want to filter by.
Method signature:
public where(column: Record<string, any>): this;
const users = await aggregate.where({ id: 10 }).get();
You can also use the built-in Mongodb operators:
const users = await aggregate.where({ id: { $gt: 10 } }).get();
You can use $agg utility to easily use built-in operators directly.
Where null
If you want to filter documents based on a null value, you can use the whereNull method:
Method signature:
public whereNull(column: string): this;
const users = await aggregate.whereNull("id").get();
This will return all users that have id = null.
Where not null
If you want to filter documents based on a not null value, you can use the whereNotNull method:
Method signature:
public whereNotNull(column: string): this;
const users = await aggregate.whereNotNull("id").get();
Where in
If you want to filter documents based on a list of values, you can use the whereIn method:
Method signature:
public whereIn(column: string, value: any[]): this;
const users = await aggregate.whereIn("id", [1, 2, 3]).get();
If the second argument is a string, then it will be treated as a column name.
Where not in
If you want to filter documents based on a list of values, you can use the whereNotIn method:
Method signature:
public whereNotIn(column: string, value: any[]): this;
const users = await aggregate.whereNotIn("id", [1, 2, 3]).get();
If the second argument is a string, then it will be treated as a column name.
Where between
If you want to filter documents based on a range of values, you can use the whereBetween method:
Method signature:
public whereBetween(column: string, value: [any, any]): this;
const users = await aggregate.whereBetween("id", [1, 10]).get();
The second argument is an array of two values, the first value is the min value, and the second value is the max value and they are both included in the range.
Where not between
If you want to filter documents based on a range of values, you can use the whereNotBetween method:
Method signature:
public whereNotBetween(column: string, value: [any, any]): this;
const users = await aggregate.whereNotBetween("id", [1, 10]).get();
The second argument is an array of two values, the first value is the
minvalue, and the second value is themaxvalue and they are both included in the range.
Where like
If you want to filter documents based on a string pattern, you can use the whereLike method:
Method signature:
public whereLike(column: string, value: string): this;
const users = await aggregate.whereLike("name", "John").get();
This will return all users that have name column that contains John.
If the second argument is a string, the value will be case-insensitive.
You can also pass a regex object:
const users = await aggregate.whereLike("name", /John/i).get();
Where not like
If you want to filter documents based on a string pattern, you can use the whereNotLike method:
Method signature:
public whereNotLike(column: string, value: string): this;
const users = await aggregate.whereNotLike("name", "John").get();
This will return all users that have name column that does not contain John.
If the second argument is a string, the value will be case-insensitive.
Where starts with
If you want to filter documents based on a string pattern, you can use the whereStartsWith method:
Method signature:
public whereStartsWith(column: string, value: string): this;
const users = await aggregate.whereStartsWith("name", "John").get();
This will return all users that have name column that starts with John.
The value will be case-sensitive.
Where not starts with
Search for documents that a column does not start with a given value.
Method signature:
public whereNotStartsWith(column: string, value: string): this;
const users = await aggregate.whereNotStartsWith("name", "John").get();
Where ends with
If you want to filter documents based on a string pattern, you can use the whereEndsWith method:
Method signature:
public whereEndsWith(column: string, value: string): this;
const users = await aggregate.whereEndsWith("name", "John").get();
This will return all users that have name column that ends with John.
The value will be case-sensitive.
Where not ends with
Search for documents that a column does not end with a given value.
Method signature:
public whereNotEndsWith(column: string, value: string): this;
const users = await aggregate.whereNotEndsWith("name", "John").get();
Where date between
If you want to filter documents based on a date range, you can use the whereDateBetween method:
Method signature:
public whereDateBetween(column: string, value: [Date, Date]): this;
const users = await aggregate
.whereDateBetween("createdAt", [
new Date("2020-01-01"),
new Date("2020-12-31"),
])
.get();
Where date not between
Filter documents that are not between a date range.
Method signature:
public whereDateNotBetween(column: string, value: [Date, Date]): this;
const users = await aggregate
.whereDateNotBetween("createdAt", [
new Date("2020-01-01"),
new Date("2020-12-31"),
])
.get();
Where exists
This method will filter documents based on the existence of a column.
Method signature:
public whereExists(column: string): this;
const users = await aggregate.whereExists("name").get();
Where not exists
This method will filter documents based on the existence of a column.
Method signature:
public whereNotExists(column: string): this;
const users = await aggregate.whereNotExists("name").get();
Where near
This method will filter documents based on the distance between two points.
Or Where
If we need to filter if any of the conditions is true, we can use orWhere method.
const users = await aggregate
.orWhere({
id: 10,
name: "John",
})
.get();
This will return all users that have either id = 10 or name = John.
You can also use the $agg utility to use built-in operators:
Before:
const users = await aggregate
.orWhere({
id: { $gt: 10 },
name: { $regex: /john/i },
})
.get();
After:
import { $agg } from "@mongez/monpulse";
const users = await aggregate
.orWhere({
id: $agg.gt(10),
name: $agg.like("john"),
})
.get();
Where Columns
If we need to compare values between two columns or more, we can use whereColumns method.
const users = await aggregate
.whereColumns("maxAddresses", ">", "addressesCount")
.get();
This will return all users that have maxAddresses column greater than addressesCount column.
The second argument is the operator, it could be any of the where operators and it is always required.