Kenni•3w ago

How to query items where array field contains a specific value?

I have a paginated query where I need to filter items based on whether an array field contains a specific category ID. Currently, my query only returns items where the array exactly matches [categoryId], but I need it to return items where categoryId is one of potentially many elements in the array. Here's my current query:

const baseQuery = ctx.db
    .query("items")
    .withIndex("by_owner_and_categories_and_timestamp", q =>
        q.eq("ownerId", args.ownerId)
        .eq("categories", [args.categoryId])  // Only matches exact array [categoryId]
        .lt("timestamp", Number.MAX_SAFE_INTEGER)
    )
    .order("desc");

const baseQuery = ctx.db
    .query("items")
    .withIndex("by_owner_and_categories_and_timestamp", q =>
        q.eq("ownerId", args.ownerId)
        .eq("categories", [args.categoryId])  // Only matches exact array [categoryId]
        .lt("timestamp", Number.MAX_SAFE_INTEGER)
    )
    .order("desc");

For example, if I have these items in my database:

// Item 1 - Currently NOT returned, but I want it to be
{
    ownerId: "owner1",
    categories: ["category1", "category2"],
    timestamp: 123
}

// Item 2 - Currently returned
{
    ownerId: "owner1",
    categories: ["category1"],
    timestamp: 456
}

// Item 1 - Currently NOT returned, but I want it to be
{
    ownerId: "owner1",
    categories: ["category1", "category2"],
    timestamp: 123
}

// Item 2 - Currently returned
{
    ownerId: "owner1",
    categories: ["category1"],
    timestamp: 456
}

When querying for categoryId: "category1", I want both items to be returned since they both contain "category1" in their categories array. Is there a way to achieve this kind of "contains" functionality for array fields in Convex? I need this to work with pagination too. Any suggestions would be appreciated!

3 Replies

Convex Bot•3w ago

Thanks for posting in <#1088161997662724167>. Reminder: If you have a Convex Pro account, use the Convex Dashboard to file support tickets. - Provide context: What are you trying to achieve, what is the end-user interaction, what are you seeing? (full error message, command output, etc.) - Use search.convex.dev to search Docs, Stack, and Discord all at once. - Additionally, you can post your questions in the Convex Community's <#1228095053885476985> channel to receive a response from AI. - Avoid tagging staff unless specifically instructed. Thank you!

erquhart•3w ago

You can't index on individual array values, you'll have to use filter instead. This will read all records with the owner id from the index and then drop filtered out values from the result set, so not as efficient as an index. Depending on how many records per owner id exist, this may be fine. If you need to index on the category id, you'll want to keep it in a separate field.

sshader•3w ago

Linking to some related stack posts: https://stack.convex.dev/complex-filters-in-convex, https://stack.convex.dev/databases-are-spreadsheets

Databases are Spreadsheets

I want to share my mental model of databases: - Databases are just big spreadsheets - An index is just a view of the spreadsheet sorted by one or mor...

How to query items where array field contains a specific value?

Did you find this page helpful?