🧬 Handling Nested JSON & Semi-structured Data in Snowflake
Snowflake supports storing and querying semi-structured data like JSON, Avro, XML, and Parquet using powerful types such as VARIANT, OBJECT, and ARRAY.
This section demonstrates how to store, load, and query real-world nested data structures.
📦 1. Table with Diverse Semi-structured Columns
CREATE OR REPLACE TABLE demo_semi_structured (
emp_id INT,
emp_name STRING,
salary NUMBER(10,2),
is_active BOOLEAN,
joining_date DATE,
current_ts TIMESTAMP,
preferences VARIANT, -- nested key-value
tags ARRAY, -- array of values
metadata OBJECT -- structured nested data
);
✅ Insert Sample Data
INSERT INTO demo_semi_structured
SELECT
1,
'Alice Johnson',
75000.50,
TRUE,
'2023-01-15',
CURRENT_TIMESTAMP(),
PARSE_JSON('{"theme": "dark", "notifications": true}'),
ARRAY_CONSTRUCT('tag1', 'tag2', 'tag3', 'Gopi4'),
OBJECT_CONSTRUCT('department', 'HR', 'location', 'NY');
🔍 Query Specific Nested Fields
SELECT
emp_name,
preferences:"theme" AS theme,
tags[3] AS fourth_tag,
metadata:"location" AS location
FROM demo_semi_structured;
🧱 2. Table for Nested Customer Orders
CREATE OR REPLACE TABLE nested_customers (
customer_data VARIANT
);
✅ Insert One-Level Nested JSON
INSERT INTO nested_customers
SELECT PARSE_JSON('{
"name": "John Doe",
"email": "john@example.com",
"address": {
"street": "123 Main St",
"city": "Chennai",
"country": "India"
},
"orders": [
{
"order_id": 101,
"items": [
{ "item_id": "A1", "qty": 2 },
{ "item_id": "B2", "qty": 1 }
]
},
{
"order_id": 102,
"items": [
{ "item_id": "C3", "qty": 3 }
]
}
]
}');
🔍 Query Nested Array: Flatten Orders
SELECT
customer_data:name::STRING AS customer_name,
orders.value:order_id::INT AS order_id
FROM nested_customers,
LATERAL FLATTEN(input => customer_data:orders) AS orders;
🔁 3. N-Level Nested JSON: Real-world Employee Data
CREATE OR REPLACE TABLE ext_tab_json_file_load (
customer_data VARIANT
);
✅ Insert Multi-level Nested JSON
INSERT INTO ext_tab_json_file_load
SELECT PARSE_JSON('[
{
"id": 1,
"name": "Anjali Sharma",
"email": "anjali.sharma@example.in",
"address": {
"street": "12 MG Road",
"city": "Bangalore",
"state": "Karnataka",
"pincode": "560001"
},
"education": {
"degree": "B.Tech",
"major": "Computer Science",
"university": "IIT Bombay"
},
"employment": {
"current": {
"company": "Infosys",
"designation": "Software Engineer"
},
"previous": [
{ "company": "TCS", "designation": "Intern" },
{ "company": "Wipro", "designation": "Junior Developer" }
]
}
},
{
"id": 2,
"name": "Ravi Kumar",
"email": "ravi.kumar@example.in",
"address": {
"street": "45 Anna Nagar",
"city": "Chennai",
"state": "Tamil Nadu",
"pincode": "600040"
},
"education": {
"degree": "MBA",
"major": "Finance",
"university": "IIM Ahmedabad"
},
"employment": {
"current": {
"company": "HDFC Bank",
"designation": "Financial Analyst"
},
"previous": [
{ "company": "ICICI Bank", "designation": "Associate" }
]
}
}
]');
🔍 Query: Flatten Records & Previous Employment
SELECT
rec.value:id::INT AS id,
rec.value:name::STRING AS name,
rec.value:employment.current.company::STRING AS current_company,
prev.value:company::STRING AS prev_company,
prev.value:designation::STRING AS prev_role
FROM ext_tab_json_file_load,
LATERAL FLATTEN(input => customer_data) AS rec,
LATERAL FLATTEN(input => rec.value:employment.previous) AS prev;
🌐 4. Generic Events Table (Flexible Log Capture)
CREATE OR REPLACE TABLE generic_events (
event_id INT,
event_payload VARIANT
);
INSERT INTO generic_events
SELECT 101, PARSE_JSON('{
"type": "LOGIN",
"user": {
"id": "U123",
"region": "South"
},
"metadata": {
"device": "mobile",
"app_version": "1.2.3"
}
}');
🔍 Query Nested Object Fields
SELECT
event_id,
event_payload:type::STRING AS event_type,
event_payload:user.id::STRING AS user_id,
event_payload:user.region::STRING AS user_region,
event_payload:metadata.device::STRING AS device,
event_payload:metadata.app_version::STRING AS app_version
FROM generic_events;
✅ Summary: When to Use Which Semi-structured Feature
| Feature | Use Case Example |
|---|---|
VARIANT | Any flexible JSON or raw event payload |
OBJECT inside VARIANT | Structured nested details (like metadata) |
ARRAY inside VARIANT | Lists of items (e.g., orders, tags) |
FLATTEN() | Expand arrays into rows |
Dot notation : | Access JSON fields (data:key) |
Cast :: | Convert JSON value to SQL data types |
🧠 Real-world Advantages
- ✅ Schema flexibility — no rigid column definitions
- 🔍 Deep insights from nested JSON logs or IoT data
- 📊 Simplifies ETL for complex, event-driven systems
- ⚡ Fast development with evolving data structures