New SQL Array Functions for Snowflake

How Snowflake is bringing new Options when working with Semi-Structured Data

Since I haven’t reported anything about Snowflake for a long time, there are now interesting news about SQL functionalities if you work with arrays, so besides structured data also with semi-structured data.

With other updates this May, Snowflake has also implemented the following functions when working with arrays[1]:

So here is a small example with the ARRAY_GENERATE_RANGE function, which is very handy if you have to generate dummy data for example[2]:

SELECT ARRAY_GENERATE_RANGE(2, 6);

+----------------------------+
| ARRAY_GENERATE_RANGE(2, 6) |
|----------------------------|
| [                          |
|   2,                       |
|   3,                       |
|   4,                       |
|   5                        |
| ]                          |
+----------------------------+

This example returns an ARRAY containing a range of numbers starting from 2 and ending before 6.

Also an inserting new function is the ARRAY_REMOVE_AT function. With that, you can remove certain values within the array. This could be interesting for Data Analysts and Scientists during data transformations as well as for Snowflake Data Engineers when implementing and transforming data[3].

+-------------------------------+
| ARRAY_REMOVE_AT([3, 5, 8], 0) |
|-------------------------------|
| [                             |
|   5,                          |
|   8                          |
| ]                             |
+-------------------------------+

In this example, I used the function to remove the first element. In this case, it would be the 3.

If you want to dive further into the matter and learn more about the functions and the parameters you can use, feel free to use the official docs of Snowflake linked below. I hope that this short article could help you with telling that Snowflake has extended the functionality for working with arrays and that you can use the example SQLs as a blueprint.

Sources and Further Readings

[1] Snowflake, SQL Updates (2023)

[2] Snowflake, ARRAY_GENERATE_RANGE (2023)

[3] Snowflake, ARRAY_REMOVE_AT (2023)