Indexing of When clause


#1

Can Index query cover the WHEN condition of query? For Example:
Query:

SELECT  RAW ROUND(AVG(ARRAY_SUM(ARRAY ABS(position.ball.z) FOR position IN s.position WHEN position.ball.hit = true AND s.position IS NOT MISSING END)),2)
        FROM CourtTracking p 
		UNNEST p.shot[1:2] s
		WHERE p.context.tournament.tournYear = 9999
				AND p.context.tournament.Id = 999
				AND p.context.match.tourId = 999
				AND p.valid = true
				AND p.type='point'
				AND p.serve = 1
				AND p.`set`!=0
			    AND p.context.serveClass.c='RETURNED'
			    AND s.Id = '9999'
			AND p.shot IS NOT MISSING

If yes would you, please, point to an example? I tried to add index with this query, but get errors:

CREATE INDEX y ON CourtTracking
(
context.tournament.tournYear, context.tournament.Id, 
context.match.tourId, valid, type, serve, `set`, game, context.serveClass.c,  

DISTINCT ARRAY 
	{s.Id, s.shotType, s.spinRPM,
     DISTINCT ARRAY {position.ball.hit } FOR position in s.position END
	} 
FOR s in shot END) 
WHERE (`type` = 'point' AND shot IS NOT MISSING);

Thank you very much for fast and very helpful responses. It is cool technology, but learning curve is tough :slight_smile:


#2
CREATE INDEX y ON CourtTracking ( context.tournament.tournYear, context.tournament.Id,
                                  context.match.tourId, valid, serve, `set`, context.serveClass.c,
                                  ARRAY  {s.Id, s.shotType, s.spinRPM,
                                          "position": (ARRAY position
                                                      FOR position IN s.position
                                                      WHEN position.ball.hit = true END)
                                 FOR s IN shot END)
WHERE (`type` = 'point' AND shot IS NOT MISSING);

SELECT  RAW ROUND(AVG(ARRAY_SUM(ARRAY ABS(p.ball.z) FOR p IN s.position END)),2)
                (SELECT (ARRAY {s.Id, s.shotType, s.spinRPM,
                              "position": (ARRAY position
                                           FOR position IN s.position
                                           WHEN position.ball.hit = true END)
                       FOR s IN p.shot END) AS shot
                FROM CourtTracking p
                WHERE p.context.tournament.tournYear = 9999
                        AND p.context.tournament.Id = 999
                        AND p.context.match.tourId = 999
                        AND p.valid = true
                        AND p.type='point'
                        AND p.serve = 1
                        AND p.`set`!=0
                        AND p.context.serveClass.c='RETURNED'
                       AND p.shot IS NOT MISSING
               ) AS t UNNEST t.shot[1:2] AS s
        WHERE s.Id = '9999';