Reading Data
Query and mutation functions can read data from database tables using document ids and document queries.
Reading a single document
Given a single document's id you can read its data with the
db.get
method:
export default query(async ({ db }, { taskId }) => {
const task = await db.get(taskId);
// ...
});
Querying documents
Document queries always begin by choosing the table to query with the
db.query
method:
export default query(async ({ db }) => {
db.query("tasks")…
});
Then you can:
- Filter
- order
- and
await
the results
We'll see how this works in the examples below.
The examples in this document are all full table scan queries meaning that they may require Convex to inspect every document in the table. If your tables have more than a few thousand documents you should use indexes to improve your query performance.
Filtering
The filter
method allows you to
restrict the documents that your query returns. This method takes a filter
constructed by FilterBuilder
and will
only select documents that match.
The examples below demonstrate some of the common uses of filter
. You can see
the full list of available filtering methods
in the reference docs.
If you need to filter to documents containing some keywords, use a search query.
Equality conditions
This query finds documents in the users
table where doc.name === "Alex"
:
// Get all users named "Alex".
const usersNamedAlex = await db
.query("users")
.filter(q => q.eq(q.field("name"), "Alex"))
.collect();
Here q
is the FilterBuilder
utility
object. It contains methods for all of our supported filter operators.
This filter will run on all documents in the table. For each document,
q.field("name")
evaluates to the name
property. Then q.eq
checks if this
property is equal to "Alex"
.
If your query references a field that is missing from a given document then that
field will be considered to have the value undefined
.
Comparisons
Filters can also be used to compare fields against values. This query finds
documents where doc.age >= 18
:
// Get all users with an age of 18 or higher.
const adults = await db
.query("users")
.filter(q => q.gte(q.field("age"), 18))
.collect();
Here the q.gte
operator checks if the first argument (doc.age
) is greater
than or equal to the second (18
).
Here's the full list of comparisons:
Operator | Equivalent TypeScript |
---|---|
q.eq(l, r) | l === r |
q.neq(l, r) | l !== r |
q.lt(l, r) | l < r |
q.lte(l, r) | l <= r |
q.gt(l, r) | l > r |
q.gte(l, r) | l >= r |
Arithmetic
You can also include basic arithmetic in your queries. This query finds
documents in the carpets
table where doc.height * doc.width > 100
:
// Get all carpets that have an area of over 100.
const largeCarpets = await db
.query("carpets")
.filter(q => q.gt(q.mul(q.field("height"), q.field("width")), 100))
.collect();
Here's the full list of arithmetic operators:
Operator | Equivalent TypeScript |
---|---|
q.add(l, r) | l + r |
q.sub(l, r) | l - r |
q.mul(l, r) | l * r |
q.div(l, r) | l / r |
q.mod(l, r) | l % r |
q.neg(x) | -x |
Combining operators
You can construct more complex filters using methods like q.and
, q.or
, and
q.not
. This query finds documents where
doc.name === "Alex" && doc.age >= 18
:
// Get all users named "Alex" whose age is at least 18.
const adultAlexes = await db
.query("users")
.filter(q => q.and(q.eq(q.field("name"), "Alex"), q.gte(q.field("age"), 18)))
.collect();
Here is a query that finds all users where
doc.name === "Alex" || doc.name === "Emma"
:
// Get all users named "Alex" or "Emma".
const usersNamedAlexOrEmma = await db
.query("users")
.filter(q =>
q.or(q.eq(q.field("name"), "Alex"), q.eq(q.field("name"), "Emma"))
)
.collect();
Ordering
By default Convex always returns documents ordered by
_creationTime
.
You can use .order("asc" | "desc")
to
pick whether the order is ascending or descending. If the order isn't specified,
it defaults to ascending.
// Get all messages, oldest to newest.
const messages = await db.query("messages").order("asc").collect();
// Get all messages, newest to oldest.
const messages = await db.query("messages").order("desc").collect();
If you need to sort on a field other than _creationTime
and your query returns
a small number of documents (on the order of hundreds rather than thousands of
documents), consider sorting in Javascript:
// Get top 10 most liked messages, assuming messages is a fairly small table:
const messages = await db.query("messages").collect();
const topTenMostLikedMessages = recentMessages
.sort((a, b) => b.likes - a.likes)
.slice(0, 10);
For queries that return larger numbers of documents, you'll want to use an index to improve the performance. Queries that use indexes will be ordered based on the columns in the index and can avoid slow table scans.
// Get the top 20 most liked messages of all time, using the "by_likes" index.
const messages = await db
.query("messages")
.withIndex("by_likes", q => q)
.order("desc")
.take(20);
See Limit expressions with indexes for details.
Retrieving results
Most of our previous examples have ended the query with the
.collect()
method, which returns all
the documents that match your filters. Here are the other options for retrieving
results.
Taking n
results
.take(n)
selects only the first n
results that match your query.
const users = await db.query("users").take(5);
Finding the first result
.first()
selects the first document that
matches your query and returns null
if no documents were found.
// We expect only one user with that email address.
const userOrNull = await db
.query("users")
.filter(q => q.eq(q.field("email"), "test@example.com"))
.first();
Using a unique result
.unique()
selects the single document
from your query or returns null
if no documents were found. If there are
multiple results it will throw an exception.
// Our counter table only has one document.
const counterOrNull = await db.query("counter").unique();
Loading a page of results
.paginate(opts)
loads a page
of results and returns a Cursor
for loading
additional results.
See Paginated Queries to learn more.
More complex queries
Convex prefers to have a few, simple ways to walk through and select documents from tables. When there is complex logic to perform, like an aggregation, a group by, or a join, the way to solve that in Convex is different than many of the databases you're used to.
In Convex, instead of using some specific query language, you can just write the complex logic in JavaScript!
Here's an example of computing an average:
import { query } from "./_generated/server";
export const averagePurchasePrice = query(({ db }, { email }) => {
const userPurchases = await db
.query("purchases")
.filter(q => q.eq(q.field("buyer"), email));
const average = userPurchases.reduce((a, b) => a + b) / userPurchases.length;
return average;
});
Table join might look like:
import { query } from "./_generated/server";
export const eventAttendees = query(async ({ db }, { eventId }) => {
const event = await db.get(eventId);
return Promise.all(event.attendeeIds.map(userId => db.get(userId)));
});