Skip to main content

🧬 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​

FeatureUse Case Example
VARIANTAny flexible JSON or raw event payload
OBJECT inside VARIANTStructured nested details (like metadata)
ARRAY inside VARIANTLists 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