Tuple functions enable efficient creation, manipulation, and analysis of composite data types. They support operations ranging from basic tuple creation to advanced element-wise arithmetic, facilitating complex data handling and structured analysis.

​
ClickHouse function reference

​
tuple

Creates a tuple from the given arguments.

Syntax:

tuple(x, y, ...)

Arguments:

  • x, y, … (any type): Values of any type.

Returns:

A tuple containing the provided arguments.

Example:

SELECT
  tuple('Carne Asada', 2, 9.99) AS taco_info;

Result:

| taco_info              |
|------------------------|
| ('Carne Asada',2,9.99) |

This function allows you to group multiple values into a single tuple. It’s often used as an intermediate step for operations like IN clauses or as arguments for lambda functions. Tuples cannot be directly written to tables.

The function implements the operator (x, y, ...), so you can also create tuples using parentheses:

SELECT
  ('Al Pastor', 3, 8.50) AS another_taco;

​
tupleElement

Returns the element at the specified index or name in a tuple.

Syntax:

tupleElement(tuple, index [, default_value])
tupleElement(tuple, name [, default_value])

Arguments:

  • tuple (Tuple): The input tuple.
  • index (numeric): The 1-based index of the element to return.
  • name (String): The name of the element to return.
  • default_value (optional): The value to return if the index is out of bounds or the name doesn’t exist.

Returns:

The element at the specified index or name in the tuple.

Example:

SELECT
	tupleElement(('Carnitas', 'Barbacoa', 'Al Pastor'), 2) AS second_filling,
	tupleElement((filling := 'Pollo', salsa := 'Verde'), 'salsa') AS salsa_type;

Result:

| second_filling | salsa_type |
|----------------|------------|
| Barbacoa       | Verde      |

In this example:

  • second_filling returns the second element of the tuple.
  • salsa_type returns the value associated with the ‘salsa’ key in the named tuple.
  • If the index is out of bounds or the name doesn’t exist, and no default value is provided, the function throws an exception.
  • The function implements the operators x.index and x.name for tuple access.

Arguments:

  • x (Tuple): A tuple function, column, or tuple of elements.

Returns:

  • None. The function substitutes tuple elements directly in the query.
  • The names of the result columns are implementation-specific and subject to change. Do not assume specific column names after untuple.
  • You can use the EXCEPT expression to skip columns in the result of the query.

Example:

SELECT
    1 AS order_id,
    untuple(('Carne Asada', 2, 8.99) AS items)

UNION ALL

SELECT
    2 AS order_id,
    untuple(('Al Pastor', 3, 7.50) AS items)

UNION ALL

SELECT
    3 AS order_id,
    untuple(('Pollo', 1, 6.99) AS items);

Result:

| order_id | name        | quantity | price |
|----------|-------------|----------|-------|
| 1        | Carne Asada | 2        | 8.99  |
| 3        | Pollo       | 1        | 6.99  |
| 2        | Al Pastor   | 3        | 7.50  |

In this example, untuple expands the items tuple into separate columns.

Example using EXCEPT:

SELECT
	order_id,
	untuple(items.*) EXCEPT (quantity)
FROM taco_orders;

Result:

| order_id | name        | price |
|----------|-------------|-------|
| 1        | Carne Asada | 8.99  |
| 2        | Al Pastor   | 7.50  |
| 3        | Pollo       | 6.99  |

This query excludes the quantity field from the result.

The untuple function is particularly useful when working with complex nested structures or when you need to flatten tuple data for further processing or analysis.

​
tupleHammingDistance

Calculates the Hamming distance between two tuples of the same size.

Syntax:

tupleHammingDistance(tuple1, tuple2)

Arguments:

  • tuple1 (Tuple): First tuple.
  • tuple2 (Tuple): Second tuple.

Tuples should have the same type of elements.

Returns:

  • The Hamming distance.

