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
andname-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.
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.
[
{
"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"
}
}
]
But if you change the SATISFIES
clause to 1 minute after the last flight (23:42), then the resulting array is empty.
[]
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
.
[
{
"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.
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.
[
{
"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"
}
}
]
But if you change the SATISFIES
clause to 1 minute after the first flight (00:37), then the resulting array is empty.
[]
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
.
[
{
"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.
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
.
[
{
"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 thevar
andname-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
andname-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
andname-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.
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";
[
{
"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.
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";
[
{
"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";
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";
[
{
"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.
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";
[
{
"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.
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";
[
{
"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.
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.
[
{
"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.
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.
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:
[
{
"t": {
"callsign": "CORSAIR",
"country": "France",
"iata": "SS",
"icao": "CRL",
"id": 1908,
"name": "Corsairfly",
"type": "airline"
}
},
// ...
]
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.
[]
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.
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.
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.
[
{
"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.
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.
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.
[
{
"city": "Medway"
},
{
"city": "Giverny"
},
{
"city": "Glasgow"
},
{
"city": "Highland"
},
//...
]
Related Links
Refer to Construction Operators for a simpler way to generate arrays and objects from a data source.