Core Concepts

N1QL: SQL for JSON Documents

Master N1QL queries — Couchbase's SQL dialect for querying JSON documents including UNNEST, subqueries, and index optimization.

N1QL Fundamentals

N1QL brings the expressiveness of SQL to JSON document databases. It supports SELECT, INSERT, UPDATE, DELETE, MERGE, and most SQL operators.

Basic SELECT

sql

-- Get all users
SELECT * FROM myapp WHERE type = 'user';

-- Select specific fields
SELECT name, email, tier FROM myapp WHERE type = 'user';

-- With alias (META().id gives the document key)
SELECT META().id AS docId, name, email
FROM myapp
WHERE type = 'user' AND tier = 'premium';

Querying Nested JSON

N1QL uses dot notation to access nested fields:

sql

-- Document: { "address": { "city": "Paris", "country": "France" } }
SELECT name, address.city, address.country
FROM myapp
WHERE type = 'user' AND address.country = 'France';

-- Deep nesting
SELECT profile.social.twitter
FROM myapp
WHERE type = 'user' AND profile.social.twitter IS NOT MISSING;

UNNEST: Unwinding Arrays

sql

-- Document: { "tags": ["redis", "database", "caching"] }
-- Get one row per tag
SELECT u.name, t AS tag
FROM myapp u
UNNEST u.tags AS t
WHERE u.type = 'post'
ORDER BY u.name;

-- Filter by array element
SELECT * FROM myapp
WHERE type = 'post' AND 'redis' IN tags;

JOINs

N1QL supports JOINs between documents using document keys:

sql

-- ANSI JOIN (Couchbase 5.5+)
SELECT u.name, o.total, o.status
FROM myapp u
JOIN myapp o ON META(o).id = 'order:' || u.userId
WHERE u.type = 'user' AND o.type = 'order' AND o.status = 'shipped';

-- LOOKUP JOIN (key-based, very fast)
SELECT u.name, o.total
FROM myapp o
JOIN myapp u USE KEYS ('user:' || o.userId)
WHERE o.type = 'order';

Aggregations

sql

SELECT
  tier,
  COUNT(*) AS user_count,
  AVG(lifetime_value) AS avg_ltv,
  MAX(lifetime_value) AS max_ltv
FROM myapp
WHERE type = 'user'
GROUP BY tier
HAVING COUNT(*) > 100
ORDER BY avg_ltv DESC;

DML: INSERT, UPDATE, DELETE

sql

-- INSERT
INSERT INTO myapp (KEY, VALUE)
VALUES ('user:bob', {
  "type": "user",
  "name": "Bob",
  "email": "bob@example.com",
  "tier": "free"
});

-- UPDATE
UPDATE myapp
SET tier = 'premium', upgradedAt = NOW_STR()
WHERE META().id = 'user:bob';

-- Conditional UPDATE
UPDATE myapp
SET stock = stock - 1
WHERE META().id = 'product:sku-123' AND stock > 0
RETURNING name, stock;

-- DELETE
DELETE FROM myapp WHERE META().id = 'user:bob';

Indexes in Couchbase

Without indexes, N1QL performs a full collection scan. For production queries, always create indexes.

sql

-- Primary index (enables full scans, only for development)
CREATE PRIMARY INDEX ON myapp;

-- Secondary index on type field (recommended pattern)
CREATE INDEX idx_user_type ON myapp(type)
WHERE type = 'user';

-- Compound index
CREATE INDEX idx_user_tier ON myapp(tier, lifetime_value)
WHERE type = 'user';

-- Array index
CREATE INDEX idx_post_tags ON myapp(DISTINCT ARRAY t FOR t IN tags END)
WHERE type = 'post';

Use EXPLAIN to check if your query uses an index:

sql

EXPLAIN SELECT * FROM myapp WHERE type = 'user' AND tier = 'premium';

Example

sql

-- Practical: e-commerce order analytics
SELECT
  DATE_PART_STR(o.createdAt, 'month') AS month,
  p.category,
  COUNT(*) AS order_count,
  SUM(oi.price * oi.quantity) AS revenue,
  AVG(o.total) AS avg_order_value
FROM orders o
UNNEST o.items AS oi
JOIN products p USE KEYS ('product:' || oi.productId)
WHERE o.status = 'completed'
  AND o.createdAt >= '2025-01-01'
GROUP BY DATE_PART_STR(o.createdAt, 'month'), p.category
ORDER BY month, revenue DESC;

Want to run this code interactively?

Try in Compiler