https://supabase.com/ logo
Join Discord
Powered by
How do I filter to get only rows where a column *i...
# help
n
How do I filter to get only rows where a column isn't an empty array? I've tried:
Copy code
javascript
const { data, error } = await supabase.from('users').select().neq('times', []);
But that ☝️ doesn't seem to work (
data
is always empty).
Fix (as always when using complex filters) is to just create a view that turns that condition into a 
boolean
column:
Copy code
pgsql
create view view_users as select 
users.*,
cardinality(times) > 0 as available
order by id
And then I can simply filter like:
Copy code
javascript
const { data, error } = await supabase.from('view_users').select().eq('available', true);
b
Nice workaround. That's what I love about PostgreSQL, there's always another way to get at what you want.
When you said "isn't an empty array" did you mean the array has a count of > 0?
Yeah, I don't see any way to get the length of an array in a JSONB field from the javascript client (which uses Postgrest). let me look at the postgrest operator list, hold on.
n
It's not a JSON column tho. It's a 
bigint[]
column.
The 
availability
column is 
bigint[]
.
I think my workaround is fine though, no need to spend any extra time on this.
I'm already using views for more complex outer joins for many-to-many relations.
So it's fine.
b
k, I was just curious
n
yup, i j dont want u wasting ur time 😄
b
I think if you could reference the first item in. your array you could just have look for it's existence
n
hmmm?
b
column_name[0] <> null
or
n
that messes with the typescript types tho
because 
"column_name[0]"
isn't defined on the table type
b
.ne('column_name[0]', null)
n
supabase.from<TableType>('table_name').ne('column_name[0]', null)
b
or whatever syntax you use to refer to first item in an array field
maybe column_name->0
n
TableType
doesn't have 
column_name[0]
as a key
b
not sure...
n
oh yeah that makes sense
but it's still more messy in the typescript code than j creating an 
available
boolean col
b
yeah, your solution is good and easy to follow
PreviousNext
Powered by