A newer version of this documentation is available.

View Latest

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;