Skip to main content
Version: 4.5

Smart Query Conditions

When you want to make complex queries, you can easily end up with a lot of boilerplate code full of curly brackets:

const res = await orm.em.find(Author, { $and: [
{ id: { $in: [1, 2, 7] }, },
{ id: { $nin: [3, 4] }, },
{ id: { $gt: 5 }, },
{ id: { $lt: 10 }, },
{ id: { $gte: 7 }, },
{ id: { $lte: 8 }, },
{ id: { $ne: 9 }, },
] });

For AND condition with single field, you can also do this:

const res = await orm.em.find(Author, {
id: {
$in: [1, 2, 7],
$nin: [3, 4],
$gt: 5,
$lt: 10,
$gte: 7,
$lte: 8,
$ne: 9,
},
});

Another way to do this by including the operator in your keys:

This approach is deprecated and will be removed in future versions.

const res = await orm.em.find(Author, { $and: [
{ 'id:in': [1, 2, 7] },
{ 'id:nin': [3, 4] },
{ 'id:gt': 5 },
{ 'id:lt': 10 },
{ 'id:gte': 7 },
{ 'id:lte': 8 },
{ 'id:ne': 9 },
] });

For comparison operators, you can also use their mathematical symbols:

const res = await orm.em.find(Author, { $and: [
{ 'id >': 5 },
{ 'id <': 10 },
{ 'id >=': 7 },
{ 'id <=': 8 },
{ 'id !=': 9 },
] });

Keys with operators like this will cause TypeScript errors as there is no way to support them on the typings side. They are still supported, but you will need to cast the condition to any to use them.

There is also shortcut for $in - simply provide array as value and it will be converted automatically:

const res = await orm.em.find(Author, { favouriteBook: [1, 2, 7] });

For primary key lookup, you can provide the array directly to em.find():

const res = await orm.em.find(Author, [1, 2, 7]);

List of supported operators

Comparison

operatornamedescription
$eqequalsMatches values that are equal to a specified value.
$gtgreaterMatches values that are greater than a specified value.
$gtegreater or equalMatches values that are greater than or equal to a specified value.
$incontainsMatches any of the values specified in an array.
$ltlowerMatches values that are less than a specified value.
$ltelower or equalMatches values that are less than or equal to a specified value.
$nenot equalMatches all values that are not equal to a specified value.
$ninnot containsMatches none of the values specified in an array.
$likelikeUses LIKE operator
$reregexpUses REGEXP operator
$ilikeilike(postgres only)
$overlap&&(postgres only)
$contains@>(postgres only)
$contained<@(postgres only)

Logical

operatordescription
$andJoins query clauses with a logical AND returns all documents that match the conditions of both clauses.
$notInverts the effect of a query expression and returns documents that do not match the query expression.
$orJoins query clauses with a logical OR returns all documents that match the conditions of either clause.