Queries

Querying data in Moralis Server.

We've already seen how a Moralis.Query with get can retrieve a single Moralis.Object from Moralis. There are many other ways to retrieve data with Moralis.Query - you can retrieve many objects at once, put conditions on the objects you wish to retrieve, and more.

Basic Queries

In many cases, get isn't powerful enough to specify which objects you want to retrieve. Moralis.Query offers different ways to retrieve a list of objects rather than just a single object.

The general pattern is to create a Moralis.Query, put conditions on it, and then retrieve an Array of matching Moralis.Objects using find. For example, to retrieve the scores that have a particular playerName, use the equalTo method to constrain the value for a key.

const GameScore = Moralis.Object.extend("GameScore");
const query = new Moralis.Query(GameScore);
query.equalTo("playerName", "Dan Stemkoski");
const results = await query.find();
alert("Successfully retrieved " + results.length + " scores.");
// Do something with the returned Moralis.Object values
for (let i = 0; i < results.length; i++) {
const object = results[i];
alert(object.id + ' - ' + object.get('playerName'));
}

Query Constraints

There are several ways to put constraints on the objects found by a Moralis.Query. You can filter out objects with a particular key-value pair with notEqualTo:

query.notEqualTo("playerName", "Michael Yabuti");

You can give multiple constraints, and objects will only be in the results if they match all of the constraints. In other words, it's like an AND of constraints.

query.notEqualTo("playerName", "Michael Yabuti");
query.greaterThan("playerAge", 18);

You can limit the number of results by setting limit. By default, results are limited to 100. In the old Moralis hosted backend, the maximum limit was 1,000, but Moralis Server removed that constraint:

query.limit(10); // limit to at most 10 results

If you want exactly one result, a more convenient alternative may be to use first instead of using find.

const GameScore = Moralis.Object.extend("GameScore");
const query = new Moralis.Query(GameScore);
query.equalTo("playerEmail", "[email protected]");
const object = await query.first();

You can skip the first results by setting skip. In the old Moralis hosted backend, the maximum skip value was 10,000, but Moralis Server removed that constraint. This can be useful for pagination:

query.skip(10); // skip the first 10 results

If you want to know the total number of rows in a table satisfying your query, for e.g. pagination purposes - you can use withCount.

Note: Enabling this flag will change the structure of response, see the example below.

Let's say you have 200 rows in a table called GameScore:

const GameScore = Moralis.Object.extend("GameScore");
const query = new Moralis.Query(GameScore);
query.limit(25);
const results = await query.find(); // [ GameScore, GameScore, ...]
// to include count:
query.withCount();
const response = await query.find(); // { results: [ GameScore, ... ], count: 200 }

⚠️ Сount operations can be slow and expensive.

If you only want to get the count without objects - use Counting Objects.

For sortable types like numbers and strings, you can control the order in which results are returned:

// Sorts the results in ascending order by the score field
query.ascending("score");
// Sorts the results in descending order by the score field
query.descending("score");

For sortable types, you can also use comparisons in queries:

// Restricts to wins < 50
query.lessThan("wins", 50);
// Restricts to wins <= 50
query.lessThanOrEqualTo("wins", 50);
// Restricts to wins > 50
query.greaterThan("wins", 50);
// Restricts to wins >= 50
query.greaterThanOrEqualTo("wins", 50);

If you want to retrieve objects matching any of the values in a list of values, you can use containedIn, providing an array of acceptable values. This is often useful to replace multiple queries with a single query. For example, if you want to retrieve scores made by any player in a particular list:

// Finds scores from any of Jonathan, Dario, or Shawn
query.containedIn("playerName",
["Jonathan Walsh", "Dario Wunsch", "Shawn Simon"]);

If you want to retrieve objects that do not match any of several values you can use notContainedIn, providing an array of acceptable values. For example if you want to retrieve scores from players besides those in a list:

// Finds scores from anyone who is neither Jonathan, Dario, nor Shawn
query.notContainedIn("playerName",
["Jonathan Walsh", "Dario Wunsch", "Shawn Simon"]);

