Examples
Example1
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.
SELECT name, (SELECT raw avg(s.ratings.Overall)
FROM t.reviews as s)[0] AS overall_avg_rating
FROM `travel-sample` AS t
WHERE type = "hotel"
ORDER BY overall_avg_rating DESC
LIMIT 3;
[
{
"name": "Culloden House Hotel",
"overall_avg_rating": 5
},
{
"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.
SELECT id, sourceairport, destinationairport,
(SELECT s.*
FROM `travel-sample`.schedule s
WHERE s.utc > "22:00:00"
ORDER BY s.utc) after_10pm
FROM `travel-sample`
WHERE type = "route" and sourceairport = "SFO"
LIMIT 2;
[
{
"after_10pm": [
{
"day": 2,
"flight": "DL708",
"utc": "22:07:00"
},
{
"day": 6,
"flight": "DL868",
"utc": "23:00:00"
}
],
"destinationairport": "LHR",
"id": 21756,
"sourceairport": "SFO"
},
{
"after_10pm": [
{
"day": 3,
"flight": "WS580",
"utc": "22:42:00"
}
],
"destinationairport": "YYC",
"id": 64657,
"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.
SELECT airline, sourceairport
FROM `travel-sample`
WHERE type = "route" AND (SELECT raw count(*)
FROM `travel-sample`.schedule as s WHERE s.day = 1)[0] > 4
LIMIT 3;
[
{
"airline": "FR",
"sourceairport": "BRE"
},
{
"airline": "WX",
"sourceairport": "ANR"
},
{
"airline": "BK",
"sourceairport": "TSN"
}
]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.
SELECT name, cnt_reviewers
FROM `travel-sample` AS t
LET cnt_reviewers = (SELECT raw count(*)
FROM t.reviews AS s
WHERE s.ratings.Overall >= 4)[0]
WHERE type = "hotel" and cnt_reviewers >= 6
ORDER BY cnt_reviewers DESC
LIMIT 3;
[
{
"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.
MERGE INTO `travel-sample` 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;
[
{
"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.
SELECT count(*) FROM `travel-sample` t
LET x = t.geo
WHERE (SELECT RAW y.alt FROM x y)[0] > 6000;Example 7
An example of using same keyspace name in subquery FROM clause that is used in the parent query.
SELECT array_length((SELECT RAW t1.geo.alt
FROM `travel-sample` t1))
FROM `travel-sample` LIMIT 4;
[
{
"$1": 31596
},
...
]Example 8
An example of using alias name in the subquery FROM clause.
SELECT array_length((SELECT RAW t1.geo.alt FROM t t1))
FROM `travel-sample` t;
[
{
"$1": 1
},
...
]Example 9
A non-correlated subquery with UPDATE.
Update `travel-sample` t1 set airportname_dup = "high_altitude_" || airportname
WHERE type = "airport" AND t1.geo.alt IN (SELECT raw t2.geo.alt
FROM `travel-sample` t2
WHERE t2.geo.alt > 6000)
RETURNING *;Example 10
A correlated subquery with UPDATE with nested paths.
UPDATE `travel-sample` t1
SET airportname_dup = "high_altitude_" || airportname
WHERE type = "airport" AND (SELECT RAW geo.alt
FROM t1.geo
WHERE geo.alt > 6000)[0] = t1.geo.alt
RETURNING *;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.
UPDATE `travel-sample` t1
SET reviews_5star = (SELECT raw t2
FROM t1.reviews t2
WHERE t2.ratings.Overall = 5
ORDER BY t2.author)
WHERE type = "hotel"
LIMIT 1
RETURNING t1.reviews[*].author, t1.reviews1;