How to Use Variant Data Type in Snowflake Queries
In Snowflake,
VARIANT is a flexible data type that stores semi-structured data like JSON. You can query VARIANT columns using dot notation or functions like GET and GET_PATH to extract nested values in your SQL queries.Syntax
The VARIANT data type stores semi-structured data such as JSON, XML, or Avro. To query data inside a VARIANT column, use:
column_name:keyorcolumn_name.keyfor direct child keys.GET(column_name, 'key')function for extracting values.GET_PATH(column_name, ['key1', 'key2'])for nested keys.
These methods return VARIANT values that can be cast to specific types.
sql
SELECT variant_column:key AS value FROM table_name; SELECT GET(variant_column, 'key') AS value FROM table_name; SELECT GET_PATH(variant_column, ['key1', 'key2']) AS nested_value FROM table_name;
Example
This example shows how to create a table with a VARIANT column, insert JSON data, and query nested values using dot notation and functions.
sql
CREATE OR REPLACE TABLE example_variant (id INT, data VARIANT); INSERT INTO example_variant VALUES (1, PARSE_JSON('{"name": "Alice", "age": 30, "address": {"city": "NYC", "zip": "10001"}}')), (2, PARSE_JSON('{"name": "Bob", "age": 25, "address": {"city": "LA", "zip": "90001"}}')); -- Query name using dot notation SELECT id, data:name AS name FROM example_variant; -- Query nested city using GET_PATH SELECT id, GET_PATH(data, ['address', 'city']) AS city FROM example_variant;
Output
id | name
---+-------
1 | Alice
2 | Bob
id | city
---+-----
1 | NYC
2 | LA
Common Pitfalls
Common mistakes when querying VARIANT data include:
- Using incorrect dot notation for nested keys (only direct children work with dot notation).
- Not casting
VARIANTresults to the expected data type, causing unexpected output. - Confusing
GET(single key) withGET_PATH(nested keys).
Always verify the JSON structure and use appropriate functions for nested data.
sql
/* Wrong: Trying to access nested key with dot notation */ SELECT data.address.city FROM example_variant; /* Right: Use GET_PATH for nested keys */ SELECT GET_PATH(data, ['address', 'city']) AS city FROM example_variant;
Quick Reference
| Usage | Description | Example |
|---|---|---|
| Dot notation | Access direct child key | data:name |
| GET | Extract value by key | GET(data, 'name') |
| GET_PATH | Extract nested value by path | GET_PATH(data, ['address', 'city']) |
| PARSE_JSON | Convert JSON string to VARIANT | PARSE_JSON('{"key": "value"}') |
| Casting | Convert VARIANT to specific type | data:name::STRING |
Key Takeaways
Use dot notation or GET/GET_PATH functions to query VARIANT data in Snowflake.
Dot notation works only for direct child keys; use GET_PATH for nested keys.
Cast VARIANT results to the desired type for proper output.
Use PARSE_JSON to insert JSON data into VARIANT columns.
Check JSON structure carefully to avoid query errors.
