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:

convex/tasks.ts
TS
import { query } from "./_generated/server";
import { v } from "convex/values";

export const getTask = query({
  args: { taskId: v.id("tasks") },
  handler: async (ctx, args) => {
    const task = await ctx.db.get(args.taskId);
    // do something with `task`
  },
});

Note: You should use the v.id validator like in the example above to make sure you are not exposing data from tables other than the ones you intended.

Querying documents

Document queries always begin by choosing the table to query with the db.query method:

convex/tasks.ts
TS
import { query } from "./_generated/server";

export const listTasks = query({
  handler: async (ctx) => {
    const tasks = await ctx.db.query("tasks").collect();
    // do something with `tasks`
  },
});

Then you can:

filter
order
and await the results

We'll see how this works in the examples below.

Filtering

The filter method allows you to restrict the documents that your document 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 document query finds documents in the users table where doc.name === "Alex":

// Get all users named "Alex".
const usersNamedAlex = await ctx.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 document query finds documents where doc.age >= 18:

// Get all users with an age of 18 or higher.
const adults = await ctx.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 document 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 ctx.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 document query finds documents where doc.name === "Alex" && doc.age >= 18:

// Get all users named "Alex" whose age is at least 18.
const adultAlexes = await ctx.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 ctx.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 ctx.db.query("messages").order("asc").collect();

// Get all messages, newest to oldest.
const messages = await ctx.db.query("messages").order("desc").collect();

If you need to sort on a field other than _creationTime and your document 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 ctx.db.query("messages").collect();
const topTenMostLikedMessages = recentMessages
  .sort((a, b) => b.likes - a.likes)
  .slice(0, 10);

For document queries that return larger numbers of documents, you'll want to use an index to improve the performance. Document 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 ctx.db
  .query("messages")
  .withIndex("by_likes")
  .order("desc")
  .take(20);

See Limit expressions with indexes for details.

Retrieving results

Most of our previous examples have ended the document 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 ctx.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 ctx.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 ctx.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. In Convex, there is no specific query language for complex logic like a join, an aggregation, or a group by.

Instead, you can write the complex logic in Javascript! Convex guarantees that the results will be consistent.

Join

Table join might look like:

convex/events.ts
TS
import { query } from "./_generated/server";
import { v } from "convex/values";

export const eventAttendees = query({
  args: { eventId: v.id("events") },
  handler: async (ctx, args) => {
    const event = await ctx.db.get(args.eventId);
    return Promise.all(
      (event?.attendeeIds ?? []).map((userId) => ctx.db.get(userId))
    );
  },
});

Aggregation

Here's an example of computing an average:

convex/purchases.ts
TS
import { query } from "./_generated/server";
import { v } from "convex/values";

export const averagePurchasePrice = query({
  args: { email: v.string() },
  handler: async (ctx, args) => {
    const userPurchases = await ctx.db
      .query("purchases")
      .filter((q) => q.eq(q.field("buyer"), args.email))
      .collect();
    const sum = userPurchases.reduce((a, { value: b }) => a + b, 0);
    return sum / userPurchases.length;
  },
});

Group by

Here's an example of grouping and counting:

convex/purchases.ts
TS
import { query } from "./_generated/server";
import { v } from "convex/values";

export const numPurchasesPerBuyer = query({
  args: { email: v.string() },
  handler: async (ctx, args) => {
    const userPurchases = await ctx.db.query("purchases").collect();

    return userPurchases.reduce(
      (counts, { buyer }) => ({
        ...counts,
        [buyer]: counts[buyer] ?? 0 + 1,
      }),
      {} as Record<string, number>
    );
  },
});

Querying performance and limits

Most of the example document queries above can lead to a full table scan. That is, for the document query to return the requested results, it might need to walk over every single document in the table.

Take this simple example:

const tasks = await ctx.db.query("tasks").take(5);

This document query will not scan more than 5 documents.

On the other hand, this document query:

const tasks = await ctx.db
  .query("tasks")
  .filter((q) => q.eq(q.field("isCompleted"), true))
  .first();

might need to walk over every single document in the "tasks" table just to find the first one with isCompleted: true.

If a table has more than a few thousand documents, you should use indexes to improve your document query performance. Otherwise, you may run into enforced limits, detailed here.

Reading a single document​

Querying documents​

Filtering​

Equality conditions​

Comparisons​

Arithmetic​

Combining operators​

Ordering​

Retrieving results​

Taking n results​

Finding the first result​

Using a unique result​

Loading a page of results​

More complex queries​

Join​

Aggregation​

Group by​

Querying performance and limits​