If you want to retrieve objects that have a particular key set, you can use exists. Conversely, if you want to retrieve objects without a particular key set, you can use doesNotExist.

// Finds objects that have the score set
query.exists("score");
// Finds objects that don't have the score set
query.doesNotExist("score");

You can use the matchesKeyInQuery method to get objects where a key matches the value of a key in a set of objects resulting from another query. For example, if you have a class containing sports teams and you store a user's hometown in the user class, you can issue one query to find the list of users whose hometown teams have winning records. The query would look like:

const Team = Moralis.Object.extend("Team");
const teamQuery = new Moralis.Query(Team);
teamQuery.greaterThan("winPct", 0.5);
const userQuery = new Moralis.Query(Moralis.User);
userQuery.matchesKeyInQuery("hometown", "city", teamQuery);
// results has the list of users with a hometown team with a winning record
const results = await userQuery.find();

Conversely, to get objects where a key does not match the value of a key in a set of objects resulting from another query, use doesNotMatchKeyInQuery. For example, to find users whose hometown teams have losing records:

const losingUserQuery = new Moralis.Query(Moralis.User);
losingUserQuery.doesNotMatchKeyInQuery("hometown", "city", teamQuery);
// results has the list of users with a hometown team with a losing record
const results = await losingUserQuery.find();

To filter rows based on objectId's from pointers in a second table, you can use dot notation:

const rolesOfTypeX = new Moralis.Query('Role');
rolesOfTypeX.equalTo('type', 'x');
const groupsWithRoleX = new Moralis.Query('Group');
groupsWithRoleX.matchesKeyInQuery('objectId', 'belongsTo.objectId', rolesOfTypeX);
groupsWithRoleX.find().then(function(results) {
// results has the list of groups with role x
});

You can restrict the fields returned by calling select with a list of keys. To retrieve documents that contain only the score and playerName fields (and also special built-in fields such as objectId, createdAt, and updatedAt):

const GameScore = Moralis.Object.extend("GameScore");
const query = new Moralis.Query(GameScore);
query.select("score", "playerName");
query.find().then(function(results) {
// each of results will only have the selected fields available.
});

Similarly, use exclude to remove undesired fields while retrieving the rest:

const GameScore = Moralis.Object.extend("GameScore");
const query = new Moralis.Query(GameScore);
query.exclude("playerName");
query.find().then(function(results) {
// Now each result will have all fields except `playerName`
});

The remaining fields can be fetched later by calling fetch on the returned objects:

query.first().then(function(result) {
// only the selected fields of the object will now be available here.
return result.fetch();
}).then(function(result) {
// all fields of the object will now be available here.
});

Remember to make sure that addresses are in lowercase when making query constraints against an address column as described in the casing subheading in the Web3 section

Queries on Array Values

For keys with an array type, you can find objects where the key's array value contains 2 by:

// Find objects where the array in arrayKey contains 2.
query.equalTo("arrayKey", 2);

You can also find objects where the key's array value contains each of the elements 2, 3, and 4 with the following:

// Find objects where the array in arrayKey contains all of the elements 2, 3, and 4.
query.containsAll("arrayKey", [2, 3, 4]);

Query Testing in the Dashboard

A note before getting deeper into queries. It's possible to run query code directly in the Moralis Dashboard! It can be found by navigating to "API Console > JS Console" in the menu. This is a great place to play around while first building your queries as it doesn't require any setup or initialization. It functions exactly like the JavaScript console in the browser except that it has direct access to the Moralis SDK and Master Key.

Type the query exactly as you would in the client or Cloud Code. Inlude a console.log() to print out the results then press the "Run" button. Some differences to watch out for.

  • Need to use the Parse key word instead of Moralis

    • i.e new Parse.Query("EthTokenTransfers")

    • This will likely be fixed in a future version (Moralis is a fork of Parse)

  • Don't escape $ in queries

  • Can use the Master Key

    • const results = query.find({ useMasterKey: true })

The code can be saved between sessions by pressing "Save".

Queries on String Values

