N1QL for Analytics offers the following key advancements beyond N1QL for Query:
-
WITH: N1QL for Analytics supports the ANSI SQL WITH clause to allow the definition of inlined views or variables of primitive types to simplify complex query construction.
-
JOIN: N1QL for Analytics supports the ANSI join syntax and allows joins on any condition expressions over Analytics collections, arrays, or subqueries.
-
GROUP BY: In N1QL for Analytics, in addition to a set of aggregate functions as in standard SQL, the groups created by the
GROUP BY
clause are directly usable in nested queries and/or to obtain nested results. -
Subquery: Any valid N1QL for Analytics query can be used as a subquery.
For N1QL for Query users, the following matrix is a quick compatibility cheat sheet for N1QL for Analytics.
Feature | N1QL for Query | N1QL for Analytics Equivalent |
---|---|---|
USE KEYS |
SELECT fname, email FROM tutorial USE KEYS ["dave", "ian"]; |
SELECT fname, email FROM tutorial WHERE meta().id IN ["dave", "ian"]; |
ON KEYS |
SELECT * FROM user u |
SELECT * FROM user u, u.order_history s |
ON KEY |
SELECT * FROM user u |
SELECT * FROM user u |
NEST |
SELECT * FROM user u |
SELECT u, orders FROM users u |
LEFT OUTER NEST |
SELECT * FROM user u |
SELECT u, (SELECT VALUE o FROM u.order_history s, orders o WHERE meta(o).id = s.order_id) orders |
ARRAY |
ARRAY i FOR i IN [1, 2] END |
(SELECT VALUE i FROM [1, 2] AS i) |
ARRAY FIRST |
ARRAY FIRST arr |
arr[0] |
LIMIT l OFFSET o |
Allows OFFSET without LIMIT |
Doesn’t support OFFSET without LIMIT |
UNION, INTERSECT, and EXCEPT |
All three are supported (with ALL and DISTINCT variants) |
Only UNION ALL is supported (and necessary for query expressibility) |
<, <=, =, etc. operators |
Can compare either complex values or scalar values |
Only scalar values may be compared |
ORDER BY |
Can order by complex values or scalar values |
Can only order by scalar values |
SELECT DISTINCT |
Supported |
SELECT DISTINCT VALUE is supported when the returned values are scalars |
CREATE INDEX |
Supported |
Supported but different (e.g., typed) |
INSERT/UPSERT/DELETE |
Supported |
Unsupported (by design) |
N1QL for Analytics generalizes N1QL for Query’s syntax constructs such as USE KEYS
, ON KEYS
, ON KEY
, NEST
,
LEFT OUTER NEST
and ARRAY
and thus eliminates cases where must-be-indexed or must-use-keys
restrictions are required for certain N1QL for Query queries or expressions to be acceptable.
In addition, the general composability of N1QL for Analytics queries eliminates the need for some of N1QL for Query’s
special syntax; for example, N1QL for Analytics does not require or support the IN/WITHIN subclauses of
N1QL for Query’s existential (SOME, ANY, or EVERY) expressions.
Note that INSERT/UPSERT/DELETE are not supported at all in the Couchbase Analytics Service. Data is mutated in Couchbase Server, using the Couchbase Server SDK or N1QL for Query mutation, and the mutations will then be automatically synchronized into the Couchbase Analytics Service.