Bird
Raised Fist0
SnowflakeHow-ToBeginner · 4 min read

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:key or column_name.key for 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 VARIANT results to the expected data type, causing unexpected output.
  • Confusing GET (single key) with GET_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

UsageDescriptionExample
Dot notationAccess direct child keydata:name
GETExtract value by keyGET(data, 'name')
GET_PATHExtract nested value by pathGET_PATH(data, ['address', 'city'])
PARSE_JSONConvert JSON string to VARIANTPARSE_JSON('{"key": "value"}')
CastingConvert VARIANT to specific typedata: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.