Array queries

I have a column, dates, that is an array of dates (already sorted, guaranteed).
Essentially, I want to query my table for all rows that have the first element of dates (

dates[0]

dates[0]

) be after some variable

startTime

startTime

and the last element of dates (

dates[dates.length-1]

dates[dates.length-1]

) be less than the variable

endTime

endTime

Is there any way to do this?

I'm honestly just struggling with figuring out how to handle the array data. Here's what I have, which (obviously) does not work:

const { data: courses, error } = await client
    .from('schedules')
    .select('dates, start_date:dates->0, end_date:dates->-1')
    .gte(
        'start_date',
        startDate ? startDate.toISOString() : new Date(0).toISOString(),
    )
    .lte(
        'end_date',
        endDate ? endDate.toISOString() : new Date().toISOString(),
    )
    .limit(10);

const { data: courses, error } = await client
    .from('schedules')
    .select('dates, start_date:dates->0, end_date:dates->-1')
    .gte(
        'start_date',
        startDate ? startDate.toISOString() : new Date(0).toISOString(),
    )
    .lte(
        'end_date',
        endDate ? endDate.toISOString() : new Date().toISOString(),
    )
    .limit(10);

(the query is more pseudocode than anything LOL)
thanks yall!

Supabase•9mo ago•

5 replies

alaninnovates, the sequel

Array queries

I have a column, dates, that is an array of dates (already sorted, guaranteed).
Essentially, I want to query my table for all rows that have the first element of dates (

dates[0]

dates[0]

) be after some variable

startTime

startTime

and the last element of dates (

dates[dates.length-1]

dates[dates.length-1]

) be less than the variable

endTime

endTime

Is there any way to do this?

I'm honestly just struggling with figuring out how to handle the array data. Here's what I have, which (obviously) does not work:

const { data: courses, error } = await client
    .from('schedules')
    .select('dates, start_date:dates->0, end_date:dates->-1')
    .gte(
        'start_date',
        startDate ? startDate.toISOString() : new Date(0).toISOString(),
    )
    .lte(
        'end_date',
        endDate ? endDate.toISOString() : new Date().toISOString(),
    )
    .limit(10);

const { data: courses, error } = await client
    .from('schedules')
    .select('dates, start_date:dates->0, end_date:dates->-1')
    .gte(
        'start_date',
        startDate ? startDate.toISOString() : new Date(0).toISOString(),
    )
    .lte(
        'end_date',
        endDate ? endDate.toISOString() : new Date().toISOString(),
    )
    .limit(10);

(the query is more pseudocode than anything LOL)
thanks yall!

Array queries

Similar Threads

Array queries

Similar Threads

Similar Threads

Similar Threads