User GuidesAPI ReferenceRelease Notes
Doc HomeHelp CenterLog In

Spread

Spread expressions pass an array as the arguments of a function that can take a varying number of arguments.

You use a spread (or ...) expression to pass an array as the arguments of a vararg function. A vararg function is a function that can take a varying number of arguments, such as greatest(), which returns the greatest value out of all the arguments it is supplied.

This action of taking the values out of an array and feeding them as arguments is called unpacking. The first element in the array gets unpacked as the first argument, the second as the second argument, and so on. This is done by putting the spread operator, ..., inside of a function call before the array.

Examples

Say you have a column where the values in each row are Array<string>, representing words that make up a sentence:

get(sentences, 0) = [‘Once’, ‘ ’, ‘upon’, ‘ ’, ‘a’, ‘ ’, ‘time’]

Applying the transformation:
SELECT concat(...sentences) as sentences;

Will output the value of get(sentences, 0) as ‘Once upon a time’

Using ... is the equivalent of writing concat(‘Once’, ‘ ’, ‘upon’, ‘ ’, ‘a’, ‘ ’, ‘time’)

You can also convert a column of array<string>, representing words in sentences, to a column of string type containing sentences. This example starts with the following column:

Sentences
[‘Two’, ‘roads’, ‘diverged’, ‘in’, ‘a’, ‘yellow’, ‘wood,’]
[‘And’, ‘sorry’, ‘I’, ‘could’, ‘not’, ‘travel’, ‘both’]
[‘And’, ‘be’, ‘one’, ‘traveler,’, ‘long’, ‘I’, ‘stood’]
[‘And’, ‘looked’, ‘down’, ‘one’, ‘as’, ‘far’, ‘as’, ‘I’, ‘could’]

If you write: SELECT concat(...map(lambda x : concat(x, ‘ ’), sentences)) as sentences then the result is the following.

Sentences
‘Two roads diverged in a yellow wood, ’
‘And sorry I could not travel both ’
‘And be one traveler, long I stood ’
‘And looked down one as far as I could ’

map(lambda x : concat(x, ‘ ’), sentences) takes each String in the array and adds a space to the end of it, then unpacks that and concatenates all values into a single string.

Or, let's say you are working with an attribute where each row has an array of prices. These prices represent the price the product is sold at in three different location. To see the highest price for a product in the Max_Price attribute, you can write SELECT *, Prices, greatest(...Prices) AS Max_Price;

PricesMax_Price
[33.02, 30.50, 32.99]33.02
[15.99, 15.99, 16.00]16.00
[25.99, 30.15, 28.99]30.15

Here are a few more examples:

  • least(... array(2, 1, 3)) = least(2, 1, 3) = 1
  • array(...some_array) = some_array
  • coalesce(...array(null, null, 1, 2)) = 1
  • concat(...array(‘this_’, ‘is’, ‘_spread’)) = ‘this_is_spread’
  • hash(...array(20, ‘twenty’, array(3, 9, 8))) = -1910362911