Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

How to Access Nested JSON in Snowflake: Simple Guide

In Snowflake, you can access nested JSON data using dot notation like json_column:key1.key2 or functions such as OBJECT_GET and FLATTEN. These methods let you extract values from deep inside JSON structures stored in VARIANT columns.
📐

Syntax

Snowflake stores JSON data in VARIANT columns. To access nested JSON fields, you can use:

  • Dot notation: Use json_column:key1.key2 to get nested keys.
  • OBJECT_GET: Function to get a value by key from an object.
  • FLATTEN: Table function to expand arrays or objects into rows.
sql
SELECT json_column:key1.key2 AS nested_value FROM table_name;
💻

Example

This example shows how to extract a nested value from JSON stored in a VARIANT column and how to flatten an array inside JSON.

sql
CREATE OR REPLACE TABLE example_json (data VARIANT);

INSERT INTO example_json VALUES
  (PARSE_JSON('{"user": {"name": "Alice", "contacts": ["email@example.com", "123-456-7890"]}}'));

-- Access nested field 'name'
SELECT data:user.name AS user_name FROM example_json;

-- Flatten the 'contacts' array
SELECT contact.value AS contact_info
FROM example_json,
LATERAL FLATTEN(input => data:user.contacts) AS contact;
Output
USER_NAME Alice CONTACT_INFO email@example.com 123-456-7890
⚠️

Common Pitfalls

Common mistakes when accessing nested JSON in Snowflake include:

  • Using incorrect key names or case sensitivity.
  • Trying to access array elements with dot notation instead of FLATTEN.
  • Not using PARSE_JSON when inserting JSON strings.

Always verify the JSON structure and use the right method for objects vs arrays.

sql
/* Wrong: Trying to access array element with dot notation */
SELECT data:user.contacts[0] AS first_contact FROM example_json;

/* Right: Use FLATTEN to access array elements */
SELECT contact.value AS first_contact
FROM example_json,
LATERAL FLATTEN(input => data:user.contacts) AS contact
LIMIT 1;
Output
FIRST_CONTACT email@example.com
📊

Quick Reference

MethodUsageDescription
Dot notationjson_column:key1.key2Access nested object keys directly
OBJECT_GETOBJECT_GET(json_column, 'key')Get value by key from JSON object
FLATTENLATERAL FLATTEN(input => json_column:key)Expand JSON arrays or objects into rows
PARSE_JSONPARSE_JSON('json_string')Convert string to VARIANT JSON type
✅

Key Takeaways

Use dot notation to access nested JSON keys in VARIANT columns.
Use FLATTEN to work with JSON arrays and expand them into rows.
Always parse JSON strings with PARSE_JSON before storing or querying.
Check JSON structure carefully to choose the right access method.
Avoid using dot notation for arrays; use FLATTEN instead.