Functions for Working with Tuples

tuple

A function that allows grouping multiple columns.
For columns with the types T1, T2, …, it returns a Tuple(T1, T2, …) type tuple containing these columns. There is no cost to execute the function.
Tuples are normally used as intermediate values for an argument of IN operators, or for creating a list of formal parameters of lambda functions. Tuples can’t be written to a table.

The function implements the operator (x, y, …).

Syntax

  1. tuple(x, y, …)

tupleElement

A function that allows getting a column from a tuple.
‘N’ is the column index, starting from 1. N must be a constant. ‘N’ must be a constant. ‘N’ must be a strict postive integer no greater than the size of the tuple.
There is no cost to execute the function.

The function implements the operator x.N.

Syntax

  1. tupleElement(tuple, n)

untuple

Performs syntactic substitution of tuple elements in the call location.

Syntax

  1. untuple(x)

You can use the EXCEPT expression to skip columns as a result of the query.

Arguments

  • x - A tuple function, column, or tuple of elements. Tuple.

Returned value

  • None.

Examples

Input table:

  1. ┌─key─┬─v1─┬─v2─┬─v3─┬─v4─┬─v5─┬─v6────────┐
  2. │   1 │ 10 │ 20 │ 40 │ 30 │ 15 │ (33,'ab') │
  3. │   2 │ 25 │ 65 │ 70 │ 40 │  6 │ (44,'cd') │
  4. │   3 │ 57 │ 30 │ 20 │ 10 │  5 │ (55,'ef') │
  5. │   4 │ 55 │ 12 │  7 │ 80 │ 90 │ (66,'gh') │
  6. │   5 │ 30 │ 50 │ 70 │ 25 │ 55 │ (77,'kl') │
  7. └─────┴────┴────┴────┴────┴────┴───────────┘

Example of using a Tuple-type column as the untuple function parameter:

Query:

  1. SELECT untuple(v6) FROM kv;

Result:

  1. ┌─_ut_1─┬─_ut_2─┐
  2. │    33 │ ab    │
  3. │    44 │ cd    │
  4. │    55 │ ef    │
  5. │    66 │ gh    │
  6. │    77 │ kl    │
  7. └───────┴───────┘

Example of using an EXCEPT expression:

Query:

  1. SELECT untuple((* EXCEPT (v2, v3),)) FROM kv;

Result:

  1. ┌─key─┬─v1─┬─v4─┬─v5─┬─v6────────┐
  2. │   1 │ 10 │ 30 │ 15 │ (33,'ab') │
  3. │   2 │ 25 │ 40 │  6 │ (44,'cd') │
  4. │   3 │ 57 │ 10 │  5 │ (55,'ef') │
  5. │   4 │ 55 │ 80 │ 90 │ (66,'gh') │
  6. │   5 │ 30 │ 25 │ 55 │ (77,'kl') │
  7. └─────┴────┴────┴────┴───────────┘

See Also

Original article