What does UNNEST do in a Couchbase SQL++ query over a document's array field?
answer
- Turns nested elements into result rows
- It behaves like a join with itself
- Empty arrays make the parent disappear
- There is a LEFT form for that
- Its mirror image builds arrays instead
basics
~10 sUNNEST flattens an array embedded in a document into one result row per element, joined back to its parent document, so array elements can be filtered, grouped and projected exactly like ordinary rows.
solid answer
~40 s`UNNEST` is a join between a document and an array inside that same document. Each element of the array becomes its own row, paired with the fields of the parent, so `WHERE`, `GROUP BY` and projection can address the element's own attributes. A document whose array is missing or empty contributes no rows at all — use `LEFT UNNEST` if you need the parent row preserved with a MISSING element. `NEST` is the mirror image: it joins another keyspace and collects the matching documents into an array attribute of the result instead of flattening one. For UNNEST-heavy queries to run well, index the array with an array index, `CREATE INDEX ... ON coll(DISTINCT ARRAY v.day FOR v IN schedule END)`, otherwise the Query service has to fetch and expand every candidate document.
code
sql · 4 linesSELECT r.airline, s.day, s.utc
FROM `travel-sample`.inventory.route AS r
UNNEST r.schedule AS s
WHERE s.day = 2;go deeper
Recall that UNNEST turns each element of an embedded array into its own result row, and that you alias the element to project its fields. Being able to read such a query is enough at this level.
Explain the inner-join semantics — missing or empty arrays drop the document — and when LEFT UNNEST is required. Be ready to contrast UNNEST with NEST and to describe chained UNNESTs multiplying rows.
Show that you know an UNNEST predicate needs an array index to be served by the Index service, and that aggregates over multiplied rows must be structured to avoid double counting parent values.
Own the modelling consequence: how deeply nested arrays you accept in a document shape determines how much of your query load becomes expansion work, and when a separate collection beats an embedded array.
## The problem UNNEST solves Documents routinely embed arrays: a route holds a `schedule` array, an order holds a `lines` array, a user holds a `roles` array. SQL-style filtering and grouping work on rows, not on nested arrays, so SQL++ needs a way to turn array elements into rows. That is `UNNEST`. ## What UNNEST actually does `UNNEST` performs a join of a document with one of its own embedded arrays. For each document, it emits one row per element of the named array; each row carries the element bound to an alias plus all the fields of the parent document. Given a document ```sql { "airline": "AA", "schedule": [ {"day":1,"utc":"08:00"}, {"day":2,"utc":"11:30"} ] } ``` the query ```sql SELECT r.airline, s.day, s.utc FROM `travel-sample`.inventory.route AS r UNNEST r.schedule AS s WHERE s.day = 2; ``` produces one row, with `airline` from the parent and `day`/`utc` from the matched element. Without the `UNNEST`, `s` would not exist and the only way to test the array would be an `ANY ... SATISFIES` predicate, which tells you *whether* a matching element exists but cannot project the element itself. ## Inner versus LEFT semantics `UNNEST` is an inner operation. A document whose array attribute is missing, is not an array, or is an empty array contributes **zero rows** to the result. That is the single most common surprise: a count over an unnested query is a count of elements, not of documents, and documents with empty arrays silently vanish. `LEFT UNNEST` keeps the parent row, binding the element alias to MISSING when there is nothing to expand. Use it when the report must list every document, including those with no array entries. ## Nested and repeated UNNEST UNNEST clauses chain. If each order line itself holds an array of `adjustments`, you can write `UNNEST o.lines AS l UNNEST l.adjustments AS a` and get one row per adjustment, with the order and the line still in scope. Multiplicity compounds: a document with 10 lines each holding 3 adjustments emits 30 rows. Aggregations over such a shape need care — `SUM(o.total)` after two UNNESTs adds the order total once per emitted row, not once per order. Aggregate over the element being expanded, or aggregate first in a subquery. ## NEST is the inverse `NEST` joins a second keyspace and gathers the matching right-hand documents into an array attribute of each result object rather than producing one row per match: ```sql SELECT a.name, routes FROM `travel-sample`.inventory.airline AS a NEST `travel-sample`.inventory.route AS routes ON routes.airlineid = META(a).id; ``` Each result carries the airline plus a `routes` array. `LEFT NEST` keeps airlines with no routes, giving them an empty or missing array. So: UNNEST flattens an array that is already inside the document; NEST builds an array out of documents that were not. Interviewers like this pair because it shows whether you think in terms of the document shape you want out, rather than mechanically translating a relational join. ## Making it fast UNNEST does not by itself give the Query service an access path. A predicate on an element attribute can be served by an **array index**: ```sql CREATE INDEX idx_sched_day ON `travel-sample`.inventory.route(DISTINCT ARRAY s.day FOR s IN schedule END); ``` This indexes each element's `day` value, so `WHERE s.day = 2` after an `UNNEST` (and an equivalent `ANY s IN schedule SATISFIES s.day = 2 END`) can be answered by an index scan instead of expanding every document. `DISTINCT ARRAY` deduplicates repeated values within one document; `ALL ARRAY` keeps every occurrence. Without such an index the query either scans a primary index or scans a broader secondary index and expands arrays in the Query service — fine for thousands of documents, ruinous for millions. ## What weak answers look like Saying UNNEST "returns the array" misses that it produces rows. Assuming a document with an empty array still appears misses the inner-join semantics. Assuming the array is automatically indexed because a field of the same name is indexed misses that a plain secondary index on `schedule` indexes the array value itself, not its elements.
- What happens to a document whose UNNEST target array is empty or missing in Couchbase SQL++?It contributes no rows at all — plain `UNNEST` has inner-join semantics. Use `LEFT UNNEST` to keep the parent row, with the element alias bound to MISSING. This is why a `COUNT(*)` over an unnested query counts elements rather than documents, and why documents with empty arrays silently drop out of reports.
- How is NEST different from UNNEST in Couchbase SQL++?`UNNEST` flattens an array that already lives inside a document into one row per element. `NEST` goes the other way: it joins another keyspace and collects each set of matching documents into an array attribute of the result, so you get one row per left-hand document with a nested array. `LEFT NEST` preserves left documents that matched nothing.
- Which index lets a predicate on an unnested array element use an index scan in Couchbase?An array index, created with the `DISTINCT ARRAY expr FOR v IN arrayField END` form of `CREATE INDEX`. It indexes each element's value rather than the array as a whole, so a predicate on the element attribute is answerable by the Index service. `ALL ARRAY` is the variant that keeps duplicate values within one document.
UNNEST is unpacking a suitcase onto the bed so each item can be inspected on its own; NEST is packing loose items back into a suitcase.
saying these in an interview costs you the question
- Thinks UNNEST just returns the array as a value
- Expects documents with empty arrays to still appear
- Assumes an index on the array field indexes its elements
- Sums parent fields after UNNEST and double-counts
- Confuses NEST with UNNEST's direction