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 multiple 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.Object
s using find
. For example, to retrieve the monster that have a particular ownerName
, use the equalTo
method to constrain the value for a key.
For an introduction video tutorial Click Here
Using Master Key
There are cases when a master key is needed for a query,
For example, if you want to get the list of all the users, you can only do that in a cloud function using a master key - because a user can not read the info for the other users due to ACL.
Query Constraints
For a Query Constraints video tutorial Click Here
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
:
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.
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 Dapps now removed that constraint:
If you want exactly one result, a more convenient alternative may be to use first
instead of using find
.
You can skip the first results by setting skip
. In the old Moralis hosted backend, the maximum skip value was 10,000, but Moralis Dapps now removed that constraint. This can be useful for pagination:
For a Query pagination video tutorial Click Here
To get 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 the response, see the example below.
Example - Let's say you have 200 rows in a table called Monster
:
Сount operations can be slow and expensive.
If you only want to get the count without objects - use Counting Objects.
To sort on sortable types like numbers and strings, you can control the order in which results are returned:
To do comparisons in queries for sortable types :
To retrieve objects matching 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 monsters owned by any monster owner in a particular list:
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 monsters from monster owners besides those in a list:
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
.
To get objects where a key matches the value of a key in a set of objects resulting from another query. You can use the matchesKeyInQuery
method.
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 this:
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:
To filter rows based on objectId
's from pointers in a second table, you can use dot notation:
To restrict the fields returned by calling select
with a list of keys.
For example, To retrieve documents that contain only the strength
and ownerName
fields (and also special built-in fields such as objectId
, createdAt
, and updatedAt
):
Similarly, to remove undesired fields while retrieving the rest use exclude
:
The remaining fields can be fetched later by calling fetch
on the returned objects:
Remember to make sure that addresses are in lowercase when making query constraints against an address column as described in Address Casing
Queries on Array Values
For keys with an array type, you can find objects where the key's array value contains 2 by:
You can also find objects where the key's array value contains each of the elements 2, 3, and 4 with the following:
Query Testing in the Dashboard
Note: 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. Include a console.log()
to print out the results then press the "Run" button. Some differences to watch out for:
Need to use the
Parse
keyword instead ofMoralis
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.You can use the master key -
const results = query.find({ useMasterKey: true })
The code can be saved between sessions by clicking "Save".
Queries on String Values
StartsWith Search
Use startsWith
to restrict to string values that start with a particular string. Similar to a MySQL LIKE operator, this is indexed so it's efficient for large datasets:
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 operations that can run on a particular app at any given time.
Full-Text Search
Use fullText
for efficient search capabilities. Text indexes are automatically created for you. Your strings are turned into tokens for fast searching.
Note: Full-Text Search can be resource-intensive. Ensure the cost of using indexes is worth the benefit, see storage requirements & performance costs of text indexes.
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.
For "Case" or "Diacritic Sensitive" search, please use the REST API.
Relational Queries
For a Query pagination video tutorial Click Here
There are several ways to issue queries for relational data. 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
:
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:
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:
You can also do relational queries by objectId
:
To return multiple types of related objects in one query use theinclude
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:
To do multi-level includes using dot notation. Use the below query.
For example, If you wanted to include the post for a comment and the post's author as well you can do:
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 Dapp has removed both constraints and can count objects well above 1,000.
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 monsters have been owned by a particular monster owner:
Compound Queries
For an advance Queries video tutorial Click Here
For more complex queries, you might need compound queries. A compound query is a logical combination (e. g. "and" or "or") of subqueries.
Note: 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
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:
AND-ed Query Constraints
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.
Sometimes the world is more complex than this simple example and you may need a compound query of subqueries. 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 two friends, you can do:
Aggregate
Queries can be made using aggregates, allowing you to retrieve objects over a set of input values. The results will not be Moralis.Object
s 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.
For a Query aggregates video tutorial Click Here
For a list of available stages please refer to Mongo Aggregate Documentation.
Note: Most operations in the Mongo Aggregate Documentation will work with Moralis Server, but _id
do not exist. Please replace with objectId
.
Match
Match pipeline is similar to equalTo
.
Legacy UI is present in this video, some things might be different
You can match by comparison.
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).
Legacy UI is present in this video, some things might be different
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 one attribute and it requires a different syntax which will be covered below. For now, let's assume we have another collection called Token
which looks like this:
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!).
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 a length greater than 1 if the join is 1-to-many instead of 1-to-1.
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
.
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)
Legacy UI is present in this video, some things might be different
Project pipeline is similar to keys
or select
, add or remove existing fields.
Group
Legacy UI is present in this video, some things might be different
Group pipeline is similar to distinct
.
You can group by field:
You can apply collective calculations like $sum, $avg, $max, $min.
If you want to perform calculations on a column that contains numerical values but they're 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
.
The collection of documents matching the grouping field can be accessed with $$ROOT
, and can be pushed into an array using the $push
accumulator.
You can group by the column of a child document by using dot notation. However, you must wrap it in quotation marks.
Sort
Legacy UI is present in this video, some things might be different
The sorting 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.
You can sort by several columns in a given order.
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.
Limit
The limit stage only includes the first n
number of rows that are passed into the stage.
Count
The count stage returns the number of rows passed into the stage assigned to a variable name.
Distinct
Queries can be made using distinct
, allowing you to find unique values for a specified field.
MasterKey
is required.
You can also restrict results by using equalTo
.
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 also be used for the includes. The same rule applies for the subqueryReadPreference
argument.
Tutorials
Legacy UI might be present in the videos, some things might be different
Last updated