Collection Functions

Confluent Cloud for Apache Flink®️ provides these built-in collections to use in SQL queries:

ARRAY

Syntax
ARRAY ‘[’ value1 [, value2 ]* ‘]’
Description

Creates an array from the specified list of values, (value1, value2, ...).

Use the bracket syntax, array_name[INT], to return the element at position INT in the array.

The index starts at 1.

Example
-- returns Java
SELECT ARRAY['Java', 'SQL'][1];

ARRAY_CONTAINS

Syntax
ARRAY_CONTAINS(array, element)

Returns a value indicating whether the specified element exists in an array.

Checking for NULL elements in the array is supported.

If the array itself is NULL, the ARRAY_CONTAINS function returns NULL.

The specified element is cast implicitly to the array’s element type, if necessary.

Example
-- returns TRUE
SELECT ARRAY_CONTAINS(ARRAY['Java', 'SQL'], 'SQL');

ARRAY_DISTINCT

Syntax
ARRAY_DISTINCT(array)

Returns an array with unique elements. If the specified array is NULL, the ARRAY_DISTINCT function returns null.

The order of elements in the source array is preserved in the returned array.

CARDINALITY(array)

Syntax
CARDINALITY(array)
Description
Returns the number of elements in the specified array.
Example
-- returns 5
SELECT CARDINALITY(ARRAY['Java', 'SQL', 'Python', 'Rust', 'C++']);

CARDINALITY(map)

Syntax
CARDINALITY(map)
Description
Returns the number of entries in the specified map.
Example
-- returns 3
SELECT CARDINALITY(MAP['Java', 5, 'SQL', 4, 'Python', 3]);

ELEMENT

Syntax
ELEMENT(array)
Description

Returns the sole element of the specified array. The cardinality of array must be 1. Returns NULL if array is empty.

Throws an exception if array has more than one element.

Example
-- returns Java
SELECT ELEMENT(ARRAY['Java']);

GROUP_ID

Syntax
GROUP_ID()
Description
Returns an integer that uniquely identifies the combination of grouping keys.

GROUPING

Syntax
GROUPING(expression1 [, expression2]* )
GROUPING_ID(expression1 [, expression2]* )
Description
Returns a bit vector of the specified grouping expressions.

Implicit row constructor

Syntax
(value1 [, value2]*)
Description

Returns a row created from a list of values, (value1, value2,...).

The implicit row constructor supports arbitrary expressions as fields and requires at least two fields.

The explicit row constructor can deal with an arbitrary number of fields but doesn’t support all kinds of field expressions.

MAP

Syntax
MAP [ key1, value1 [, key2, value2 ], ... ]
Description

Returns a map created from the specified list of key-value pairs, ((key1, value1), (key2, value2), ...).

Use the bracket syntax, map_name[key], to return the value that corresponds with the specified key.

Example
-- returns 4
SELECT MAP['Java', 5, 'SQL', 4, 'Python', 3]['SQL'];

Other built-in functions

Important

Confluent Cloud for Apache Flink®️ is currently available for Preview. A Preview feature is a Confluent Cloud component that is being introduced to gain early feedback from developers. Preview features can be used for evaluation and non-production testing purposes or to provide feedback to Confluent. The warranty, SLA, and Support Services provisions of your agreement with Confluent do not apply to Preview features. Confluent may discontinue providing Preview releases of the Preview features at any time in Confluent’s sole discretion. Check out Getting Help for questions, feedback and requests.

For Flink SQL features and limitations in the preview program, see Notable Limitations in Public Preview.

Related content

Note

This website includes content developed at the Apache Software Foundation under the terms of the Apache License v2.