Query with `whereIn` in convex

[cobalt condor]

I want to know how to do a whereIn query in convex without scanning the whole table.

Trying to find a bunch of records by Ids in convex seems to be impossible.

The filter() function only allows q.eq and not q.in() to pass a list.

To be clear, I'm not talking about eager fetching relations. I'm talking about a single query. What if you have entities that many to many relationships. Whether you use a join/edge relation table or store an array of related entities, there's no way to fetch the dependent entities all at once

e.g. A.b_id = [x,y,z]

How do you query SELECT B WHERE B.id IN (x, y, x).

Using the convex-helper filter filter still scans the WHOLE table for b before filtering. In a large table this easily reaches the max documents read limit and max size (16mb) for a function read.

The only work around I've found is to call an action instead of a query in order to paginate, but actions have way lower limits than functions.

Help!

[covert orchid]

Hi @cobalt condor. I'm kapa.ai, your friendly support bot. I will try to answer your question by searching through the knowledge base. :wave:

---

In Convex, there is no single whereIn operator in the database query API. To fetch multiple documents by their IDs without scanning the entire table, you should use parallelized db.get calls or multiple indexed queries.

1. Fetching by a list of IDs (The "In" for IDs)

If you already have a list of document IDs (e.g., [x, y, z]), the most efficient way to fetch them is to use Promise.all with db.get. Unlike a filter() which scans the table, db.get is a point lookup and is extremely fast.

// Fetching B where B._id is in [x, y, z]
const bIds = ["id1", "id2", "id3"];
const results = await Promise.all(bIds.map((id) => ctx.db.get(id)));
// filter out nulls if some IDs might not exist
const documents = results.filter((doc) => doc !== null);

Alternatively, you can use the getAll helper from the convex-helpers library to make this more readable: const documents = await getAll(ctx.db, bIds); [Relationship Helpers].

2. Querying by a list of values (The "In" for other fields)

If you need to find records where a specific field (not the ID) matches any value in a list (e.g., SELECT * FROM messages WHERE author IN ('Alice', 'Bob')), you should:

1. Define an index on that field.
2. Run multiple queries in parallel and flatten the results.

// Example: Querying messages for multiple authors using an index
const authors = ["Alice", "Bob"];
const results = await Promise.all(
  authors.map((author) =>
    ctx.db
      .query("messages")
      .withIndex("by_author", (q) => q.eq("author", author))
      .collect()
  )
);
const allMessages = results.flat();

This approach ensures that each sub-query uses an index and only scans the relevant rows rather than the entire table [Translate SQL into Convex; IN operator equivalent].

3. Many-to-Many Relationships

For many-to-many relationships where you use a join table (e.g., postCategories), the recommended pattern is to:

1. Query the join table for all links matching your starting ID.
2. Map those links to get the target IDs.
3. Use the getAll pattern mentioned above to fetch the actual entities.

// Manual approach
const links = await ctx.db.query("postCategories")
  .withIndex("by_postId", q => q.eq("postId", postId))
  .collect();
const categoryIds = links.map(link => link.categoryId);
const categories = await Promise.all(categoryIds.map(id => ctx.db.get(id)));

You can also use the getManyVia helper from convex-helpers to simplify this into a single line: const categories = await getManyVia(db, "postCategories", "categoryId", "postId", post._id); [Many-to-many Helpers].

Important Performance Note

You mentioned that filter() or the convex-helpers filter helper scans the whole table. This is correct; in Convex, .filter() and TypeScript-based filtering after .collect() both require walking over every document in the result set. To avoid limits, you must use .withIndex() to narrow down the range of documents before applying any filters [Filtering Performance; Complex Filters].

Note: you can ask me a follow up question by @ mentioning me again :speech_balloon:

@cobalt condor Kapa.ai is still learning and improving, please let me know how I did by reacting below.