Appropriate index for query with unnest

SQL++
,
2

Try this. Query should cover with index. You need to use [p.lat, p.lon] as pair, include array index key as leading key

CREATE INDEX `ix1` ON `data`((all (array [(`p`.`lat`), (`p`.`lon`)] for `p` in `points` end)), `type`,`name`,`key`) WHERE (`type` in ["Lake", "Stream", "PutTakeLake", "CoastLocalArea", "SeaLocalArea"]);

SELECT d.type,
       d.name,
       d.`key`,
       lat,
       lon,
       (ACOS(SIN( RADIANS(55.55)) * SIN(RADIANS(lat)) + COS( RADIANS(55.55 )) * COS(RADIANS(lat)) * COS(RADIANS(lon) - RADIANS( 11.25))) * 6371) distance
FROM data d
UNNEST d.points p
LET  fltr = [p.lat, p.lon], lat = fltr[0], lon = fltr[1]
WHERE d.type IN ['Lake','Stream','PutTakeLake','CoastLocalArea','SeaLocalArea']
    AND lat BETWEEN 55.5 AND 55.6 AND lon BETWEEN 11.0 AND 11.5
    AND fltr >= [55.5, 11.0] AND fltr <  [SUCCESSOR(55.6), 11.5]
ORDER BY distance ASC
LIMIT 100

FYI: How to Speed Up Spatial Search in Couchbase N1QL - DZone Performance

2 Likes