Use startsWith to restrict to string values that start with a particular string. Similar to a MySQL LIKE operator, this is indexed so it is efficient for large datasets:

// Finds barbecue sauces that start with "Big Daddy's".
const query = new Moralis.Query(BarbecueSauce);
query.startsWith("name", "Big Daddy's");

The above example will match any BarbecueSauce objects where the value in the "name" String key starts with "Big Daddy's". For example, both "Big Daddy's" and "Big Daddy's BBQ" will match, but "big daddy's" or "BBQ Sauce: Big Daddy's" will not.

Queries that have regular expression constraints are very expensive, especially for classes with over 100,000 records. Moralis restricts how many such operations can be run on a particular app at any given time.

You can use fullText for efficient search capabilities. Text indexes are automatically created for you. Your strings are turned into tokens for fast searching.

const query = new Moralis.Query(BarbecueSauce);
query.fullText('name', 'bbq');

The above example will match any BarbecueSauce objects where the value in the "name" String key contains "bbq". For example, both "Big Daddy's BBQ", "Big Daddy's bbq" and "Big BBQ Daddy" will match.

// You can sort by weight / rank. ascending() and select()
const query = new Moralis.Query(BarbecueSauce);
query.fullText('name', 'bbq');
query.ascending('$score');
query.select('$score');
query.find()
.then(function(results) {
// results contains a weight / rank in result.get('score')
})
.catch(function(error) {
// There was an error.
});

For Case or Diacritic Sensitive search, please use the REST API.

Relational Queries

There are several ways to issue queries for relational data. If you want to retrieve objects where a field matches a particular Moralis.Object, you can use equalTo just like for other data types. For example, if each Comment has a Post object in its post field, you can fetch comments for a particular Post:

// Assume Moralis.Object myPost was previously created.
const query = new Moralis.Query(Comment);
query.equalTo("post", myPost);
// comments now contains the comments for myPost
const comments = await query.find();

If you want to retrieve objects where a field contains a Moralis.Object that matches a different query, you can use matchesQuery. In order to find comments for posts containing images, you can do:

const Post = Moralis.Object.extend("Post");
const Comment = Moralis.Object.extend("Comment");
const innerQuery = new Moralis.Query(Post);
innerQuery.exists("image");
const query = new Moralis.Query(Comment);
query.matchesQuery("post", innerQuery);
// comments now contains the comments for posts with images.
const comments = await query.find();

If you want to retrieve objects where a field contains a Moralis.Object that does not match a different query, you can use doesNotMatchQuery. In order to find comments for posts without images, you can do:

const Post = Moralis.Object.extend("Post");
const Comment = Moralis.Object.extend("Comment");
const innerQuery = new Moralis.Query(Post);
innerQuery.exists("image");
const query = new Moralis.Query(Comment);
query.doesNotMatchQuery("post", innerQuery);
// comments now contains the comments for posts without images.
const comments = await query.find();

You can also do relational queries by objectId:

const post = new Post();
post.id = "1zEcyElZ80";
query.equalTo("post", post);

In some situations, you want to return multiple types of related objects in one query. You can do this with the include method. For example, let's say you are retrieving the last ten comments, and you want to retrieve their related posts at the same time:

const query = new Moralis.Query(Comment);
// Retrieve the most recent ones
query.descending("createdAt");
// Only retrieve the last ten
query.limit(10);
// Include the post data with each comment
query.include("post");
// Comments now contains the last ten comments, and the "post" field
const comments = await query.find();
// has been populated. For example:
for (let i = 0; i < comments.length; i++) {
// This does not require a network access.
const post = comments[i].get("post");
}

You can also do multi level includes using dot notation. If you wanted to include the post for a comment and the post's author as well you can do:

query.include(["post.author"]);

You can issue a query with multiple fields included by calling include multiple times. This functionality also works with Moralis.Query helpers like first and get.

Counting Objects

Note: In the old Moralis hosted backend, count queries were rate limited to a maximum of 160 requests per minute. They also returned inaccurate results for classes with more than 1,000 objects. But, Moralis Server has removed both constraints and can count objects well above 1,000.

