What is Array in Snowflake: Definition and Usage
ARRAY is a data type that stores an ordered list of elements, which can be of any supported data type. It allows you to group multiple values into a single column, making it easy to handle collections of data within a table.How It Works
Think of an ARRAY in Snowflake like a row of mailboxes, where each mailbox holds a value and the order matters. You can store multiple values together in one place, and access them by their position, just like checking the first or second mailbox.
Under the hood, Snowflake stores arrays as a single column but keeps track of each element's position. This lets you easily add, remove, or query individual elements without needing separate columns for each value.
Example
This example shows how to create a table with an ARRAY column, insert data, and query elements from the array.
CREATE OR REPLACE TABLE fruits_table ( id INT, fruits ARRAY ); INSERT INTO fruits_table (id, fruits) VALUES (1, ARRAY_CONSTRUCT('apple', 'banana', 'cherry')), (2, ARRAY_CONSTRUCT('orange', 'grape')); SELECT id, fruits, fruits[0] AS first_fruit FROM fruits_table;
When to Use
Use ARRAY when you want to store multiple related values in one column without creating many separate columns. This is helpful for data like tags, lists of items, or multiple phone numbers.
For example, an e-commerce site might store a list of product colors or sizes in an array. It simplifies queries and keeps your table structure clean.
Key Points
- ARRAY stores ordered collections of values in one column.
- Elements can be accessed by their index starting at 0.
- Supports any Snowflake data type inside the array.
- Useful for grouping related data without extra columns.
