Skip to content
Tutorials Center
My learning

Topics

Lesson 17 of 38 ยท Querying and Semi-structured Data

Semi-structured Data and the VARIANT Type

Store JSON, Avro, Parquet and XML in Snowflake with VARIANT, OBJECT and ARRAY. Load JSON, use colon and dot notation, cast values, and understand how it is stored.

Not all data fits neat rows and columns. Web events, API responses and application logs arrive as JSON documents whose fields differ from record to record. Snowflake can store such data as is in a column of type VARIANT and query it with SQL, so you do not need a separate document database or a rigid schema up front.

The semi-structured types

Type Holds
VARIANT Any value: object, array, string, number, boolean, null (up to 16 MB per value)
OBJECT Key-value pairs, like a JSON object
ARRAY An ordered list

A VARIANT column is the most common choice for raw JSON. It can sit alongside normal typed columns in one table.

SQL
-- Snowflake SQL (read-only)
CREATE OR REPLACE TABLE shop.raw.events (
  loaded_at TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP(),
  payload   VARIANT
);

INSERT INTO shop.raw.events (payload)
SELECT PARSE_JSON('{"user":{"id":7,"name":"Hana"},"event":"purchase","items":[{"sku":"A1","qty":2},{"sku":"B2","qty":1}],"total":42.5}');

PARSE_JSON turns text into a VARIANT. (INSERT ... VALUES cannot call it directly, so use INSERT ... SELECT.)

Loading JSON files

Load JSON with a JSON file format into a single VARIANT column. Each document, or each array element if STRIP_OUTER_ARRAY = TRUE, becomes one row:

SQL
-- Snowflake SQL (read-only)
COPY INTO shop.raw.events (payload)
FROM @shop.raw.landing/events/
FILE_FORMAT = (TYPE = JSON STRIP_OUTER_ARRAY = TRUE);

Parquet, Avro and ORC work the same way with their own file types. For COPY INTO details, see the loading chapter.

Querying with colon and dot notation

Use a colon to step into the first level and dots (or brackets) for deeper levels:

SQL
-- Snowflake SQL (read-only)
SELECT payload:event                    AS event,
       payload:user.name                AS user_name,
       payload:user:id::NUMBER          AS user_id,
       payload:items[0].sku::STRING     AS first_sku,
       payload:total::NUMBER(10,2)      AS total
FROM shop.raw.events;

Important details:

  • The result of a path is itself a VARIANT. A string comes back with quotes ("Hana") until you cast it with ::STRING. Always cast to the type you need.
  • Key names are case-sensitive: payload:Event and payload:event differ. Use bracket syntax for odd keys: payload['first-name'].
  • A missing path returns NULL, not an error.
  • Array positions start at 0.

What it looks like in the browser

SQLite has JSON functions, so you can practice the same ideas with slightly different syntax: json_extract(doc, '$.user.name') plays the role of payload:user.name.

SQL Stand-in: paths into a JSON document

Press Run (or Ctrl + Enter) to see the result here.

The second event has no items or total, and the query returns NULL for them. Snowflake behaves the same way: documents with different shapes live happily in one column.

Casting and type checks

Cast values with ::TYPE or functions (TO_NUMBER, TO_VARCHAR, TO_DATE). Check types with TYPEOF or the IS_ functions:

SQL
-- Snowflake SQL (read-only)
SELECT payload:total,                   -- 42.5 (VARIANT)
       TYPEOF(payload:total),           -- 'DECIMAL'
       TYPEOF(payload:items),           -- 'ARRAY'
       IS_ARRAY(payload:items),
       payload:user.id::NUMBER + 1
FROM shop.raw.events;

The browser has json_type:

SQL Stand-in: checking value types

Press Run (or Ctrl + Enter) to see the result here.

Building semi-structured values

You can also create objects and arrays in SQL:

SQL
-- Snowflake SQL (read-only)
SELECT OBJECT_CONSTRUCT('id', 1, 'name', 'Hana')          AS obj,
       ARRAY_CONSTRUCT(1, 2, 3)                           AS arr,
       OBJECT_INSERT(OBJECT_CONSTRUCT('a', 1), 'b', 2)    AS obj2,
       ARRAY_APPEND(ARRAY_CONSTRUCT(1, 2), 3)             AS arr2;

And turn query results into JSON with ARRAY_AGG and OBJECT_CONSTRUCT, a common way to prepare an API response.

How VARIANT is stored and performs

When data is loaded, Snowflake examines the keys and, for common keys used consistently in most documents, stores those values in separate, columnar sub-columns inside the micro-partitions. So filters and aggregates on a frequently present path such as payload:event prune partitions and read only that column, almost like a normal column.

Limits to remember:

  • Values of the same path should keep the same type across documents; mixed types (a number sometimes, a string other times) cannot be stored as an optimized sub-column.
  • Dates are stored as strings in JSON. Snowflake cannot optimize them as dates; cast them when you query, or better, extract them to a real date column.
  • Very deep or huge documents and rarely present keys are kept in a generic form and are slower to read.
  • A single value cannot exceed 16 MB.

Hybrid design: promote hot fields

A practical pattern is to keep the raw payload and also extract the few fields you filter or join on into real columns. Real columns have precise types, statistics and clear lineage:

SQL
-- Snowflake SQL (read-only)
CREATE OR REPLACE TABLE shop.clean.events AS
SELECT loaded_at,
       payload:event::STRING          AS event,
       payload:user.id::NUMBER        AS user_id,
       payload:total::NUMBER(10,2)    AS total,
       payload                        AS raw_payload
FROM shop.raw.events;

In the browser the same transformation is a plain CREATE TABLE AS:

SQL Stand-in: promote hot fields into real columns

Press Run (or Ctrl + Enter) to see the result here.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which type holds an arbitrary JSON value, object or array?

    VARIANT can hold any semi-structured value up to 16 MB.
  2. 2. What does payload:user.name return without a cast?

    Paths return VARIANT. Add ::STRING to get a plain string.
  3. 3. A query uses payload:Event but the key is event. What is returned?

    Missing paths yield NULL, and key names are case-sensitive.
  4. 4. Why promote frequently used JSON fields into real columns?

    Dates inside JSON are strings, and mixed types prevent optimization. Real columns avoid both problems.

Summary

  • VARIANT, OBJECT and ARRAY store JSON-like data without a fixed schema.
  • Load with a JSON (or Parquet, Avro) file format into a VARIANT column; use PARSE_JSON for text.
  • Query with col:key.subkey and [index]; cast with ::TYPE; keys are case-sensitive.
  • Consistent paths are stored in optimized sub-columns; mixed types and dates-as-strings are not.
  • Keep the raw document and extract hot fields into typed columns.

Next: FLATTEN and LATERAL