Collections

Construction

Collections contain any number of elements of the same type (or null).

Example

Type

Remarks

collection(<expression>, …)

collection(1, 2, 3)

collection(int not nullable)

Collection with three integers.

[<expression>, …]

[1, 2.0, null]

collection(double nullable)

Collection of doubles since 2.0 is a double.

Membership: IN / NOT IN

Check if an element is contained or not in a collection.

Syntax:

<expression> IN <collection>
<expression> NOT IN <collection>

For example:

1 IN collection(1,2,3) // true
1 NOT IN collection(1,2,3) // false
1 IN null // null
1 NOT IN null // null
null IN collection(1,2,3) // null

INTERSECT

Returns the intersection of two collections. Both collections must have compatible types.

Syntax:

<collection> INTERSECT <collection>

For example:

[1,2,3] INTERSECT [1,2,4] // [1,2]
null INTERSECT [1,2] // null
[1,2] INTERSECT null // null

Attention

INTERSECT not currently implemented.

EXCEPT

Returns the first collection without the elements in the second collection. Both collections must have compatible types.

Syntax:

<collection> EXCEPT <collection>

For example:

[1,2,3,4] EXCEPT [1,3] // [1,2]
null EXCEPT [1,3] // null
[1,2,null] EXCEPT null // [1,2,null]

Attention

EXCEPT not currently implemented.

UNION / UNION ALL / ++

UNION two collections, removing repeated elements. UNION ALL preserves repeated elements. ++ is equivalent to UNION ALL. Both collections must have compatible types.

Syntax:

<collection> UNION <collection>
<collection> UNION ALL <collection>
<collection> ++ <collection>

For example:

[1,2] UNION [2,3] is [1,2,3]
[1,2] UNION ALL [2,3] is [1,2,2,3]
[1,2] ++ [2,3] // Same as [1,2] UNION ALL [2,3]

LEN / LENGTH

Count the number of elements in the collection.

Syntax:

LEN(<collection>)
LENGTH(<collection>)

For example:

LEN([1, 2]) // 2
LENGTH([null, 1, 2]) // 3

CCOUNT

Count the number of elements in the collection skipping null elements.

Syntax:

CCOUNT(<collection>)

For example:

CCOUNT([1, 2]) // 2
CCOUNT([null, 1, 2]) // 2

CFIRST

Get the first element of the collection, or null if empty.

Syntax:

CFIRST(<collection>)

For example:

CFIRST([1, 2]) // 1
CFIRST([null, 1, 2]) // null
CFIRST([]) // null

CLAST

Get the last element of the collection, or null if empty.

Syntax:

CLAST(<collection>)

For example:

CLAST([1, 2]) // 2
CLAST([null, 1, 2]) // 2
CLAST([]) // null

EXISTS

True if collection is non-empty, false otherwise.

Syntax:

EXISTS(<collection>)

For example:

EXISTS([1, 2]) // true
EXISTS([]) // false
EXISTS(null) // null

ZIP

Combine elements of two collections. The collection produced has the same length as the shortest of the two.

Syntax:

zip(<collection>, <collection>)

For example:

zip([1,2], [2,3,4]) // [(1,2),(2,3)]

ENUMERATE

Create a new collection with a tuple consisting of the index (starting from 0) and the element.

Syntax:

enumerate(<collection>)

For example:

enumerate(["a","b"]) // [(0,"a"),(1,"b")]
enumerate([(name: "John", age: 30),
          (name: "Jane", age: 31)])
  // [(0, (name: "John", age: 30)), (1, (name: "Jane", age: 31))]

Index and Range Operations

Index and range operations are used to extract elements from collections.

Index operations have a variant with suffix ? that returns null instead of a runtime error for out-of-bounds accesses.

Syntax:

<collection>[<int index>]
<collection>[<start index>:<end index>]
<collection>[<start index>:<end index>:<step>]
<collection>[<int index>]?

For example:

X := [1,2,3]

x[1] // 2
x[0:1] // [1]
x[0:2] // [1,2]
x[-1] // [3]
x[0:3:2] // [1,3]
x[4] // Runtime error
x[4]? // null

Attention

Index and range operations can be expensive to compute over collections, particularly for large collections.

Attention

Index and range operations not currently implemented.

Comparisons

Collections cannot be compared with equality operators (=, <>, etc). However, collections can be compared using IS DISTINCT FROM / IS NOT DISTINCT FROM as described in Comparisons.

Casts

Collections can be casted to other collections, as long as the inner types are also castable.

For example:

cast(collection(1, 2) as collection(float)) // collection(1f, 2f)
cast([(a: 1)] as collection(record(a: string)) ) // [(a: "1")]