Spread

Overview

A spread or ... expression is used 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 arguments 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’

This is the equivalent of concat(‘Once’, ‘ ’, ‘upon’, ‘ ’, ‘a’, ‘ ’, ‘time’)

We can also convert a column of array<string>, representing words in sentences, to a column of string type containing sentences. Let's say we start with the following column:

If we write: SELECT concat(...map(lambda x : concat(x, ‘ ’), sentences)) as sentences then our column will look like:

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

Or, let's say I am 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. If I want to see the highest price for a product in the Max_Price attribute, I would write SELECT *, Prices, greatest(...Prices) AS Max_Price;

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