Nest and Unnest Documents
- Capella Operational
- how-to
How to nest and unnest arrays of embedded objects.
Introduction
Couchbase Capella is a document database. Data is stored as JSON documents in keyspaces, rather than as rows in tables. This means that documents can contain arrays of embedded subdocuments. SQL++ provides syntax which enables you to nest (create) or unnest (flatten) arrays of embedded documents in a query.
If you want to try out the examples in this section, follow the instructions given in Create an Account and Deploy Your Free Tier Operational Cluster to create a free account, deploy a cluster, and load a sample dataset. Read the following for further information about the tools available for editing and executing queries:
Nesting Data
Nesting is like creating a join from a document to documents from another data source. However, in the resultset, the nested documents are embedded in an array within the parent document.
SQL++ offers several types of nest syntax. This guide focuses on ANSI nest, which is the recommended nest syntax. It enables you to nest objects from one data source within objects from another, using arbitrary fields.
To create a nest:
-
Use the FROM clause to specify the data source on the left-hand side of the nest. This may be a keyspace identifier, a subquery, or a subquery.
-
Use the NEST clause to specify the data source on the right-hand side of the nest. This must be a keyspace reference.
-
Use the ON keyword to specify the nest predicate. This is a condition that must be met in order to nest an object on the right-hand side within an object on the left-hand side.
To use a document key in the nest predicate, use the META() function to return the id field from the document metadata.
|
For example, the following query selects a route and the associated airline.
Set the query context to the inventory scope in the travel sample dataset.
For more information, see Setting the Query Context.
SELECT *
FROM route r (1)
NEST airline a (2)
ON r.airlineid = META(a).id (3)
LIMIT 1;| 1 | The route keyspace is the left-hand side of the nest. |
| 2 | The airline keyspace is the right-hand side of the nest. |
| 3 | The airlineid field on the left-hand side must be equal to the document key on the right-hand side. |
| Before running a query containing a nest, make sure all the required indexes exist. To check which indexes may be required, use the Index Advisor. |
For more information and examples, refer to ANSI NEST Clause.
Nest Types
ANSI nests support two types of nest: inner nests and left outer nests. (There is no provision for right outer nests, because objects from the right-hand side cannot be nested within an object that doesn’t exist.)
Inner Nests
The default nest type is an inner nest. An inner nest returns nested objects only where a source object from the left-hand side of the nest matches a source object from the right-hand side of the nest.
To create an inner nest, omit the nest type, or optionally include the INNER keyword before the NEST clause.
For example, the following query lists only airports in Toulouse which have routes starting from them, and nests details of the routes.
Set the query context to the inventory scope in the travel sample dataset.
For more information, see Setting the Query Context.
SELECT *
FROM airport a
INNER NEST route r
ON a.faa = r.sourceairport
WHERE a.city = "Toulouse"
ORDER BY a.airportname;[
{
"a": {
"airportname": "Blagnac",
"city": "Toulouse",
"country": "France",
"faa": "TLS",
"geo": {
"alt": 499,
"lat": 43.629075,
"lon": 1.363819
},
"icao": "LFBO",
"id": 1273,
"type": "airport",
"tz": "Europe/Paris"
},
"r": [
{
"airline": "AH",
"airlineid": "airline_794",
"destinationairport": "ALG",
"distance": 787.299015326995,
"equipment": "736",
"id": 10265,
// ...
},
{
"airline": "AH",
"airlineid": "airline_794",
"destinationairport": "ORN",
"distance": 906.1483088609814,
"equipment": "736",
"id": 10266,
// ...
]
}
]Left Outer Nests
A left outer nest returns nested objects using all the source objects from the left-hand side of the nest, but only including source objects from the right-hand side of the nest if they match.
To create a left outer nest, include the LEFT or LEFT OUTER keywords before the NEST clause.
For example, the following query lists all airports in Toulouse, and nests details of any routes that start from each airport.
Set the query context to the inventory scope in the travel sample dataset.
For more information, see Setting the Query Context.
SELECT *
FROM airport a
LEFT NEST route r
ON a.faa = r.sourceairport
WHERE a.city = "Toulouse"
ORDER BY a.airportname;[
{
"a": {
"airportname": "Blagnac",
"city": "Toulouse",
"country": "France",
"faa": "TLS",
"geo": {
"alt": 499,
"lat": 43.629075,
"lon": 1.363819
},
"icao": "LFBO",
"id": 1273,
"type": "airport",
"tz": "Europe/Paris"
},
"r": [
{
"airline": "AH",
"airlineid": "airline_794",
"destinationairport": "ALG",
"distance": 787.299015326995,
"equipment": "736",
"id": 10265,
// ...
}
]
},
{
"a": {
"airportname": "Francazal",
"city": "Toulouse",
"country": "France",
"faa": null,
"geo": {
"alt": 535,
"lat": 43.545555,
"lon": 1.3675
},
"icao": "LFBF",
"id": 1266,
"type": "airport",
"tz": "Europe/Paris"
},
"r": [] (1)
},
{
"a": {
"airportname": "Lasbordes",
"city": "Toulouse",
"country": "France",
"faa": null,
"geo": {
"alt": 459,
"lat": 43.586113,
"lon": 1.499167
},
"icao": "LFCL",
"id": 1286,
"type": "airport",
"tz": "Europe/Paris"
},
"r": []
}
]| 1 | If there is no corresponding data object on the right-hand side of the nest, fields from the right-hand side are missing or null. |
Unnesting Data
Unnesting data is the opposite of nesting. Unnesting is like creating a join from a parent document to subdocuments in an array within that document. In the resultset, the subdocuments are flattened and joined to the parent document.
To unnest subdocuments from an array:
-
Use the FROM clause to specify the parent data source on the left-hand side of the unnest.
-
Use the UNNEST clause to specify the nested data on the right-hand side of the unnest.
For example, the following query unnests the schedule data from within the route document to get details of flights on Monday (day 1).
Set the query context to the inventory scope in the travel sample dataset.
For more information, see Setting the Query Context.
SELECT route.sourceairport, route.destinationairport, sched.flight, sched.utc
FROM route
UNNEST schedule sched
WHERE sched.day = 1
LIMIT 3;[
{
"destinationairport": "MRS",
"flight": "AF356",
"sourceairport": "TLV",
"utc": "12:40:00"
},
{
"destinationairport": "MRS",
"flight": "AF480",
"sourceairport": "TLV",
"utc": "08:58:00"
},
{
"destinationairport": "MRS",
"flight": "AF250",
"sourceairport": "TLV",
"utc": "12:59:00"
}
]For more information and examples, refer to UNNEST Clause.
Chaining Nests and Unnests
To chain joins, nests, and unnests, use the right-hand side of one JOIN, NEST, or UNNEST clause as the left-hand side of the next.
For example, the following query joins routes to airports by destination airport, and then nests landmarks in the same city as each airport.
Set the query context to the inventory scope in the travel sample dataset.
For more information, see Setting the Query Context.
SELECT *
FROM route AS rte (1)
JOIN airport AS apt (2)
ON rte.destinationairport = apt.faa
NEST landmark AS lmk (3)
ON apt.city = lmk.city
LIMIT 5;| 1 | The route keyspace is on the left-hand side of the join. |
| 2 | The airport keyspace is on the right-hand side of the join and the left-hand side of the nest. |
| 3 | The landmark keyspace is on the right-hand side of the nest. |
For more information, refer to Left-Hand Side for NEST clauses, or Left-Hand Side for UNNEST clauses.