query over a nested array?
let's say I have a table called
projects {
users: v.array(v.string())
}
is there a way I can query over projects using a userId and only return the projects that container the userId inside that users array? should I just do a .collect() and then filter in ts?
15 Replies
sorry I had the schema wrong
there is not, yep just collect and then filter. If this starts to get big you want to use a "memberships" join table that contains every user/project pair. At big scale this is often the pattern you want, but with Convex it's really nice that you don't need to move to this until you know it's working.
it's an array of user ids
k, yeah I figured having a separate table was the right solution
"right solution" in quotes, the users array is real convenient!
not disagreeing, just saying I like the array approach and personally I tend to stick with it until it's clear it's not going to work because I have thousands of projects
so like
if I just do a .collect()
is there a point where I won't get all the entries back?
like is there a hard cap on returned entries?
what if I have 10,000 projects or something
it'll fail loudly, just erroring
is that based on mb returned from a query?
or number of entries?
we plan on adding warnings for these to make them less surprising, there are limits on both of these
yeah I saw a 1mb limit of the document size
so a project with 1000000 userIds might be too much
but I'm trying to find where the limit is for how much data returned from a db.query().collect()
I believe it's ~8000 documents read in a query or 16MB data read
any clue if that's in the docs? I'd recommend putting it somewhere in this limits page. I've been screwed over so many times due to AWS limits I didn't think about before building out a system
totally, we're working on this — and also on warnings so that even if you didn't read them you won't be surprised a month later
oh nice yeah a warning would be slick
thanks for the help @ballingt all of you are super helpful in answering questions
There's also a limit (8192 items) to the size of the array! https://docs.convex.dev/database/types#convex-values
thanks, that's good to know