Can you filter on an array

Can I create a filter on an array when querying values, like an includes?

11 Replies

ian•2y ago

Unfortunately not at the moment. You'd have to do that filtering in JS after returning them. Do you mind sharing the type of query you're trying to do, though? It would help us prioritize the kinds of filters folks need You could have another table, with one document per item in the array, and query on that table. e.g.:

// in schema.ts
postTags: defineTable({
  postId: v.id("posts"),
  tag: v.string(),
}).index("tag", ["tag"])

// in query:
const matchingPostTags = await db.query("postTags").withIndex("tag", q => q.eq("tag", "myTag")).collect();
// fetch those posts
const posts = await Promise.all(matchingPostTags.map(async (pt) => db.get(pt.postId)));

// in schema.ts
postTags: defineTable({
  postId: v.id("posts"),
  tag: v.string(),
}).index("tag", ["tag"])

// in query:
const matchingPostTags = await db.query("postTags").withIndex("tag", q => q.eq("tag", "myTag")).collect();
// fetch those posts
const posts = await Promise.all(matchingPostTags.map(async (pt) => db.get(pt.postId)));

or fetch the posts first and intersect them with the matching PostTags.

Chad MaycumberOP•2y ago

Yeah I'd love to show you, I also considered that and may do that

Chad MaycumberOP•2y ago

I'm checking these values in a participant array, on a conversations model, to check whether or not the conversation contains both participants in the conversation

ian•2y ago

The screenshot is of a v.union, not of a query. Is your question around argument validation, or database query? You'd be able to query on "participant.id" or "participant.type" just fine I believe.

Chad MaycumberOP•2y ago

Yeah sorry, maybe that wasn't clear. I'm trying to query on an array of participants within a conversation based on if a particular conversation includes two of the participants. Does that make sense?

ian•2y ago

Yup, that makes sense. “Give me all the conversations where these participants are all included” basically. I was just thrown off by seeing the validator instead of the query. If you didn’t want to make a join table, and the number of conversations is less than 1000, you could store an array of conversations that each participant is in. You’d need to update it when a participant joins or leaves a conversation but it’d be very fast. It’d just be a set intersection of the participants’ conversations. This strategy is called Denormalization. If the conversion list could get really big, however, you’d want to use the separate table. That’s the typical “normalized” relational DB approach

Chad MaycumberOP•2y ago

Thanks for the input! This is super helpful

ian•2y ago

Here’s a draft of a post on relationships in the mean time: https://stack.convex.dev/relationship-structures-let-s-talk-about-schemas

Relationship Structures: Let's Talk About Schemas

In this post we’ll look at some patterns for structuring relationships in the Convex database.

winsoroaks•17mo ago

hi! im also wondering bout this feature. i have a "fruits" column in the db that takes in an array, say ["apple", "banana"]. i am interested in doing

fruit = args.fruit // "apple"
const basket = await ctx.db
  .query("basket")
  .filter((q) => q.contains(q.field("fruits"), fruit))
  .collect()

fruit = args.fruit // "apple"
const basket = await ctx.db
  .query("basket")
  .filter((q) => q.contains(q.field("fruits"), fruit))
  .collect()

or perhaps when fruit is just "apple", i can do something like the following

fruits = args.fruits // ["apple", "banana"]
const basket = await ctx.db
  .query("basket")
  .filter((q) => q.contains(q.field("fruit"), fruits))
  .collect()

// currently, 
fruits.forEach(fruit => 
  const basket = await ctx.db
    .query("basket")
    .filter((q) => q.eq(q.field("fruit"), fruit))
    .collect()
  // append to array
)

fruits = args.fruits // ["apple", "banana"]
const basket = await ctx.db
  .query("basket")
  .filter((q) => q.contains(q.field("fruit"), fruits))
  .collect()

// currently, 
fruits.forEach(fruit => 
  const basket = await ctx.db
    .query("basket")
    .filter((q) => q.eq(q.field("fruit"), fruit))
    .collect()
  // append to array
)

or pls lemme know if im crazy by violating some design principles 😂

lee•17mo ago

if there are a small number of documents, then you can do the filtering in javascript, like

const baskets = (await ctx.db.query("baskets").collect()).filter((basket) => basket.fruits.includes(args.fruit))

const baskets = (await ctx.db.query("baskets").collect()).filter((basket) => basket.fruits.includes(args.fruit))

This is just as efficient as q.contains would be -- putting a .filter on a query is basically the same as doing it on the collected javascript array. If there are a large number of documents, or you want it to be more efficient, you could create a relationship table like in @ian's link. Adding a new table to represent the relationship between basket and fruit lets you look up containment in both directions efficiently.

lee•10mo ago

Here's a new pattern (since kapa is linking to this thread): https://stack.convex.dev/complex-filters-in-convex

Using TypeScript to Write Complex Query Filters

There’s a new Convex helper to perform generic TypeScript filters, with the same performance as built-in Convex filters, and unlimited potential.

Can you filter on an array

Did you find this page helpful?