If you just need to count how many objects match a query, but you do not need to retrieve all the objects that match, you can use count instead of find. For example, to count how many games have been played by a particular player:

const GameScore = Moralis.Object.extend("GameScore");
const query = new Moralis.Query(GameScore);
query.equalTo("playerName", "Sean Plott");
const count = await query.count();
alert("Sean has played " + count + " games");

Compound Queries

For more complex queries you might need compound queries. A compound query is a logical combination (e. g. "and" or "or") of sub queries.

Note that we do not support GeoPoint or non-filtering constraints (e.g. near, withinGeoBox, limit, skip, ascending/descending, include) in the subqueries of the compound query.

OR-ed query constraints

If you want to find objects that match one of several queries, you can use Moralis.Query.or method to construct a query that is an OR of the queries passed in. For instance if you want to find players who either have a lot of wins or a few wins, you can do:

const lotsOfWins = new Moralis.Query("Player");
lotsOfWins.greaterThan("wins", 150);
const fewWins = new Moralis.Query("Player");
fewWins.lessThan("wins", 5);
const mainQuery = Moralis.Query.or(lotsOfWins, fewWins);
mainQuery.find()
.then(function(results) {
// results contains a list of players that either have won a lot of games or won only a few games.
})
.catch(function(error) {
// There was an error.
});

AND-ed query constraints

If you want to find objects that match all conditions, you normally would use just one query. You can add additional constraints to the newly created Moralis.Query that act as an 'and' operator.

const query = new Moralis.Query("User");
query.greaterThan("age", 18);
query.greaterThan("friends", 0);
query.find()
.then(function(results) {
// results contains a list of users both older than 18 and having friends.
})
.catch(function(error) {
// There was an error.
});

Sometimes the world is more complex than this simple example and you may need a compound query of sub queries. You can use Moralis.Query.and method to construct a query that is an AND of the queries passed in. For instance if you want to find users in the age of 16 or 18 who have either no friends or at least 2 friends, you can do:

const age16Query = new Moralis.Query("User");
age16Query.equalTo("age", 16);
const age18Query = new Moralis.Query("User");
age18Query.equalTo("age", 18);
const friends0Query = new Moralis.Query("User");
friends0Query.equalTo("friends", 0);
const friends2Query = new Moralis.Query("User");
friends2Query.greaterThan("friends", 2);
const mainQuery = Moralis.Query.and(
Moralis.Query.or(age16Query, age18Query),
Moralis.Query.or(friends0Query, friends2Query)
);
mainQuery.find()
.then(function(results) {
// results contains a list of users in the age of 16 or 18 who have either no friends or at least 2 friends
// results: (age 16 or 18) and (0 or >2 friends)
})
.catch(function(error) {
// There was an error.
});

Aggregate

Queries can be made using aggregates, allowing you to retrieve objects over a set of input values. The results will not be Moralis.Objects since you will be aggregating your own fields

  • MasterKey is Required.

Aggregates use stages to filter results by piping results from one stage to the next. The output from the previous stages becomes the input for the next stage.

You can create a pipeline using an Array or an Object.

const pipelineObject = {
};
const pipelineArray = [
];

For a list of available stages please refer to Mongo Aggregate Documentation.

  • Note: Most operations in Mongo Aggregate Documentation will work with Moralis Server, but _id doesn't exist. Please replace with objectId.

Match

Match pipeline is similar to equalTo.

const pipeline = [
{ match: { name: 'BBQ' } }
];
const query = new Moralis.Query("User");
query.aggregate(pipeline)
.then(function(results) {
// results contains name that matches 'BBQ'
})
.catch(function(error) {
// There was an error.
});

You can match by comparison.

const pipeline = [
{ match: { score: { $gt: 15 } } }
];
const query = new Moralis.Query("User");
query.aggregate(pipeline)
.then(function(results) {
// results contains score greater than 15
})
.catch(function(error) {
// There was an error.
});

You can read more about what operators are available in the Mongo query operators docs.

Lookup (Join)

