Querying with SQL++ (SELECT & joins)

This section documents the current Couchbase Server 7.6.x line as published at the Couchbase Server documentation, which is the reference these pages are written and verified against. No specific patch version is pinned. Some capabilities (Enterprise-Edition-only Analytics, auditing, encryption at rest, the Backup service and rack-zone awareness, and Capella-only App Services and Columnar) are linked, not documented in depth.

This content was generated with the assistance of AI and should be verified against the official documentation before being relied on in production, as Couchbase iterates quickly.

This section’s bibliography lists the reference material consulted while preparing these pages.

SQL++ (formerly N1QL) is Couchbase’s query language: SQL’s SELECT generalised to schemaless JSON — nested objects, arrays, and values that may be NULL, MISSING, or a different type in every document. Statements run on the Query service, a stateless tier that parses and plans each request and reads data through the Index and Data services.

The Query service and how you run a statement

A SQL++ statement targets a keyspace — the bucket / scope / collection path, such as the airline collection in the inventory scope of the travel-sample bucket. Any name containing a hyphen is wrapped in backticks, as in the examples below. You submit a statement in one of several ways:

# cbq -- the interactive query shell shipped with the server
$ cbq -engine=http://localhost:8093 -u Administrator -p password
cbq> SELECT name FROM `travel-sample`.inventory.airline LIMIT 3;

# the /query/service REST endpoint (what every client ultimately calls)
$ curl -s http://localhost:8093/query/service \
    -u Administrator:password \
    --data-urlencode 'statement=SELECT name FROM `travel-sample`.inventory.airline LIMIT 3'

The Query Workbench in the web console runs the same statements with a result grid and a visual plan. From an SDK you call cluster.query(…) for a statement that names its keyspaces in full, or scope.query(…) to resolve unqualified collection names against a scope:

// Java SDK -- named parameters, never string-concatenated input
QueryResult r = cluster.query(
    "SELECT a.name FROM `travel-sample`.inventory.airline a WHERE a.country = $country",
    queryOptions().parameters(JsonObject.create().put("country", "France")));

// scope-level: the unqualified `airline` resolves inside inventory
Scope inventory = cluster.bucket("travel-sample").scope("inventory");
inventory.query("SELECT name FROM airline WHERE country = $1",
    queryOptions().parameters(JsonArray.from("France")));

USE KEYS turns a query into a direct key-value lookup with no index scan when the document keys are already known:

SELECT * FROM `travel-sample`.inventory.airline
USE KEYS ["airline_10", "airline_10748"];

SQL generalises SQL to JSON in a few ways (https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/index.html[SQL language reference]):

Nested-path navigation. geo.alt and schedule[0].day reach into objects and arrays with dotted and subscripted paths.
MISSING vs. NULL. An absent field evaluates to MISSING, distinct from an explicit NULL, and both differ from a present value. IS MISSING, IS NULL and IS VALUED test the three states; IFMISSING() and IFMISSINGORNULL() collapse them.
Heterogeneous results. Rows in one result need not share a shape; a projection can return an object, a bare scalar (SELECT RAW), or a nested array.

For the relational starting point this builds on, see SQL Queries (SELECT).

The SELECT clause set

The clause set and its evaluation order mirror SQL (SELECT syntax):

SELECT   r.airline, COUNT(*) AS routes, AVG(r.distance) AS avg_km
FROM     `travel-sample`.inventory.route AS r
WHERE    r.stops = 0
GROUP BY r.airline
HAVING   COUNT(*) > 100
ORDER BY routes DESC
LIMIT    10
OFFSET   0;

SELECT RAW (synonym SELECT VALUE) unwraps the single projected expression, so the result is an array of bare values instead of an array of one-field objects — ideal for feeding an IN list or an SDK List<String>:

SELECT RAW a.name
FROM `travel-sample`.inventory.airline AS a
WHERE a.country = "France";
-- ["Air France", "Aigle Azur", ...]

Avoiding large offsets

LIMIT/OFFSET still requires the Query service to scan and discard the first OFFSET rows before it can return the requested page, so deep pagination gets progressively slower. Paginate with a seek query on a unique indexed field instead, carrying the last seen key forward — no OFFSET at all:

-- page 1
SELECT r.id, r.airline, r.distance
FROM `travel-sample`.inventory.route AS r
ORDER BY r.id
LIMIT 20;

-- next page: start after the last id seen, no OFFSET
SELECT r.id, r.airline, r.distance
FROM `travel-sample`.inventory.route AS r
WHERE r.id > $lastKey
ORDER BY r.id
LIMIT 20;

See Pagination: Offset vs. Keyset for why this matters and how the same pattern applies across SQL, MongoDB, Solr, Elasticsearch, GraphQL and Spring Data.

Nested paths and array subscripts appear anywhere an expression is allowed:

SELECT h.name, h.geo.lat, h.geo.lon, h.reviews[0].ratings.Overall AS first_rating
FROM `travel-sample`.inventory.hotel AS h
WHERE h.geo.alt IS VALUED;

UNNEST flattens an array-valued field into one row per element, joining each element back to its parent row:

SELECT r.sourceairport, s.day, s.flight
FROM `travel-sample`.inventory.route AS r
UNNEST r.schedule AS s
WHERE r.airline = "AF"
LIMIT 5;

NEST is the inverse: it gathers matching documents from another keyspace into an array field on each left-hand row (NEST clause):

