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'

range ::= ( name-var ':' )? var ( 'IN' | 'WITHIN' ) expr
    ( ',' ( name-var ':' )? var ( 'IN' | 'WITHIN' ) expr )*

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'

range ::= ( name-var ':' )? var ( 'IN' | 'WITHIN' ) expr
    ( ',' ( name-var ':' )? var ( 'IN' | 'WITHIN' ) expr )*

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

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

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

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.

Collection Operators

Range Predicates

Syntax

Arguments

ANY

Return Values

Examples

EVERY

Return Values

Examples

ANY AND EVERY

Return Values

Examples

Range Transformations

Syntax

Arguments

ARRAY

Return Values

Examples

FIRST

Return Values

Examples

OBJECT

Return Values

Examples

Membership and Existence

IN

Syntax

Arguments

Return Values

Examples

WITHIN

Syntax

Arguments

Return Values

Examples

EXISTS

Syntax

Arguments

Return Values

Examples

Related Links