The result type is calculated the same way as for Arithmetic functions, based on the number of elements in the input tuples.

Example:

SELECT
  tupleHammingDistance(
    ('Carne Asada', 'Salsa Verde', 'Guacamole'),
    ('Pollo', 'Salsa Verde', 'Queso')
  ) AS TacoDistance;

Result:

| TacoDistance |
|--------------|
| 2            |

This function can be used with MinHash functions for detection of semi-duplicate strings:

SELECT
	tupleHammingDistance(
		wordShingleMinHash(menu_item),
		wordShingleMinHashCaseInsensitive(menu_item)
	) AS MenuItemDistance
FROM
(
	SELECT 'Tacos al Pastor with pineapple, onions, and cilantro served on corn tortillas.' AS menu_item
);

Result:

| MenuItemDistance |
|------------------|
| 2                |

In this example, we compare the MinHash of a taco menu item description with its case-insensitive version to detect potential duplicates or variations in menu listings.

​
tupleToNameValuePairs

Converts a named tuple into an array of (name, value) pairs.

Syntax:

tupleToNameValuePairs(tuple)

Arguments:

  • tuple (Tuple): A named tuple.

Returns:

An array of (name, value) pairs, where:

  • The names are strings representing the named fields of the tuple.
  • The values are of the same type as in the original tuple.

Type: Array(Tuple(String, T))

Example:

SELECT
  tupleToNameValuePairs(
    tuple(taco_id := 1,
      filling := 'carnitas',
      price := 9.99
    )
  ) AS taco_info;

Result:

| taco_info                                                |
|----------------------------------------------------------|
| [('taco_id',1),('filling','carnitas'),('price',9.99)]    |

This function can be useful for transforming columns to rows:

CREATE TABLE taco_stats
(
	stats Tuple(orders UInt32, revenue Float64, rating Float32)
) ENGINE = Memory;

INSERT INTO taco_stats VALUES (tuple(100, 999.50, 4.7));

SELECT
	arrayJoin(tupleToNameValuePairs(stats))
FROM
	taco_stats;

Result:

| arrayJoin(tupleToNameValuePairs(stats)) |
|-----------------------------------------|
| ('orders',100)                          |
| ('revenue',999.5)                       |
| ('rating',4.7)                          |

If you pass a simple tuple without named elements, ClickHouse uses the element indices as names:

SELECT
  tupleToNameValuePairs(tuple(3, 'al pastor', 8.50));

Result:

| tupleToNameValuePairs(tuple(3, 'al pastor', 8.50))  |
|-----------------------------------------------------|
| [('1',3),('2','al pastor'),('3',8.5)]               |

This function is particularly useful when working with complex nested structures or when you need to dynamically process tuple elements without knowing their names in advance.

​
tupleNames

Returns an array of column names from a tuple.

Syntax:

tupleNames(tuple)

Arguments:

  • tuple (Tuple): A named tuple.

Returns:

An array of strings representing the named columns of the tuple. If the tuple elements do not have explicit names, their indices will be used as the column names instead.

Type: Array(String).

Example:

SELECT
  tupleNames((taco_id UInt64, filling String, price Decimal(5,2))) AS taco_info;

Result:

| taco_info                     |
|-------------------------------|
| ['taco_id','filling','price'] |

If you pass a simple tuple without named elements to the function, ClickHouse uses the indices of the values as their names:

SELECT tupleNames((3, 'carnitas', 9.99));

Result:

| tupleNames((3, 'carnitas', 9.99))  |
|------------------------------------|
| ['1','2','3']                      |

This function is useful when working with complex tuple structures or when you need to dynamically access the names of tuple elements in your queries.

​
tuplePlus

Calculates the sum of corresponding elements in two tuples of the same size.

Syntax:

tuplePlus(tuple1, tuple2)

Alias:

  • vectorSum

Arguments:

  • tuple1 (Tuple): First tuple.
  • tuple2 (Tuple): Second tuple.