SELECT a.name, ARRAY_LENGTH(routes) AS route_count
FROM `travel-sample`.inventory.airline AS a
NEST `travel-sample`.inventory.route AS routes
  ON routes.airlineid = META(a).id
LIMIT 5;

Joins

SQL++ offers three join styles (JOIN clause).

Lookup join — ON KEYS. The right-hand keyspace is reached by document key computed from the left row. Fast, and no index on the right side is needed, but the join key must be a document key:

SELECT r.sourceairport, r.destinationairport, a.name
FROM `travel-sample`.inventory.route AS r
JOIN `travel-sample`.inventory.airline AS a
  ON KEYS r.airlineid
LIMIT 5;

ANSI join. Any boolean predicate joins the two sides and the optimizer may use an index on either. LEFT [OUTER] JOIN keeps unmatched left rows with MISSING right-hand fields:

SELECT r.sourceairport, a.name AS airline, ap.airportname
FROM `travel-sample`.inventory.route AS r
JOIN `travel-sample`.inventory.airline AS a
  ON r.airlineid = META(a).id
LEFT JOIN `travel-sample`.inventory.airport AS ap
  ON r.sourceairport = ap.faa
WHERE a.country = "United States"
LIMIT 10;

Index join. The reverse direction of a lookup join — follow a key held on the right side back to the left — made efficient by an index on the right-hand join field:

CREATE INDEX idx_route_airlineid
  ON `travel-sample`.inventory.route(airlineid);

SELECT a.name, r.destinationairport
FROM `travel-sample`.inventory.airline AS a
JOIN `travel-sample`.inventory.route AS r
  ON META(a).id = r.airlineid
WHERE a.icao = "SWA";

A join can target the same keyspace (a self-join for hierarchy or pairing) or another one, in the same scope or across scopes. For the relational treatment of joins see SQL Relations; for the pipeline-stage equivalent see MongoDB Aggregation ($lookup).

WITH, subqueries and collection operators

WITH binds a named result once and reuses it, the same as a SQL common table expression (WITH clause):

WITH big_countries AS (
  SELECT RAW country
  FROM `travel-sample`.inventory.airport
  GROUP BY country
  HAVING COUNT(*) > 200
)
SELECT a.name, a.country
FROM `travel-sample`.inventory.airline AS a
WHERE a.country IN big_countries;

A recursive CTE (Couchbase Server 7.6) walks a hierarchy: a seed term unioned with a recursive term that references the CTE name.

WITH RECURSIVE chain AS (
  SELECT e.id, e.name, e.managerId, 1 AS depth
  FROM main.hr.employee AS e
  WHERE e.id = "e-1"
  UNION
  SELECT e.id, e.name, e.managerId, chain.depth + 1
  FROM chain
  JOIN main.hr.employee AS e ON e.managerId = chain.id
)
-- an OPTIONS { "levels": 10 } clause on the CTE caps recursion depth
SELECT name, depth FROM chain ORDER BY depth;

Subqueries appear in FROM (a derived keyspace, which needs an explicit alias), in WHERE, and in the projection. LET introduces a named expression usable in SELECT and WHERE; LETTING does the same after GROUP BY for expressions over aggregates:

SELECT p.name, band
FROM main.catalog.product AS p
LET band = CASE WHEN p.price > 200 THEN "high" ELSE "standard" END
WHERE band = "high";

SELECT r.country, num_routes, num_routes >= 10000 AS major
FROM `travel-sample`.inventory.route AS r
GROUP BY r.country
LETTING num_routes = COUNT(*)
HAVING num_routes > 1000
ORDER BY num_routes DESC;

Collection operators test or transform arrays inline without UNNEST:

-- ANY / EVERY: existential and universal quantification over an array
SELECT META().id
FROM `travel-sample`.inventory.route AS r
WHERE ANY s IN r.schedule SATISFIES s.day = 1 END;

-- ARRAY: a comprehension that projects a new array
SELECT r.destinationairport,
       ARRAY s.flight FOR s IN r.schedule WHEN s.day = 1 END AS monday_flights
FROM `travel-sample`.inventory.route AS r
LIMIT 5;

-- FIRST: the first matching element
SELECT FIRST s.flight FOR s IN r.schedule WHEN s.utc > "18:00" END AS evening
FROM `travel-sample`.inventory.route AS r
LIMIT 5;

How a SQL++ query executes

The Query service turns each statement into a pipeline of operators: parse and semantic check, optimize (pick a Global Secondary Index per keyspace and a join order), scan the index for candidate keys, fetch the full documents from the Data service, then apply joins, residual filters, grouping, ordering and the final projection.

flowchart TD A[SQL++ statement] --> B[Parse and semantic check] B --> C[Optimize: choose indexes and join order] C --> D[Index scan: candidate keys from a GSI] D --> E[Fetch: full documents from the Data service] E --> F[Join / UNNEST / NEST] F --> G[Filter: residual WHERE predicates] G --> H[GROUP BY / aggregate / HAVING] H --> I[ORDER BY / LIMIT / OFFSET] I --> J[Project the SELECT list] J --> K[Result JSON]

A statement with no usable secondary index falls back to a primary scan of every key in the keyspace, so covering the WHERE and JOIN predicates with indexes is what keeps a query fast — see Indexes & views. Continue with SQL++ data modification, functions & transactions.