Querying VARIANT with Dot Notation

Snowflake · Semi-Structured Data Deep Dive

Theory

A VARIANT column holds a whole JSON document, and you read fields from it with path notation. A colon goes from the column into the first level (response:order_id), dots go deeper (response:customer.address.city) and square brackets read array elements or awkward keys (response:items[0], response['customer']['name']). The result is still VARIANT, so you cast it with :: to get a normal type, for example response:customer.name::STRING.

Without the cast, strings come back with JSON double quotes, like "Pune". Field names in the path are case-sensitive, even though Snowflake column names are not, and a path that does not exist returns NULL instead of an error.

Why we use it

Path notation lets you query JSON directly where it landed, without first designing tables for every field. This makes it fast to explore new sources and to pull out only the fields you need.

When to use it

Use path notation for single fields and fixed array positions. Use FLATTEN when you need one row per array element. For heavily used fields, extract them into typed columns or a view so queries stay simple and prune well.

Dot Notation Basics

data:customer.name reaches into a nested object field, and data:items[0].sku indexes into an array element — both return VARIANT, so you typically cast the result with ::STRING or ::NUMBER.

PARSE_JSON and TRY_PARSE_JSON

PARSE_JSON converts a JSON-formatted string into an actual VARIANT value; TRY_PARSE_JSON does the same but returns NULL instead of erroring on malformed input, useful for messy source data.

Building JSON Back Up

OBJECT_CONSTRUCT and ARRAY_CONSTRUCT do the reverse, assembling VARIANT objects and arrays from relational columns — handy when exporting relational data as JSON.

How it works

  1. VARIANT column holds JSON
  2. Navigate with col:field, dots for nesting, [n] for arrays
  3. Does the path exist in this row? Yes: Return the value as VARIANT. No: Return NULL.
  4. Cast with ::STRING, ::NUMBER or ::DATE
  5. Use the typed value in filters, joins and output

Example

SELECT
  response:order_id::STRING AS order_id,
  response:customer.name::STRING AS customer_name,
  response:customer.address.city::STRING AS city
FROM api_responses;

-- Bracket notation (same result)
SELECT response['customer']['name']::STRING AS name FROM api_responses;

-- Array element by index + size
SELECT response:items[0]:product::STRING AS first_item,
       ARRAY_SIZE(response:items) AS item_count
FROM api_responses;

-- FLATTEN the items array into rows
SELECT response:order_id::STRING AS order_id,
       f.value:product::STRING AS product, f.value:price::FLOAT AS price
FROM api_responses, LATERAL FLATTEN(input => response:items) f;

Key points

Snowflake topics

Snowflake Fundamentals

Warehouses & Performance

Staging & SnowSQL

Tables & Data Handling

Cloud Integrations

Storage & Optimization

Security & Governance

Views, Pipelines & Automation

Data Types & SQL Fundamentals

Semi-Structured Data Deep Dive

Advanced Features

Data Sharing & Collaboration

Cost Management & Monitoring

Databases, Schemas & Objects

Performance Deep Dive

Advanced Security & Governance

Stored Procedures & UDFs

Ecosystem & Integrations

SnowPro Certification Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Mastering Snowflake

The modern data cloud, from architecture to SnowPro certification. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice Snowflake SQL Sharpen the SQL you use in Snowflake: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Snowflake  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Unlike the SQL and Python courses, Snowflake needs a real account — there's no in-browser engine for it. Click Copy above, then paste it into a free Snowflake trial (30 days, no card required) and run it in Snowsight.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic