February 16, 2025
+ 12
Examples on this Page

To use the examples on this page, you must set the query context to the inventory scope in the travel sample dataset. For more information, see Query Context.

Please note that some of the examples below will alter the data in your sample buckets. To restore your sample data, remove and reinstall the travel-sample bucket. Refer to Sample Buckets for details.

Example 1

Here is an example of aggregating using correlated subquery expression in projection. This query finds the top 3 overall rated hotels. The subquery in the projection finds the average overall rating across all rating of the given hotel document t. Note that the subquery uses nested paths in the FROM clause.

sql++
SELECT name, (SELECT raw avg(s.ratings.Overall) FROM t.reviews as s)[0] AS overall_avg_rating FROM hotel AS t ORDER BY overall_avg_rating DESC LIMIT 3;
Results
json
[ { "name": "Culloden House Hotel", }, { "name": "The Bulls Head", "overall_avg_rating": 5 }, { "name": "La Pradella", "overall_avg_rating": 5 } ]

Example 2

The following query shows how to use correlated subquery expression in projection with predicates and sorted results on specific nested fields. This query finds flight schedules starting after 10PM from SFO airport.

sql++
SELECT id, sourceairport, destinationairport, (SELECT s.* FROM route.schedule s WHERE s.utc > "22:00:00" ORDER BY s.utc) after_10pm FROM route WHERE sourceairport = "SFO" LIMIT 2;
Results
json
[ { "after_10pm": [ { "day": 2, "flight": "AI712", "utc": "23:44:00" } ], "destinationairport": "HKG", "id": 10624, "sourceairport": "SFO" }, { "after_10pm": [ { "day": 6, "flight": "AI588", "utc": "22:14:00" }, { "day": 5, "flight": "AI886", "utc": "22:53:00" }, { "day": 1, "flight": "AI678", "utc": "23:50:00" } ], "destinationairport": "ICN", "id": 10625, "sourceairport": "SFO" } ]

Example 3

The following query uses correlated subquery expression as predicate in the WHERE clause, and finds the source airports from which more than 4 flights are scheduled on day 1.

sql++
SELECT airline, sourceairport FROM route WHERE (SELECT raw count(*) FROM route.schedule as s WHERE s.day = 1)[0] > 4 LIMIT 3;
Results
json
[ { "airline": "AF", "sourceairport": "TLV" }, { "airline": "AF", "sourceairport": "TPE" }, { "airline": "AF", "sourceairport": "TRI" } ]

Example 4

The following query finds the top 3 hotels and number of reviewers, which have Overall rating at least 4, and rated by minimum 6 people. Note that it is a correlated subquery expression in the LET clause, with nested paths (that is t.ratings) in the subquery FROM path.

sql++
SELECT name, cnt_reviewers FROM hotel AS t LET cnt_reviewers = (SELECT raw count(*) FROM t.reviews AS s WHERE s.ratings.Overall >= 4)[0] WHERE cnt_reviewers >= 6 ORDER BY cnt_reviewers DESC LIMIT 3;
Results
json
[ { "cnt_reviewers": 9, "name": "Negresco" }, { "cnt_reviewers": 9, "name": "Cadogan Hotel" }, { "cnt_reviewers": 9, "name": "Holiday Inn London Kensington Forum" } ]

Example 5

This example shows usage of subquery expressions in MERGE statement. This query uses constant expression as the MERGE source data, and updates the vacancy to false for matching documents. For the sake of demonstrating update operation, this query saves the current value of vacancy to a new attribute old_vacancy.

sql++
MERGE INTO hotel t USING [{"id":"21728"},{"id":"21730"}] source ON KEY "hotel_"|| source.id WHEN MATCHED THEN UPDATE SET t.old_vacancy = t.vacancy, t.vacancy = false RETURNING meta(t).id, t.old_vacancy, t.vacancy;
Results
json
[ { "id": "hotel_21728", "old_vacancy": false, "vacancy": false }, { "id": "hotel_21730", "old_vacancy": true, "vacancy": false } ]

Example 6

Here is an example of LET variable in the FROM clause.

