Couchbase N1QL: GROUP BY CASE clause not working

Question

I am trying to execute GROUP BY CASE query on the Couchbase workbench.

The query is -

SELECT Hemisphere, COUNT(DISTINCT name) AS LandmarkCount
FROM `travel-sample` AS l
WHERE type="landmark"
GROUP BY CASE
  WHEN l.geo.lon <0 THEN "West"
  ELSE "East"
END AS Hemisphere;

This query is provided by the Couchbase documentation as an example to execute GROUP BY CASE queries on this link

I am getting the following error:

[
  {
    "code": 3000,
    "msg": "syntax error - at AS",
    "query_from_user": "SELECT Hemisphere, COUNT(DISTINCT name) AS LandmarkCount\r\nFROM `travel-sample` AS l\r\nWHERE type=\"landmark\"\r\nGROUP BY CASE\r\n  WHEN l.geo.lon <0 THEN \"West\"\r\n  ELSE \"East\"\r\nEND AS Hemisphere;"
  }
]

I am working on Community Edition 6.0.0 build 1693.

Answer 1

I think I have the solution you want:

SELECT Hemisphere,
  COUNT(DISTINCT name) AS LandmarkCount
FROM `travel-sample` AS l
LET Hemisphere = CASE
    WHEN l.geo.lon <0 THEN "West"
    ELSE "East"
  END
WHERE type="landmark"
GROUP BY Hemisphere;

The error you're getting is because in 6.0, AS isn't supported in GROUP BY (as shown in the 6.0 docs and as the error message says).

An alternative is to use LET in 6.0+ to define a variable, as I did in the above example.

But note that LET is not required either; you could also write your query without it, like this:

SELECT CASE
    WHEN l.geo.lon <0 THEN "West"
    ELSE "East"
  END AS Hemisphere,
  COUNT(DISTINCT name) AS LandmarkCount
FROM `travel-sample` AS l
WHERE type="landmark"
GROUP BY CASE
  WHEN l.geo.lon <0 THEN "West"
  ELSE "East"
END;

But I think you'll agree that the former is easier to read, understand, and change.