The lookup pipeline is similar to LEFT OUTER JOIN in SQL. It will match documents in another collection and bring them into the results as an array property. Use this for collections that were not created with an explicit relation (see Relational Data, Relational Queries).

For example, in a portfolio app you might want to display all of a user's token transactions. In Moralis these are stored in the EthTokenTransfers collection. This collection has all the info needed except the token name and number of decimals, which are needed to display this info in a nice way on the front end. The extra info is in the EthTokenBalance collection and to get it we need a lookup. For more details check out the Mongo lookup docs.

Simple Lookup (Single Equality)

Unfortunately for our use case theEthTokenBalance collection has info for not just our current user, but all users. This means the token name is repeated for every user that also holds that token. This means joining on more than 1 attribute and requires a different syntax which will be covered below. For now, lets assume we have another collection called Token which looks like this:

Token {
objectId: string,
token_address: string,
symbol: string,
name: string,
decimals: number
}

Then you would define a Cloud Function like this (aggregate queries must be run in Cloud Code). Remember to escape the $ like \$ to prevent parsing errors (this is now fixed!).

// this goes in the Moralis server Cloud Functions section
Moralis.Cloud.define("getUserTokenTransfers", function(request) {
const userAddress = request.params.userAddress;
const query = new Moralis.Query("EthTokenTransfers");
const pipeline = [
// only transfers to or from userAddress
{match: {$expr: {$or: [
{$eq: ["$from_address", userAddress]},
{$eq: ["$to_address", userAddress]},
]}}},
// join to Token collection on token_address
{lookup: {
from: "Token",
localField: "token_address",
foreignField: "token_address",
as: "token"
}}
];
return query.aggregate(pipeline);
});

The output would look like the following where as: "token" specifies the name for the output array of matching documents in the Token table. This array will have length greater than 1 if the join is 1-to-many instead of 1-to-1.

[
{
// EthTokenTransfer collection attributes
"block_timestamp":{...}
"token_address": "0x8762db106b2c2a0bccb3a80d1ed41273552616e8"
"from_address": "0xba65016890709dbc9491ca7bf5de395b8441dc8b"
"to_address": "0x29781d9fca70165cbc952b8c558d528b85541f0b"
"value": "29803465370630263212090"
"transaction_hash": "0xfc611dbae7c67cd938fca58a0cc2fe46d224f71b91472d4c9241e997e6440ac1"
"token": [
{
// Token collection attributes from lookup
"_id": "HKA8dzHG1A"
"token_address": "0x8762db106b2c2a0bccb3a80d1ed41273552616e8"
"symbol": "RSR"
"name": "Reserve Rights"
"decimals": 18
}
]
}
]

Complex Join (multiple equality)

Joining on multiple attributes requires a nested pipeline in the lookup, but gives more flexibility when defining how the collections should be joined. The above query can be re-written to join EthTokenTranfers with EthTokenBalance.

