How to Parse JSON in Snowflake: A Practical Guide for Teams

### 1. Manual ways to parse JSON in Snowflake (the traditional route)Before we bring in automation or agents, it helps to master the core primitives Snowflake gives you for JSON. Think of these as the building blocks your team – and your AI agent – will reuse.#### 1.1 Store JSON as VARIANT and parse with PARSE_JSON1. Land your raw JSON as text in a VARCHAR column (often via COPY INTO or a staged file).2. Create a table with a VARIANT column: ```sql CREATE OR REPLACE TABLE events_raw ( id STRING, payload VARIANT ); ```3. When loading, call `PARSE_JSON` on the raw string: ```sql INSERT INTO events_raw (id, payload) SELECT $1 AS id, PARSE_JSON($2) AS payload FROM @my_stage/raw_events; ```4. Now `payload` is a JSON document in VARIANT form. You can query it with `payload:customer.id` or `payload['event_type']`.Official docs: [PARSE_JSON](https://docs.snowflake.com/en/sql-reference/functions/parse_json).#### 1.2 Safely handle bad JSON with TRY_PARSE_JSONBad rows from webhooks or malformed logs can crash a whole load.1. Swap `PARSE_JSON` for `TRY_PARSE_JSON`: ```sql INSERT INTO events_raw (id, payload) SELECT $1, TRY_PARSE_JSON($2) FROM @my_stage/raw_events; ```2. Any invalid JSON returns `NULL` instead of an error.3. Add a downstream quality check: ```sql SELECT COUNT(*) AS bad_rows FROM events_raw WHERE payload IS NULL; ```Docs: [TRY_PARSE_JSON](https://docs.snowflake.com/en/sql-reference/functions/try_parse_json).#### 1.3 Projecting fields with dot and bracket notationOnce JSON is in VARIANT:1. Use dot notation for standard keys: ```sql SELECT payload:customer.id::STRING AS customer_id, payload:event_type::STRING AS event_type, payload:properties.revenue::NUMERIC(10,2) AS revenue FROM events_raw; ```2. Use bracket notation if keys contain spaces or non-standard characters: ```sql SELECT payload['campaign name']::STRING AS campaign_name FROM events_raw; ```Snowflake JSON navigation: [Semi-structured data](https://docs.snowflake.com/en/user-guide/semistructured-overview).#### 1.4 Flattening arrays with LATERAL FLATTENMarketing and product events often store arrays: `line_items`, `impressions`, `touchpoints`.1. Assume `payload:line_items` is an array.2. Use `LATERAL FLATTEN`: ```sql SELECT e.id, li.value:sku::STRING AS sku, li.value:price::NUMBER AS price FROM events_raw e, LATERAL FLATTEN(input => e.payload:line_items) li; ```3. Each array element becomes a row.Docs: [FLATTEN](https://docs.snowflake.com/en/sql-reference/functions/flatten) and [LATERAL JOIN](https://docs.snowflake.com/en/sql-reference/constructs/join-lateral).#### 1.5 Handling nulls and duplicate keysSnowflake treats empty strings and JSON `null` differently.1. `PARSE_JSON('')` returns SQL `NULL`.2. `PARSE_JSON('null')` returns a JSON null inside VARIANT.3. For rare cases with duplicate keys, use the `d` parameter: ```sql SELECT PARSE_JSON('{"a":1,"a":2}', 'd'); ``` Snowflake keeps the last value.### 2. No-code and low-code methods with automation toolsIf you run an agency or revenue team, you might not want your marketers writing SQL. You can still harness Snowflake’s JSON power via no-code tooling.#### 2.1 ELT tools that auto-generate PARSE_JSON logicModern ELT platforms (Fivetran, Airbyte, Stitch and others) automatically ingest JSON APIs and write `PARSE_JSON` under the hood.Typical setup:1. Connect your source (Meta Ads, Google Ads, HubSpot, Shopify) and Snowflake.2. Choose tables or endpoints; the tool infers schemas from sample JSON.3. Behind the scenes it loads raw JSON and builds views that: * Parse VARCHAR with `PARSE_JSON`. * Use `FLATTEN` to explode arrays. * Cast to proper Snowflake types.Pros:- Little to no SQL.- Schemas auto-update as APIs evolve.Cons:- Less control over naming and modeling.- Harder to encode campaign-specific business rules.#### 2.2 Visual data modeling toolsTools like dbt Cloud’s GUI or other visual modelers can sit on top of Snowflake and let non-engineers tweak JSON models.Pattern:1. Start from a raw VARIANT table.2. Add a new model and visually pick fields via a tree of JSON keys.3. The tool emits SQL using `payload:field` and `FLATTEN`, then compiles to Snowflake.This lets a marketing ops manager extend models (add a new property from an ad platform payload) without hand-writing JSON navigation.#### 2.3 Workflow tools triggering Snowflake tasksBusiness owners often want: “When new JSON events arrive, refresh my dashboards automatically.”You can:1. Set up a Snowflake TASK to run parsing SQL on a schedule.2. Trigger that task via an external orchestrator or webhook.3. Orchestrate end-to-end: load JSON → parse with `PARSE_JSON` → aggregate KPIs → email a report.Snowflake tasks: [TASK docs](https://docs.snowflake.com/en/user-guide/tasks-intro).### 3. Scaling JSON parsing with AI agentsManual SQL and no-code tools get you going, but they still rely on humans to maintain patterns. As your business adds more platforms and campaigns, JSON parsing becomes a repetitive, error-prone chore.Simular’s AI computer agents are designed to automate exactly this kind of digital workflow: opening Snowflake worksheets, editing SQL, running queries, and integrating results with the rest of your stack.#### 3.1 Agent-driven template generation for new JSON sourcesImagine you onboard a new ad network that delivers logs as JSON files.An AI agent running on Simular Pro can:1. Open your browser, log into the Snowflake UI, and navigate to Worksheets.2. Paste in a proven parsing template that: * Uses `TRY_PARSE_JSON` to load raw events into VARIANT. * Applies `LATERAL FLATTEN` for key arrays (e.g., `ad_groups`, `line_items`). * Casts and renames fields into a clean reporting schema.3. Replace table and key names by reading sample JSON files from your storage.4. Execute the SQL, verify row counts and null rates, and save the worksheet.Pros:- Eliminates copy-paste and boilerplate SQL.- Ensures consistent field naming across many sources.Cons:- Requires careful initial instructions and guardrails.Learn more about Simular’s agents: [Simular Pro](https://www.simular.ai/simular-pro).#### 3.2 Continuous maintenance as schemas evolveAPIs and event schemas change quietly: a new field is added, data types shift, arrays become nested. Humans usually discover this only when dashboards break.An AI agent can:1. Periodically sample fresh JSON rows from your VARIANT tables.2. Compare keys and structures to a stored baseline.3. If it detects new fields, generate: * An updated parsing view that selects the new keys. * A short changelog in your team’s documentation or Slack.4. Run test queries to confirm that `PARSE_JSON` and `FLATTEN` still succeed (no type errors).This keeps your Snowflake environment aligned with real-world payloads without constant engineer attention.#### 3.3 End-to-end orchestration for non-technical teamsFor agencies and revenue teams, the ideal future state is simple: JSON in, dashboards out – with no one worrying about commas in SQL.A Simular AI agent can orchestrate the full workflow across desktop, browser, and cloud:1. Detect new JSON files or API loads.2. Log into Snowflake, run the appropriate `PARSE_JSON`/`TRY_PARSE_JSON` + `FLATTEN` models.3. Export aggregated tables to your BI tool or Google Sheets.4. Post a short summary in Slack: “Yesterday’s campaigns processed; 3K events parsed; 5 malformed rows quarantined.”Pros:- Frees analysts and marketers to focus on messaging and offers.- Production-grade reliability for long multi-step workflows.Cons:- Requires initial investment in designing the workflow the agent will follow.To see how these agents behave in real computer environments, explore the demos and docs at [Simular Pro](https://www.simular.ai/simular-pro) and learn about the research approach at [Simular About](https://www.simular.ai/about).