Examples

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;

Example 12

A non-correlated subquery with INSERT.

INSERT INTO `travel-sample`  t1 (KEY _k, VALUE _v)  
SELECT "newkey_" || meta(t2).id as _k, t2.airportname as _v
FROM `travel-sample` t2 
WHERE t2.type = "airport" AND t2.geo.alt > 6400
RETURNING _k;

Example 13

A correlated subquery with DELETE all hotel records which got zero overall rating by more than 4 reviewers.
DELETE FROM `travel-sample` t 
WHERE type = "hotel" AND (SELECT RAW count(*) 
                          FROM t.reviews t2
                          WHERE t2.ratings.Overall = 0 )[0] > 4;