// this goes in the Moralis server Cloud Functions section
Moralis.Cloud.define("getUserTokenTransfers", function(request) {
const userAddress = request.params.userAddress;
const query = new Moralis.Query("EthTokenTransfers");
const pipeline = [
// only transfers to or from userAddress
{match: {$expr: {$or: [
{$eq: ["$from_address", userAddress]},
{$eq: ["$to_address", userAddress]},
]}}},
// join to EthTokenBalance on token_address and userAddress
{lookup: {
from: "EthTokenBalance",
let: { tokenAddress: "$token_address", userAddress: userAddress},
pipeline: [
{$match: {$expr: {$and: [
{ $eq: ["$token_address", "$$tokenAddress"] },
{ $eq: ["$address", "$$userAddress"] },
]}}],
as: "EthTokenBalance",
}}
];
return query.aggregate(pipeline);
});

The lookup pipeline uses a match stage to specify the additional join conditions. Also take note of the let which defines variables to be used by the lookup pipeline. This is required as the lookup pipeline does not have direct access to the attributes in the original collection. See the "Specify Multiple Join Conditions with $lookup" section in the MongoDB $lookup docs for more info.

Project (Select)

Project pipeline is similar to keys or select, add or remove existing fields.

const pipeline = [
{ project: { name: 1 } }
];
const query = new Moralis.Query("User");
query.aggregate(pipeline)
.then(function(results) {
// results contains only name field
})
.catch(function(error) {
// There was an error.
});

Group

Group pipeline is similar to distinct.

You can group by a field.

// score is the field. $ before score lets the database know this is a field
const pipeline = [
{ group: { objectId: '$score' } }
];
const query = new Moralis.Query("User");
query.aggregate(pipeline)
.then(function(results) {
// results contains unique score values
})
.catch(function(error) {
// There was an error.
});

You can apply collective calculations like $sum, $avg, $max, $min.

// total will be a newly created field to hold the sum of score field
const pipeline = [
{ group: { objectId: null, total: { $sum: '$score' } } }
];
const query = new Moralis.Query("User");
query.aggregate(pipeline)
.then(function(results) {
// results contains sum of score field and stores it in results[0].total
})
.catch(function(error) {
// There was an error.
});

If you want to perform calculations on a column that contains numerical values but are stored as strings in the database, you have to cast the value to a numerical data type. For example, a uint256 stored in a string column should be converted with $toLong.

{ group: { objectId: '$nftItem', priceOfAllNfts: { $sum: {$toLong : '$price'} } } },

The collection of documents matching the grouping field can be accessed with $$ROOT, and can be pushed into an array using the $push accumulator.

// gameswill be an array of all the games played by each unique player
const pipeline = [
{ group: { objectId: '$playerId', games: { $push: "$$ROOT" } } },
];
const query = new Moralis.Query("GameRound");
query.aggregate(pipeline)
.then(function(results) {
// results contains a collection of players with their respective games played
})
.catch(function(error) {
// There was an error.
});

You can group by the column of a child document by using dot notation. However, you must wrap it in quotation marks.

{ group: { objectId: '$player.username', games: { $push: "$$ROOT" } } },

Sort

The sort stage is used to sort the results by a given column in a specific order.

To specify sort order, 1 is used to sort in ascending order, and -1 is used to sort in descending order.

// This will sort the rows in ascending order based on the username field
{ sort : { username : 1 } }

You can sort by several columns in a given order.

// This will sort the rows in ascending order based on the country field,
// and then in descending order on the city field
{ sort : { country: 1, city: -1 } }

Skip

The skip stage skips over the specified number of rows that is passed into the stage. This is often used when implementing pagination functionality.

// This stage will skip the first 5 rows passed into this stage
{ skip : 5 }

Limit

The limit stage only includes the first n number of rows that is passed into the stage.

// This stage will throw away all rows except the first 5
{ limit: 5 }

Count

The count stage returns the number of rows passed into the stage assigned to a variable name.

// This stage will return the number of rows passed into the stage
// and store it in the variable numberOfNfts
{ count: "numberOfNfts" }

Distinct

Queries can be made using distinct, allowing you find unique values for a specified field.

  • MasterKey is required.

const query = new Moralis.Query("User");
query.distinct("age")
.then(function(results) {
// results contains unique age
})
.catch(function(error) {
// There was an error.
});

You can also restrict results by using equalTo.

const query = new Moralis.Query("User");
query.equalTo("name", "foo");
query.distinct("age")
.then(function(results) {
// results contains unique age where name is foo
})
.catch(function(error) {
// There was an error.
});

Read Preference

When using a MongoDB replica set, you can use the query.readPreference(readPreference, includeReadPreference, subqueryReadPreference) function to choose from which replica the objects will be retrieved. The includeReadPreference argument chooses from which replica the included pointers will be retrieved and the subqueryReadPreference argument chooses in which replica the subqueries will run. The possible values are PRIMARY (default), PRIMARY_PREFERRED, SECONDARY, SECONDARY_PREFERRED, or NEAREST. If the includeReadPreference argument is not passed, the same replica chosen for readPreference will be also used for the includes. The same rule applies for the subqueryReadPreference argument.

query.readPreference(
'SECONDARY',
'SECONDARY_PREFERRED',
'NEAREST'
);