Expansion operators

Expansion operators expand a query expression that represents a list into the individual values in the list. Currently, the spread operator (**) is the only expansion operator supported by Snowflake.

The spread operator expands an array into a list of individual values. This operator is useful for the following use cases:

Syntax

** <array>
Copy

Limitations

  • The input must be an array of constant values, which can be an array of literal values or a bind variable that represents an array of literal values.

  • Each value in a semi-structured array is of type VARIANT. A VARIANT value can contain a value of any other data type. The spread operator supports the following data types for the value stored in the VARIANT value:

  • User-defined functions and stored procedures written in languages other than SQL can’t use the spread operator.

  • Expanding very large arrays with the spread operator might degrade performance.

Examples

Some of the examples use the data the following table:

Create a table and insert data:

CREATE OR REPLACE TABLE spread_demo (col1 INT, col2 VARCHAR);

INSERT INTO spread_demo VALUES
  (1, 'a'),
  (2, 'b'),
  (3, 'c'),
  (4, 'd'),
  (5, 'e');

SELECT * FROM spread_demo;
Copy
+------+------+
| COL1 | COL2 |
|------+------|
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
+------+------+

The following examples use the spread operator.

Expand an array of literal values in an IN clause

Expand an array of numbers using the spread operator in a query on the spread_demo table created previously:

SELECT * FROM spread_demo
  WHERE col1 IN (** [3, 4])
  ORDER BY col1;
Copy
+------+------+
| COL1 | COL2 |
|------+------|
|    3 | c    |
|    4 | d    |
+------+------+

Expand an array of strings using the spread operator:

SELECT * FROM spread_demo
  WHERE col2 IN (** ['b', 'd'])
  ORDER BY col1;
Copy
+------+------+
| COL1 | COL2 |
|------+------|
|    2 | b    |
|    4 | d    |
+------+------+

Use an IN clause in a query with a mix of INTEGER values and expanded array values:

SELECT * FROM spread_demo
  WHERE col1 IN (** [1, 2], 4, 5)
  ORDER BY col1;
Copy
+------+------+
| COL1 | COL2 |
|------+------|
|    1 | a    |
|    2 | b    |
|    4 | d    |
|    5 | e    |
+------+------+

Expand an array of literal values in a system-defined function call

Expand an array of strings in a call to the COALESCE function:

SELECT COALESCE(** [NULL, NULL, 'my_string_1', 'my_string_2']) AS first_non_null;
Copy
+----------------+
| FIRST_NON_NULL |
|----------------|
| my_string_1    |
+----------------+

Expand an array of numbers in a call to the GREATEST function:

SELECT GREATEST(** [1, 2, 5, 4, 5]) AS greatest_value;
Copy
+----------------+
| GREATEST_VALUE |
|----------------|
|              5 |
+----------------+

Use the spread operator with a bind variable in a SQL user-define function

Create a SQL user-defined function that uses the spread operator. The function takes an array as an argument and then expands the array values to query the spread_demo table created previously:

CREATE OR REPLACE FUNCTION spread_function_demo(col_1_values ARRAY)
  RETURNS TABLE(
    col1 INT,
    col2 VARCHAR)
AS
$$
   SELECT * FROM spread_demo
     WHERE col1 IN (** col_1_values)
     ORDER BY col1
$$;
Copy

Query the table using the function:

SELECT * FROM TABLE(spread_function_demo([1, 3, 5]));
Copy
+------+------+
| COL1 | COL2 |
|------+------|
|    1 | a    |
|    3 | c    |
|    5 | e    |
+------+------+

Use the spread operator with a bind variable in a Snowflake Scripting stored procedure

Create a Snowflake Scripting stored procedure that uses the spread operator. The stored procedure takes an array as an argument and then expands the array values in a bind variable to query the spread_demo table created previously:

CREATE OR REPLACE PROCEDURE spread_sp_demo(col_1_values ARRAY)
  RETURNS TABLE(
    col1 INT,
    col2 VARCHAR)
  LANGUAGE SQL
AS
$$
DECLARE
  res RESULTSET;
BEGIN
  res := (SELECT * FROM spread_demo
     WHERE col1 IN (** :col_1_values)
     ORDER BY col1);
  RETURN TABLE(res);
END;
$$;
Copy

Call the stored procedure:

CALL spread_sp_demo([2, 4]);
Copy
+------+------+
| COL1 | COL2 |
|------+------|
|    2 | b    |
|    4 | d    |
+------+------+
Language: English