Returns:

  • A tuple containing the sum of corresponding elements. (Tuple).

Example:

SELECT
  tuplePlus((1, 2, 3), (4, 5, 6)) AS result;

Result:

| result   |
|----------|
| (5,7,9)  |

In this example, tuplePlus adds the corresponding elements of two tuples:

  • 1 + 4 = 5
  • 2 + 5 = 7
  • 3 + 6 = 9

The function can be useful for vector operations or when you need to perform element-wise addition on multiple values simultaneously.

Both input tuples must have the same number of elements and compatible types for addition.

​
tupleMinus

Calculates the subtraction of corresponding values of two tuples of the same size.

Syntax:

tupleMinus(tuple1, tuple2)

Alias:

  • vectorDifference

Arguments:

  • tuple1 (Tuple): First tuple.
  • tuple2 (Tuple): Second tuple.

Returns:

A tuple with the result of subtraction. (Tuple)

Example:

SELECT
	tupleMinus((10, 5), (3, 2)) AS taco_math;

Result:

| taco_math |
|-----------|
| (7,3)     |

In this example, we subtract the values of the second tuple from the first tuple, simulating a taco shop calculation:

  • 10 tacos minus 3 eaten = 7 tacos left
  • 5 salsas minus 2 used = 3 salsas left

The tupleMinus function performs element-wise subtraction, allowing for quick calculations on multiple values simultaneously.

Arguments:

  • tuple1 (Tuple): First tuple.
  • tuple2 (Tuple): Second tuple.

Returns:

  • A tuple containing the element-wise multiplication results. (Tuple).

Example:

SELECT
  tupleMultiply((2, 3, 4), (1, 2, 3)) AS result;

Result:

| result   |
|----------|
| (2,6,12) |

In this example, we multiply two tuples element-wise:

  • 2 * 1 = 2
  • 3 * 2 = 6
  • 4 * 3 = 12

The function can be useful for performing element-wise operations on multiple values simultaneously, such as calculating the total price of different taco ingredients:

SELECT
	tupleMultiply(
		(quantity_beef, quantity_chicken, quantity_fish),
		(price_beef, price_chicken, price_fish)
	) AS ingredient_costs
FROM
	taco_orders;

This query would return a tuple with the cost of each ingredient based on its quantity and price.

​
tupleDivide

Calculates the division of corresponding values of two tuples of the same size.

Syntax:

tupleDivide(tuple1, tuple2)

Arguments:

  • tuple1 (Tuple): First tuple.
  • tuple2 (Tuple): Second tuple.

Returns:

  • Tuple with the result of division. (Tuple).

Division by zero will return inf.

Example:

SELECT
  tupleDivide((10, 4), (2, 2)) AS result;

Result:

| result |
|--------|
| (5, 2) |

In this example, we divide the values of the first tuple (10, 4) by the corresponding values of the second tuple (2, 2), resulting in (5, 2).

Example:

SELECT
	tupleDivide(
		(SELECT (COUNT(*), AVG(price)) FROM taco_menu WHERE spicy = true),
		(SELECT (COUNT(*), AVG(price)) FROM taco_menu WHERE spicy = false)
	) AS spicy_vs_mild_ratio;

This query compares the number and average price of spicy tacos to mild tacos. The result might look like:

| spicy_vs_mild_ratio |
|---------------------|
| (0.75, 1.2)         |

This indicates that there are 75% as many spicy tacos as mild tacos, and spicy tacos are on average 1.2 times more expensive than mild tacos.

​
tupleNegate

Calculates the negation of the tuple values.

Syntax:

tupleNegate(tuple)

Arguments:

  • tuple (Tuple): A tuple containing numeric values.

Returns:

A tuple with the negated values of the input tuple. (Tuple).

Example:

SELECT
  tupleNegate((1, 2, -3)) AS negated_tuple;

Result:

| negated_tuple |
|---------------|
| (-1,-2,3)     |

In this example, tupleNegate calculates the negation of each value in the input tuple (1, 2, -3), resulting in (-1, -2, 3).

This function is useful when you need to invert the signs of all values in a tuple simultaneously, such as when working with coordinate systems or financial calculations involving debits and credits.

The tupleNegate function only works with tuples containing numeric values. Attempting to negate non-numeric values will result in an error.

​
tupleMultiplyByNumber

Multiplies all elements of a tuple by a given number.

Syntax:

tupleMultiplyByNumber(tuple, number)

Arguments:

  • tuple (Tuple): A tuple containing numeric values.
  • number (numeric): The multiplier.

Returns:

  • A new tuple with all elements multiplied by the given number. (Tuple).

Example:

SELECT
  tupleMultiplyByNumber((1, 2, 3), 2) AS result;

Result:

| result   |
|----------|
| (2,4,6)  |

In this example, each element of the tuple (1, 2, 3) is multiplied by 2, resulting in a new tuple (2, 4, 6).

Example:

SELECT
	tupleMultiplyByNumber(
		(
			toDecimal32(3.50, 2),  -- Price of a basic taco
			toDecimal32(1.25, 2),  -- Price of extra cheese
			toDecimal32(0.75, 2)   -- Price of extra salsa
		),
		1.1                      -- 10% price increase
	) AS new_prices;

Result:

| new_prices        |
|-------------------|
| (3.85,1.38,0.83)  |

This example simulates a 10% price increase for a taco and its extras. The original prices (3.50 for a basic taco, 1.25 for extra cheese, and 0.75 for extra salsa) are multiplied by 1.1 to calculate the new prices after the increase.

​
tupleDivideByNumber

Divides each element of a tuple by a given number.

Syntax:

tupleDivideByNumber(tuple, number)

Arguments:

  • tuple (Tuple): A tuple containing numeric values.
  • number (numeric): The divisor.

Returns:

  • A tuple with each element divided by the given number. (Tuple).
  • If the tuple contains non-numeric values, they will be converted to numbers before division.
  • Division by zero will return inf.

Example:

SELECT
	tupleDivideByNumber((10, 20, 30), 5) AS divided_tuple;

Result:

| divided_tuple |
|---------------|
| (2,4,6)       |

In this example, each element of the tuple (10, 20, 30) is divided by 5, resulting in the tuple (2, 4, 6).

Example:

SELECT
	taco_order_id,
	taco_prices,
	tupleDivideByNumber(taco_prices, 2) AS half_price_tacos
FROM
	taco_orders;

Result:

| taco_order_id | taco_prices | half_price_tacos |
|---------------|-------------|------------------|
| 1             | (10,12,8)   | (5,6,4)          |
| 2             | (14,16,10)  | (7,8,5)          |

In this example, we calculate half-price tacos by dividing each taco price in the taco_prices tuple by 2.

​
tupleConcat

Combines multiple tuples into a single tuple.

Syntax:

tupleConcat(tuple1, tuple2, ...)

Arguments:

  • tuple1, tuple2, … (Tuple): Two or more tuples of any type.

Returns:

A new tuple containing all elements from the input tuples in the order they were provided.

Example:

SELECT
	tupleConcat((1, 'Carne Asada'), (2, 'Al Pastor'), (true, 'Salsa Verde')) AS taco_options;

Result:

| taco_options                                                 |
|--------------------------------------------------------------|
| (1, 'Carne Asada', 2, 'Al Pastor', true, 'Salsa Verde')      |

In this example, tupleConcat combines three tuples: one with a number and a taco filling, another with a different number and filling, and a third with a boolean and a salsa type. The result is a single tuple containing all these elements.

This function is useful for combining data from different sources or for creating more complex tuple structures from simpler ones.

Syntax:

tupleIntDiv(tuple_num, tuple_div)

