Couchbase Data Modeling and Querying
The Data Modeling and Querying domain tests whether you can design JSON documents for Couchbase and write SQL++ queries that use indexes efficiently. Expect questions on key design, embedding versus referencing, collection layout, index selection, and query consistency.
Document and key design
Couchbase stores JSON in buckets, scopes, and collections. A document key is unique within its collection and capped at 250 bytes. Embed child data when it is bounded and read with the parent; reference it when it grows without limit or changes independently. Avoid monotonically increasing keys on write-heavy collections because they concentrate writes on one vBucket; use a hash prefix or UUID. Keep documents well under the 20 MB limit and remember that large arrays increase write amplification.
SQL++ essentials
SQL++ (formerly N1QL) queries JSON with SQL-like syntax. Use USE KEYS for direct key lookups instead of a WHERE clause on the key. UNNEST flattens arrays for joins and aggregation. Joins typically match ON obj.field = other.field. For read-your-own-writes after a mutation, set scan consistency to request_plus; not_bounded is faster but may miss the latest write.
Indexing rules
A primary index enables full scans but is rarely the right production choice. Secondary GSI indexes follow left-to-right composite order: put equality predicates first, then range and sort fields. A covering index contains every field the query filters, projects, or sorts on, so the query avoids fetching the document. Partial indexes use a WHERE clause to index only relevant documents. Run EXPLAIN to confirm an index scan rather than a primary scan.
Worked example
An orders collection stores documents such as {"orderId":"o-1001","customerId":"c-42","orderDate":"2024-05-01","status":"shipped","items":[{"sku":"A","qty":2}]}. The query filters one customer and a date range, then sorts by date:
SELECT o.orderId, o.orderDate FROM shop.orders o WHERE o.customerId = $cid AND o.orderDate >= $from ORDER BY o.orderDate DESC;
Create CREATE INDEX idx_cust_date ON shop.orders(customerId, orderDate) INCLUDE (orderId). The equality field customerId leads, the range and sort field orderDate follows, and orderId is included so the index covers the projection. EXPLAIN should show an index scan on idx_cust_date.
Traps
Leading-wildcard LIKE and functions on indexed fields prevent index use. Sorting without an index forces a sort operator. Over-embedding unbounded arrays creates huge documents and contention. Using request_plus everywhere adds latency; use it only where read-your-own-writes matters. Forgetting that index order matters leads to non-covering or unused indexes.