sql++
SELECT count(*) FROM airport t LET x = t.geo WHERE (SELECT RAW y.alt FROM x y)[0] > 6000;
Results
json
[ { "$1": 38 } ]

Example 7

An example of using same keyspace name in subquery FROM clause that is used in the parent query.

In this example, the subquery is not correlated with the parent query, so it returns all of the airports in the airport collection.

sql++
SELECT array_length((SELECT RAW t1.geo.alt FROM airport t1)) FROM airport LIMIT 4;
Results
json
[ { "$1": 1968 }, { "$1": 1968 }, { "$1": 1968 }, { "$1": 1968 } ]

Example 8

An example of using alias name in the subquery FROM clause.

In this example, the subquery is correlated with the parent query, so it only returns the single airport found by the parent query.

sql++
SELECT array_length((SELECT RAW t1.geo.alt FROM t t1)) FROM airport t;
Results
json
[ { "$1": 1 }, ... ]

Example 9

A non-correlated subquery with UPDATE.

sql++
UPDATE airport t1 SET airportname_dup = "high_altitude_" || airportname WHERE t1.geo.alt IN (SELECT RAW t2.geo.alt FROM airport t2 WHERE t2.geo.alt > 6000) RETURNING t1.airportname_dup;
Results
json
[ { "airportname_dup": "high_altitude_Grants Milan Muni" }, { "airportname_dup": "high_altitude_Durango La Plata Co" }, { "airportname_dup": "high_altitude_Black Rock" }, ... { "airportname_dup": "high_altitude_Colorado Springs East" } ]

Example 10

A correlated subquery with UPDATE with nested paths.

sql++
UPDATE airport t1 SET airportname_dup = "high_altitude_2 " || airportname WHERE (SELECT RAW geo.alt FROM t1.geo WHERE geo.alt > 6000)[0] = t1.geo.alt RETURNING t1.airportname_dup;
Results
json
[ { "airportname_dup": "high_altitude_2 Grants Milan Muni" }, { "airportname_dup": "high_altitude_2 Durango La Plata Co" }, { "airportname_dup": "high_altitude_2 Black Rock" }, ... { "airportname_dup": "high_altitude_2 Colorado Springs East" } ]

Example 11

The following correlated subquery with UPDATE. In this example, the subquery filters for 5 rated reviews and sorts them by reviewer name. The result of the subquery is assigned to a new field reviews_5star in the hotel document.

If you are warned that the query contains no WHERE clause or USE KEYS, choose Continue.

sql++
UPDATE hotel t1 SET reviews_5star = (SELECT raw t2 FROM t1.reviews t2 WHERE t2.ratings.Overall = 5 ORDER BY t2.author) LIMIT 1 RETURNING t1.reviews[*].author;
Results
json
[ { "author": [ "Ozella Sipes", "Barton Marks" ] } ]

Example 12

A non-correlated subquery with INSERT.

sql++
INSERT INTO hotel (KEY UUID()) SELECT x.name, x.city, "landmark_hotels" AS type FROM hotel x WHERE x.city WITHIN ( SELECT DISTINCT t.city FROM landmark t) LIMIT 4 RETURNING *;
Results
json
[ { "hotel": { "city": "Aberdeenshire", "name": "Castle Hotel", "type": "landmark_hotels" } }, { "hotel": { "city": "Aberdeenshire", "name": "Two Bears Cottage", "type": "landmark_hotels" } }, { "hotel": { "city": "Agoura Hills", "name": "Malibu Creek Campground", "type": "landmark_hotels" } }, { "hotel": { "city": "Altrincham", "name": "Cresta Court Hotel", "type": "landmark_hotels" } } ]

Example 13

A correlated subquery with DELETE all hotel records which got the minimum overall rating by more than 4 reviewers.

sql++
DELETE FROM hotel t WHERE (SELECT RAW count(*) FROM t.reviews t2 WHERE t2.ratings.Overall = 1 )[0] > 4 RETURNING t.name;
Results
json
[ { "name": "Beverly Laurel Motor Hotel" }, { "name": "Tan yr Eglwys Cottages" }, ]