Arguments:

  • tuple_num (Tuple): Tuple of numerators. Tuple of numeric type.
  • tuple_div (Tuple): Tuple of divisors. Tuple of numeric type.

Returns:

  • A tuple containing the results of integer division. Tuple of integer values.
  • If either tuple_num or tuple_div contain non-integer values, the result is calculated by rounding to the nearest integer for each non-integer numerator or divisor.
  • Throws an error for division by zero.

Example:

SELECT
  tupleIntDiv((15, 10, 5), (3, 2, 2)) AS result;

Result:

| result  |
|---------|
| (5,5,2) |

In this example:

  • 15 / 3 = 5
  • 10 / 2 = 5
  • 5 / 2 = 2 (integer division)

This function is useful for performing element-wise integer division on tuples, which can be handy in various data processing scenarios, such as calculating ratios or normalizing data in tuple format.

​
tupleIntDivOrZero

Performs integer division of corresponding elements in two tuples, returning a tuple of the quotients. Unlike tupleIntDiv, this function returns 0 for division by zero instead of throwing an error.

Syntax:

tupleIntDivOrZero(tuple_num, tuple_div)

Arguments:

  • tuple_num (Tuple): Tuple of numerators. Tuple of numeric type.
  • tuple_div (Tuple): Tuple of divisors. Tuple of numeric type.

Returns:

  • A tuple containing the results of integer division. Tuple of integer values.
  • If either tuple_num or tuple_div contain non-integer values, the result is calculated by rounding to the nearest integer for each non-integer numerator or divisor.
  • Returns 0 for elements where the divisor is 0.

Example:

SELECT
	tupleIntDivOrZero((15, 10, 5), (3, 0, 2)) AS result;

Result:

| result   |
|----------|
| (5,0,2)  |

In this example:

  • 15 / 3 = 5
  • 10 / 0 = 0 (division by zero)
  • 5 / 2 = 2 (integer division)

This function is particularly useful when you need to perform element-wise division on tuples and want to handle potential division by zero gracefully without causing errors.

​
tupleIntDivByNumber

Performs integer division of each element in a tuple by a given number.

Syntax:

tupleIntDivByNumber(tuple_num, div)

Arguments:

  • tuple_num (Tuple): Tuple of numerator values. Tuple of numeric type.
  • div (numeric): The divisor value. Numeric type.

Returns:

  • A tuple containing the results of integer division. Tuple of integer values.

Implementation details:

  • If either tuple_num or div contain non-integer values, the result is calculated by rounding to the nearest integer for each non-integer numerator or divisor.
  • An error will be thrown for division by 0.

Example:

SELECT
  tupleIntDivByNumber((15, 10, 5), 3) AS result;

Result:

| result  |
|---------|
| (5,3,1) |

In this example, each element of the tuple (15, 10, 5) is divided by 3, resulting in (5, 3, 1).

Example:

SELECT
	tupleIntDivByNumber(
		(
			toUInt32(length('carnitas')),
			toUInt32(length('al pastor')),
			toUInt32(length('barbacoa'))
		),
		3
	) AS taco_name_length_thirds;

Result:

| taco_name_length_thirds |
|-------------------------|
| (2,3,2)                 |

This query divides the lengths of taco filling names by 3, showing how many “thirds” each name length represents.

​
tupleIntDivOrZeroByNumber

Performs integer division of a tuple of numerators by a given denominator, and returns a tuple of the quotients. It does not throw an error for division by zero, but instead returns 0 for those cases.

Syntax:

tupleIntDivOrZeroByNumber(tuple_num, div)

Arguments:

  • tuple_num (Tuple): Tuple of numerator values. Tuple of numeric type.
  • div (numeric): The divisor value.

Returns:

  • A tuple of the quotients of tuple_num and div. Tuple of integer values.
  • Returns 0 for quotients where the divisor is 0.

