Examples

reference

Please note that 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.

SELECT name, (SELECT raw avg(s.ratings.Overall)
              FROM   t.reviews  as s)[0] AS overall_avg_rating
FROM   `travel-sample`.inventory.hotel AS t
ORDER BY overall_avg_rating DESC
LIMIT 3;

Results

[
  {
    "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.

SELECT  id, sourceairport, destinationairport,
            (SELECT s.*
             FROM route.schedule s
             WHERE s.utc > "22:00:00"
             ORDER BY s.utc)  after_10pm
FROM `travel-sample`.inventory.route
WHERE sourceairport = "SFO"
LIMIT 2;

Results

[
  {
    "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.

SELECT airline, sourceairport
FROM  `travel-sample`.inventory.route
WHERE (SELECT raw count(*)
       FROM route.schedule as s WHERE s.day = 1)[0] > 4
LIMIT 3;

Results

[
  {
    "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.

SELECT name, cnt_reviewers
FROM   `travel-sample`.inventory.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

[
  {
    "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`.inventory.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

[
  {
    "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`.inventory.airport t
LET x = t.geo
WHERE (SELECT RAW y.alt FROM x y)[0] > 6000;

Results

[
  {
    "$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.

SELECT array_length((SELECT RAW t1.geo.alt
                     FROM `travel-sample`.inventory.airport t1))
FROM `travel-sample`.inventory.airport LIMIT 4;

Results

[
  {
    "$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.

SELECT array_length((SELECT RAW t1.geo.alt FROM t t1))
FROM `travel-sample`.inventory.airport t;

Results

[
  {
    "$1": 1
  },
  ...
]

Example 9

A non-correlated subquery with UPDATE.

UPDATE `travel-sample`.inventory.airport t1 SET airportname_dup = "high_altitude_" || airportname
WHERE  t1.geo.alt IN (SELECT RAW t2.geo.alt
                      FROM `travel-sample`.inventory.airport t2
                      WHERE t2.geo.alt > 6000)
RETURNING t1.airportname_dup;

Results

[
  {
    "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.

UPDATE `travel-sample`.inventory.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

[
  {
    "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.

UPDATE `travel-sample`.inventory.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

[
  {
    "author": [
      "Ozella Sipes",
      "Barton Marks"
    ]
  }
]

Example 12

A non-correlated subquery with INSERT.

INSERT INTO `travel-sample`.inventory.hotel (KEY UUID())
    SELECT x.name, x.city, "landmark_hotels" AS type
      FROM `travel-sample`.inventory.hotel x
      WHERE x.city WITHIN
        ( SELECT DISTINCT t.city
            FROM `travel-sample`.inventory.landmark t)
      LIMIT 4
RETURNING *;

Results

[
  {
    "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.

DELETE FROM `travel-sample`.inventory.hotel t
WHERE (SELECT RAW count(*)
       FROM t.reviews t2
       WHERE t2.ratings.Overall = 1 )[0] > 4
RETURNING t.name;

Results

[
  {
    "name": "Beverly Laurel Motor Hotel"
  },
  {
    "name": "Tan yr Eglwys Cottages"
  },
]