Subquery for unnested array of objects

.net

#1

I am comparing performance of eventing function and .NET SDK for the same function. the eventing function.
Query used in eventing:

SELECT p.pointData.receiver.playerId AS receiver, p.pointData.scorer.playerId AS scorer, 
           s AS shot, p.serve AS serve, p.pointData.serveClass AS serveClass
    FROM CourtTracking p
    UNNEST p.pointData.shot[2:array_length(p.pointData.shot)] s
	WHERE p.pointData.tournament.year = $year 
		AND p.pointData.tournament.num = $trnm_id 
		AND p.pointData.match.matchId = $match_id 
		AND 
			CASE 
			    WHEN $set_num > 0 THEN p.matchSet = $set_num 
			    ELSE p.matchSet != $set_num END 
		AND p.isValid = true 
		AND p.type = 'point'
		AND s.playerId = $player_id
	        AND p.pointData.shot IS NOT MISSING
	        AND s.isResultIn = true;

The .NET SDK query I came up with:

   var points = (from p in _db.Query<Point>()
                where p.type == "point"
                      && p.isValid
                      && p.pointData.tournament.year == eventYear
                      && p.pointData.tournament.num == eventId
                      && p.pointData.match.matchId == matchId
                      && N1QlFunctions.IsNotMissing(p.pointData.shot)
                where setNum != 0 ? p.matchSet == setNum : p.matchSet != 0 //set stats or match stats when setNum is 0
                select new ShotDataShotPlacement
                {
                    Receiver = p.pointData.receiver.playerId,
                    Scorer = p.pointData.scorer.playerId,
                    Serve = p.serve,
                    ServeClass = p.pointData.serveClass,
                    Shot = (from s in p.pointData.shot.Skip(2)
                            where s.playerId == playerId
                            select s).ToList()
                }).ToList();

When executing SDK query, I am getting error “Subqueries In The Main From Clause Are Only Supported For Grouping And Unions” .

How can I filter data in CouchbaseToLinq for unnested items?


#2

Hi @evgeniya.bell -

Can enable logging at the DEBUG level and pull the query from the log that is generated by the Linq statement?

Thanks,

Jeff