Can I push down pagination to an array index

SQL++
1

{Enterprise 6.5}

I’ve tried out different approaches but none have worked. I’m hoping there is a way to accomplish this.

Let’s say I have a doc like this:
{event_type: x, participants:[{p_type: x, p_id: y}, … ], createDate: 123, …}

I want to do the following query, ordering by createDate desc, but pushing pagination down to the indexer:

Select meta().id from bucket where event_type=‘live-sport’ and any p in participants satisfies p.id = $personId end order by createDate desc
offset x limit y.

I can push down the id equality by writing

create index … on bucket((distinct array(x.id) for x in participants end)) where event_type = ‘live-sport’

but adding (…, createDate desc) does not result in relying on the sort order and pushing down the offset/limit to the indexer. I assume it’s because of how the index sorts and stores the array elements.

Is there a way to do this?

2

Any word on this? This would be a huge win for us if doable and if not, we’d likely to know definitively (@vsr1)?

3 
create index ix24 on default((ALL array (x.id) for x in participants end), createDate DESC) where event_type = "live-sport";

 EXPLAIN SELECT DISTINCT META(d).id 
FROM default AS d 
UNNEST d.participants AS du 
WHERE d.event_type = "live-sport" AND du.id = $personId 
 ORDER BY d.createDate DESC;
1 Like