Implementation details:

  • If either tuple_num or div contain non-integer values, the result is calculated by rounding to the nearest integer for each non-integer numerator or divisor.

Examples:

SELECT
  tupleIntDivOrZeroByNumber((15, 10, 5), 5);

Result:

| tupleIntDivOrZeroByNumber((15, 10, 5), 5) |
|-------------------------------------------|
| (3,2,1)                                   |

Example dividing by zero:

SELECT
  tupleIntDivOrZeroByNumber((15, 10, 5), 0);

Result:

| tupleIntDivOrZeroByNumber((15, 10, 5), 0) |
|-------------------------------------------|
| (0,0,0)                                   |

This function is particularly useful when you need to perform division operations on tuples of values and want to handle division by zero gracefully without throwing errors.

​
tupleModulo

Calculates the element-wise modulo operation between two tuples of the same size.

Syntax:

tupleModulo(tuple_num, tuple_mod)

Arguments:

  • tuple_num (Tuple): Tuple of numerator values. Tuple of numeric type.
  • tuple_mod (Tuple): Tuple of modulus values. Tuple of numeric type.

Returns:

  • A tuple containing the remainders of division between corresponding elements of tuple_num and tuple_mod. Tuple of non-zero integer values.

Exception:

  • Throws an error if division by zero occurs.

Example:

SELECT
    tupleModulo((15, 10, 5), (4, 3, 2)) AS taco_remainders;

Result:

| taco_remainders |
|-----------------|
| (3,1,1)         |

In this example, we calculate the remainders when dividing taco quantities by different serving sizes:

  • 15 tacos divided into groups of 4 leaves a remainder of 3
  • 10 tacos divided into groups of 3 leaves a remainder of 1
  • 5 tacos divided into groups of 2 leaves a remainder of 1

This function is useful for operations involving cyclic or periodic data, such as calculating remaining inventory after packaging tacos into different sized orders.

​
tupleModuloByNumber

Calculates the element-wise modulo of a tuple by a number.

Syntax:

tupleModuloByNumber(tuple_num, div)

Arguments:

  • tuple_num (Tuple): Tuple of numeric values.
  • div (numeric): The divisor value.

Returns:

  • A tuple containing the element-wise modulo results. Tuple of numeric values.

Example:

SELECT
  tupleModuloByNumber((15, 10, 5), 3) AS modulo_results;

Result:

| modulo_results |
|----------------|
| (0,1,2)        |

In this example:

  • 15 % 3 = 0
  • 10 % 3 = 1
  • 5 % 3 = 2

The function calculates the modulo for each element in the input tuple using the provided divisor.

An error is thrown if division by zero occurs.

This function is useful for performing element-wise modulo operations on tuples, which can be handy in various data processing and analysis tasks involving cyclic or periodic data.

​
flattenTuple

Flattens a nested named tuple into a single-level tuple.

Syntax:

flattenTuple(input)

Arguments:

  • input (Tuple): A nested named tuple to flatten.

Returns:

A flattened tuple where elements are paths from the original input. (Tuple).

This function can be used to select all paths from a nested tuple as separate columns.

Example:

CREATE TABLE taco_orders (
	order Tuple(
		id UInt32,
		items Nested(
			name String,
			quantity UInt32
		),
		total Tuple(
			amount Decimal(10,2),
			currency String
		)
	)
) ENGINE = Memory;

INSERT INTO taco_orders VALUES (
	(1, [('Carne Asada', 2), ('Al Pastor', 3)], (25.50, 'USD'))
);

SELECT flattenTuple(order) FROM taco_orders;

Result:

| flattenTuple(order)                                       |
|-----------------------------------------------------------|
| (1,['Carne Asada','Al Pastor'],[2,3],25.50,'USD')         |

In this example, the nested order tuple is flattened into a single-level tuple, making it easier to access individual elements or use them in further calculations.

Was this page helpful?

Time SeriesType Conversion