Collection Operators

  • reference
    +

    Collection operators enable you to evaluate expressions over every element in an array. The operators include Range Predicates, Range Transformations, and Membership and Existence Tests.

    Although collection operators can be used with any array, they are particularly useful when used to evaluate expressions over an array of objects. The term collection is used here in a generic sense to refer to any array of objects, rather than in the specific sense of a Couchbase collection.

    Range Predicates

    Range predicates (ANY, EVERY, or ANY AND EVERY) enable you to test a Boolean condition over elements in an array. They use the IN and WITHIN operators to range through the array.

    Range predicates may also be known as range conditions or quantified expressions.

    Syntax

    range-cond ::= ( ( 'ANY' | 'SOME' ) ( 'AND' 'EVERY' )? | 'EVERY' )
                   range 'SATISFIES' cond 'END'
    Syntax diagram
    range ::= ( name-var ':' )? var ( 'IN' | 'WITHIN' ) expr
        ( ',' ( name-var ':' )? var ( 'IN' | 'WITHIN' ) expr )*
    Syntax diagram

    Arguments

    name-var

    [Optional] An identifier that represents the position of a single element in an array, counting from 0.

    var

    An identifier that represents a single element in an array.

    expr

    An expression that returns an array to evaluate.

    cond

    A condition to evaluate for each specified element. This condition may make use of the var and name-var identifiers as required.

    ANY

    ANY tests whether any element in an array matches a specified condition. (If the array is empty, then no element in the array is deemed to match the condition.)

    Synonym: SOME is a synonym for ANY.

    Return Values

    If the array is non-empty and at least one element in the array matches the specified condition, then the operator returns TRUE; otherwise, it returns FALSE.

    Examples

    To try the examples in this section, set the query context to the inventory scope in the travel sample dataset. For more information, see Query Context.

    Example 1. ANY with one matching element

    Retrieve the details of KL flight schedules from Albuquerque (ABQ) to Atlanta (ATL) if any of the flights are after 23:40.

    SELECT * FROM route
    WHERE airline="KL" AND sourceairport="ABQ"
      AND destinationairport="ATL"
      AND ANY departure IN schedule SATISFIES departure.utc > "23:40" END;

    Since the last flight departs at 23:41, this query results in the entire array.

    Results
    [
      {
        "travel-sample": {
          "airline": "KL",
          "airlineid": "airline_3090",
          "destinationairport": "ATL",
          "distance": 2038.3535078909663,
          "equipment": "757 320",
          "id": 36159,
          "schedule": [
            {
              "day": 0,
              "flight": "KL938",
              "utc": "03:54:00"
            },
    // ...
            {
              "day": 5,
              "flight": "KL169",
              "utc": "23:41:00"
            },
    // ...
            {
              "day": 6,
              "flight": "KL636",
              "utc": "17:40:00"
            }
          ],
          "sourceairport": "ABQ",
          "stops": 0,
          "type": "route"
        }
      }
    ]
    Example 2. ANY with no matching elements

    But if you change the SATISFIES clause to 1 minute after the last flight (23:42), then the resulting array is empty.

    Results
    []
    Example 3. ANY with empty array

    This example tests the ANY operator with an empty array.

    SELECT ANY v IN [] SATISFIES v = "abc" END AS existential;

    In this case, the operator returns false.

    Results
    [
      {
        "existential": false
      }
    ]

    EVERY

    EVERY tests whether every element in an array matches a specified condition. (If the array is empty, then every element in the array is deemed to match the condition.)

    Return Values

    If the array is empty, or if the array is non-empty and every element in the array matches the specified condition, then the operator returns TRUE; otherwise, it returns FALSE.

    Examples

    To try the examples in this section, set the query context to the inventory scope in the travel sample dataset. For more information, see Query Context.

    Example 4. EVERY with all matching elements

    Retrieve the details of KL flight schedules from Albuquerque (ABQ) to Atlanta (ATL) if all of the flights are after 00:35.

    SELECT * FROM route
    WHERE airline="KL"
      AND sourceairport="ABQ"
      AND destinationairport="ATL"
      AND EVERY departure IN schedule SATISFIES departure.utc > "00:35" END;

    Since the earliest flight departs at 00:36, this query results in the entire array.

    Results
    [
      {
        "travel-sample": {
          "airline": "KL",
          "airlineid": "airline_3090",
          "destinationairport": "ATL",
          "distance": 2038.3535078909663,
          "equipment": "757 320",
          "id": 36159,
          "schedule": [
    // ...
            {
              "day": 6,
              "flight": "KL884",
              "utc": "00:36:00"
            },
    // ...
            {
              "day": 6,
              "flight": "KL636",
              "utc": "17:40:00"
            }
          ],
          "sourceairport": "ABQ",
          "stops": 0,
          "type": "route"
        }
      }
    ]
    Example 5. EVERY with no matching elements

    But if you change the SATISFIES clause to 1 minute after the first flight (00:37), then the resulting array is empty.

    Results
    []
    Example 6. EVERY with empty array

    This example tests the EVERY operator with an empty array.

    SELECT EVERY v IN [] SATISFIES v = "abc" END AS universal;

    In this case, the operator returns true.

    Results
    [
      {
        "universal": true
      }
    ]

    ANY AND EVERY

    ANY AND EVERY tests whether every element in an array matches a specified condition. (If the array is empty, then no element in the array is deemed to match the condition.)

    Synonym: SOME AND EVERY is a synonym for ANY AND EVERY.

    Return Values

    If the array is non-empty and every element in the array matches the specified condition, then the operator returns TRUE; otherwise, it returns FALSE.

    Examples

    To try the examples in this section, set the query context to the inventory scope in the travel sample dataset. For more information, see Query Context.

    Example 7. ANY AND EVERY with empty array

    This example tests the ANY AND EVERY operator with an empty array.

    SELECT ANY AND EVERY v IN [] SATISFIES v = "abc" END AS universal;

    In this case, the operator returns false.

    Results
    [
      {
        "universal": false
      }
    ]

    Range Transformations

    Range transformations (ARRAY, FIRST, and OBJECT) enable you to map and filter elements and attributes from an input array. They use the IN and WITHIN operators to range through the array.

    Syntax

    range-xform ::= ( ( 'ARRAY' | 'FIRST' ) | 'OBJECT' name-expr ':' ) var-expr
                    'FOR' range ( 'WHEN' cond )? 'END'
    Syntax diagram
    range ::= ( name-var ':' )? var ( 'IN' | 'WITHIN' ) expr
        ( ',' ( name-var ':' )? var ( 'IN' | 'WITHIN' ) expr )*
    Syntax diagram

    Arguments

    name-expr

    [OBJECT only] An expression that resolves to a string, to use as the name of an attribute in the output. This expression may make use of the var and name-var identifiers as required.

    var-expr

    An expression that returns a value to include in the output. This expression may make use of the var and name-var identifiers as required.

    name-var

    [Optional] An identifier that represents the position of a single element in an array, counting from 0.

    var

    An identifier that represents a single element in an array.

    expr

    An expression that returns an array to evaluate.

    cond

    [Optional] A condition to evaluate for each specified element. This condition may make use of the var and name-var identifiers as required.

    ARRAY

    The ARRAY operator generates a new array, using values in the input array.

    Return Values

    The operator returns a new array, which contains one element for each element in the input array. If the WHEN clause is specified, only elements in the input array which satisfy the WHEN clause are considered.

    The value of each element in the output array is the output of the var-expr argument for one element in the input array.

    If the input array is empty, or no elements in the input array satisfy the WHEN clause, the operator returns an empty array.

    Examples

    To try the examples in this section, set the query context to the inventory scope in the travel sample dataset. For more information, see Query Context.

    Example 8. ARRAY with array of objects

    List the details of KL flights from Albuquerque to Atlanta on Fridays.

    SELECT ARRAY v FOR v IN schedule WHEN v.day = 5 END AS fri_flights
    FROM route
    WHERE airline="KL"
      AND sourceairport="ABQ"
      AND destinationairport="ATL";
    Results
    [
      {
        "fri_flights": [
          {
            "day": 5,
            "flight": "KL347",
            "utc": "08:51:00"
          },
          {
            "day": 5,
            "flight": "KL281",
            "utc": "06:26:00"
          },
          {
            "day": 5,
            "flight": "KL567",
            "utc": "03:54:00"
          },
          {
            "day": 5,
            "flight": "KL169",
            "utc": "23:41:00"
          }
        ]
      }
    ]

    Compare this with the results of Example 11 and Example 12.

    Example 9. ARRAY with multiple range terms

    List the details of KL flights from Albuquerque to Atlanta on Fridays after 7pm only.

    SELECT ARRAY v
      FOR v IN schedule, w IN schedule WHEN v.utc > "19:00" AND w.day = 5 END
      AS fri_evening_flights
    FROM route
    WHERE airline="KL"
      AND sourceairport="ABQ"
      AND destinationairport="ATL";
    Results
    [
      {
        "fri_evening_flights": [
          {
            "day": 5,
            "flight": "KL169",
            "utc": "23:41:00"
          }
        ]
      }
    ]

    The same results can be reached by writing the query as follows:

    SELECT ARRAY v
      FOR v IN schedule WHEN v.utc > "19:00" AND v.day = 5 END
      AS fri_evening_flights
    FROM route
    WHERE airline="KL"
      AND sourceairport="ABQ"
      AND destinationairport="ATL";
    Example 10. ARRAY with position variable

    List the first two KL flights from Albuquerque to Atlanta. This example uses the position variable i to return just the first two elements in the input array.

    SELECT ARRAY v FOR i:v IN schedule WHEN i < 2 END AS two_flights
    FROM route
    WHERE airline="KL"
      AND sourceairport="ABQ"
      AND destinationairport="ATL";
    Results
    [
      {
        "two_flights": [
          {
            "day": 0,
            "flight": "KL938",
            "utc": "03:54:00"
          },
          {
            "day": 0,
            "flight": "KL270",
            "utc": "16:57:00"
          }
        ]
      }
    ]

    Refer to Example 13 for another example with position variables.

    FIRST

    The FIRST operator generates a new value, using a single value in the input array.

    Return Values

    The operator returns the output of the var-expr argument for the first element in the input array. If the WHEN clause is specified, only elements in the input array which satisfy the WHEN clause are considered.

    If the input array is empty, or no elements in the input array satisfy the WHEN clause, the operator returns MISSING.

    Examples

    To try the examples in this section, set the query context to the inventory scope in the travel sample dataset. For more information, see Query Context.

    Example 11. FIRST

    List the first KL flight from Albuquerque to Atlanta after 7pm.

    SELECT FIRST v FOR v IN schedule WHEN v.utc > "19:00" END AS first_flight
    FROM route
    WHERE airline="KL"
      AND sourceairport="ABQ"
      AND destinationairport="ATL";
    Results
    [
      {
        "first_flight": [
          {
            "day": 1,
            "flight": "KL672",
            "utc": "19:19:00"
          }
        ]
      }
    ]

    Compare this with the results of Example 8 and Example 12.

    OBJECT

    The OBJECT operator generates a new object, using values in the input array.

    Return Values

    The operator returns an object, which contains one attribute for each element in the input array. If the WHEN clause is specified, only elements in the input array which satisfy the WHEN clause are considered.

    The value of each attribute in the output object is the output of the var-expr argument for one element in the input array.

    The name of each attribute in the output object is specified by the name-expr argument. This argument must be an expression that generates a unique name string for every value in the output object. If the expression does not generate a string, then the current attribute is not output. If the expression does not generate a unique name string for each value, then only the last attribute is output; all previous attributes are suppressed.

    The name-expr argument may reference the var argument or the name-var argument, or use any other expression that generates a unique value.

    If the input array is empty, or no elements in the input array satisfy the WHEN clause, the operator returns an empty object.

    Examples

    To try the examples in this section, set the query context to the inventory scope in the travel sample dataset. For more information, see Query Context.

    Example 12. OBJECT with array of objects

    List the details of KL flights from Albuquerque to Atlanta on Fridays. This example uses the UUID() function to generate a unique name for each attribute in the output object.

    SELECT OBJECT UUID():v FOR v IN schedule WHEN v.day = 5 END AS fri_flights
    FROM route
    WHERE airline="KL"
      AND sourceairport="ABQ"
      AND destinationairport="ATL";
    Results
    [
      {
        "fri_flights": {
          "14c040c6-2247-442f-bc27-0d7b3ff403b5": {
            "day": 5,
            "flight": "KL169",
            "utc": "23:41:00"
          },
          "645a53d6-53a2-4c0c-9431-75073c48806b": {
            "day": 5,
            "flight": "KL281",
            "utc": "06:26:00"
          },
          "6d93a43f-ecec-4e9d-89bf-2468f2771fa0": {
            "day": 5,
            "flight": "KL567",
            "utc": "03:54:00"
          },
          "f2823bc0-86e0-4a1a-a9d8-4ca496de8193": {
            "day": 5,
            "flight": "KL347",
            "utc": "08:51:00"
          }
        }
      }
    ]

    Compare this with the results of Example 8 and Example 11.

    Example 13. OBJECT with position variable

    An alternative version of Example 12. This example uses the TOSTRING() function and the position variable i to generate a unique name for each attribute in the output object.

    SELECT OBJECT "num_" || TOSTRING(i):v
      FOR i:v IN schedule WHEN v.day = 5 END
      AS fri_flights
    FROM route
    WHERE airline="KL"
      AND sourceairport="ABQ"
      AND destinationairport="ATL";

    Notice that the position of each element in the input array is calculated before applying the WHEN condition — so the Friday flights are numbered from 14 to 17.

    Results
    [
      {
        "fri_flights": {
          "num_14": {
            "day": 5,
            "flight": "KL347",
            "utc": "08:51:00"
          },
          "num_15": {
            "day": 5,
            "flight": "KL281",
            "utc": "06:26:00"
          },
          "num_16": {
            "day": 5,
            "flight": "KL567",
            "utc": "03:54:00"
          },
          "num_17": {
            "day": 5,
            "flight": "KL169",
            "utc": "23:41:00"
          }
        }
      }
    ]

    Refer to Example 10 for another example with position variables.

    Membership and Existence

    Membership tests (IN and WITHIN) enable you to test whether a value exists within an array. Membership tests are efficient over arrays with a large number of elements — up to approximately 8000.

    The existence test (EXISTS) enables you to test whether an array contains any elements at all.

    IN

    The IN operator specifies the search depth to include only the current level of an array, and not to include any child or descendant arrays.

    Syntax

    in-expr ::= search-expr 'NOT'? 'IN' target-expr
    Syntax diagram

    Arguments

    earch-expr

    An expression that returns the value to search for.

    target-expr

    An expression that resolves to the array to search through.

    Return Values

    The IN operator evaluates to TRUE if the right-side value is an array and directly contains the left-side value.

    The NOT IN operator evaluates to TRUE if the right-side value is an array and does not directly contain the left-side value.

    Examples

    To try the examples in this section, set the query context to the inventory scope in the travel sample dataset. For more information, see Query Context.

    Example 14. IN with simple array

    Search for all airlines from the United Kingdom or France.

    SELECT * FROM airline AS t
    WHERE country IN ["United Kingdom", "France"];

    This results in 60 documents:

    Results
    [
      {
        "t": {
          "callsign": "CORSAIR",
          "country": "France",
          "iata": "SS",
          "icao": "CRL",
          "id": 1908,
          "name": "Corsairfly",
          "type": "airline"
        }
      },
    // ...
    ]
    Example 15. IN with array of objects

    Search for the author "Walton Wolf" in the hotel keyspace.

    SELECT * FROM hotel AS t WHERE "Walton Wolf" IN t;

    This results in an empty set because authors are not in the current level (the root level) of the hotel keyspace.

    Results
    []

    The authors are listed inside the reviews array (a child element) and would need the WITHIN keyword to search all child elements along with the root level.

    WITHIN

    The WITHIN operator specifies the search depth to include the current level of an array, and all of its child and descendant arrays.

    Syntax

    within-expr ::= search-expr 'NOT'? 'WITHIN' target-expr
    Syntax diagram

    Arguments

    search-expr

    An expression that returns the value to search for.

    target-expr

    An expression that resolves to the array to search through.

    Return Values

    The WITHIN operator evaluates to TRUE if the right-side value is an array and contains the left-side value as a child or descendant, that is, directly or indirectly.

    The NOT WITHIN operator evaluates to TRUE if the right-side value is an array and no child or descendant contains the left-side value.

    Examples

    To try the examples in this section, set the query context to the inventory scope in the travel sample dataset. For more information, see Query Context.

    Example 16. WITHIN

    Search all elements for the author "Walton Wolf" in the hotel documents.

    SELECT * FROM hotel AS t WHERE "Walton Wolf" WITHIN t;

    This results in 1 document since his name appears inside the reviews array.

    Results
    [
      {
        "t": {
          "address": "Gilsland, CA8 7DA",
          "alias": null,
          "checkin": null,
          "checkout": null,
          "city": null,
          "country": "United Kingdom",
          "description": "Tantallon House offers accommodation around 10 minutes walk from the National Trail. It also has a holiday cottage.",
          "directions": null,
          "email": null,
          "fax": null,
          "free_breakfast": true,
          "free_internet": true,
          "free_parking": false,
          "geo": {
            "accuracy": "ROOFTOP",
            "lat": 54.99304,
            "lon": -2.58142
          },
          "id": 10851,
          "name": "Tantallon House B&B",
          "pets_ok": true,
          "phone": null,
          "price": "From £44 (no cards)",
          "public_likes": [
            "Victor Russel"
          ],
          "reviews": [
            {
              "author": "Walton Wolf",
    // ...
            }
          ],
          "state": null,
          "title": "Hadrian's Wall",
          "tollfree": null,
          "type": "hotel",
          "url": "http://www.tantallonhouse.co.uk/",
          "vacancy": false
        }
      }
    ]

    EXISTS

    The EXISTS operator enables you to test whether an array has any elements, or is empty.

    This operator may be used in a SELECT, INSERT, UPDATE, or DELETE statement in combination with a subquery. The condition is met if the subquery returns at least one result.

    Syntax

    exists-expr ::= 'EXISTS' expr
    Syntax diagram

    Arguments

    expr

    An expression that returns an array.

    Return Values

    If the expression is an array which contains at least one element, the operator evaluates to TRUE; otherwise, it evaluates to FALSE.

    Examples

    To try the examples in this section, set the query context to the inventory scope in the travel sample dataset. For more information, see Query Context.

    Example 17. EXISTS

    Of the 274 cities with a hotel, search for all cities that have hotels with reviews.

    SELECT DISTINCT h.city
    FROM hotel AS h
    WHERE EXISTS h.reviews;

    This results in 255 cities that contain hotels with reviews.

    Results
    [
      {
        "city": "Medway"
      },
      {
        "city": "Giverny"
      },
      {
        "city": "Glasgow"
      },
      {
        "city": "Highland"
      },
    //...
    ]

    Refer to Construction Operators for a simpler way to generate arrays and objects from a data source.