N1QL subquery in select

SQL++
2

Hi @siddharth.ruchwani,
What version are you using? What is the exact error you are getting??
Your query has two issues…

  1. The subquery in projection list must be an aggregate (as that is not in GROUP BY list). Change it to:
    ARRAY_AGG((SELECT COUNT(DISTINCT IpAddress) AS UniqueClicks FROM default WHERE Platform = s.Platform AND Click = "1")) AS UniqueClicks

Note that the subquery must be in parenthesis to be evaluated as an expression. Hence use see two parenthesis around the subquery.

  1. You are using correlated sub-query, with WHERE Platform = s.Platform. This requires USE KEYS (similar to a lookup JOIN). See https://developer.couchbase.com/documentation/server/4.5/n1ql/n1ql-language-reference/subqueries.html

-Prasad