Querying JSON in Snowflake: VARIANT, Path Notation and FLATTEN

1.Why semi-structured data lands in a VARIANT

A relational table needs its schema defined before any data arrives. JSON doesn't work that way. New attributes can appear at any time, two records of the same kind can carry different keys, and values nest to any depth. The Snowflake documentation describes semi-structured data as data that "does not require a prior definition of a schema and can constantly evolve".

Snowflake handles this with three data types that nest inside each other. An OBJECT is a set of key-value pairs, which the docs compare to a "dictionary" or "map". An ARRAY is an ordered list. A VARIANT can hold either of these, or any scalar. The two containers always hold VARIANTs, so a hierarchy is built like this: an OBJECT whose value is a VARIANT that wraps an ARRAY, whose cells are VARIANTs that wrap OBJECTs, and so on as deep as the data goes.

You don't usually build this structure by hand. When you load a format Snowflake recognises and parses (JSON, Avro, Parquet or ORC), the data is converted into this internal representation automatically. You can store a whole document in one VARIANT column, or split chosen parts into separate typed columns. The usual pattern sets the column type to VARIANT and declares the input format in the file format of the COPY command:

CREATE TABLE my_table (my_variant_column VARIANT); COPY INTO my_table ... FILE FORMAT = (TYPE = 'JSON') ...

Sources1

2.Turning JSON text into a VARIANT with PARSE_JSON

JSON often arrives as plain text: a VARCHAR column, a string from an API, or a value in a VALUES list. Path notation can't reach inside a string, so first you convert it. PARSE_JSON "Interprets an input string as a JSON document, producing a VARIANT value." TO_JSON goes the other way, from VARIANT back to a string. The two are only almost reciprocal, because TO_JSON doesn't preserve key order or whitespace.

Two other edge cases come up in exam questions. First, SQL NULL and JSON null are different things. PARSE_JSON(NULL) returns SQL NULL, but PARSE_JSON('null') returns a valid VARIANT that contains a JSON null. Second, numbers are typed as they're parsed. A plain decimal keeps exact precision, "treating 123.45 as NUMBER(5,2), not as a DOUBLE value", while scientific notation becomes DOUBLE. JSON has no native date or timestamp, so those values stay strings until you convert them. The table below shows the TYPEOF results from the PARSE_JSON reference example.

Duplicate keys are the last thing to watch for. By default PARSE_JSON is strict and rejects an object that repeats a key, with an error such as "duplicate object attribute". If you pass the optional 'd' parameter, the duplicates are accepted and only the last value for each repeated key is kept.

INSERT INTO vartab
SELECT column1 AS n, PARSE_JSON(column2, ' ? ') AS v
  FROM VALUES (10, '{ "a" : "123", "b" : "456", "a": "789"} ')
     AS vals;

Sources2

3.Traversing JSON with colon, dot and bracket notation

Once the data is in a VARIANT, you navigate it with path operators. A colon joins the column to the first-level key, as in src:dealership. From there you go deeper with dot notation (src:salesperson.name) or bracket notation with single-quoted names (src['salesperson']['name']). The two notations return the same result.

SELECT src:salesperson.name
    FROM car_sales
    ORDER BY 1;

SELECT src['salesperson']['name']
    FROM car_sales
    ORDER BY 1;

There are three rules to remember. First, case: the column name is case-insensitive but element names are case-sensitive, so SRC:salesperson.name works and SRC:Salesperson.Name does not. Second, quoting: a key that isn't a valid SQL identifier, such as one containing a space or starting with a digit, must be wrapped in double quotes, as in src:"company name". Third, output type: the result is still a VARIANT, which is why the docs show values like "Frank Beasley" in double quotes. To get a plain SQL value for a consumable table, cast it, for example value:name::string.

Sources3

4.Expanding arrays into rows with LATERAL FLATTEN

Path notation gives you one value per row. When a key holds an array, like the customer or vehicle arrays in car_sales, you usually want one row per element. That's what FLATTEN does. The docs call it "a table function that produces a lateral view of a VARIANT, OBJECT, or ARRAY column". With the LATERAL modifier, each output row is joined back to the source row it came from, so columns from the original table repeat once for each element.

SELECT
  value:name::string as "Customer Name",
  value:address::string as "Address"
  FROM
    car_sales
  , LATERAL FLATTEN(INPUT => SRC:customer);

FLATTEN always returns the same fixed set of columns. The query above reads VALUE and then applies path notation to it. For a single-level array, TABLE(FLATTEN(...)) and LATERAL FLATTEN(...) return the same result. For nested arrays, such as extras inside vehicle, you chain FLATTEN calls, and LATERAL is what lets each call reference the output of the one before. The MODE argument limits expansion to 'OBJECT', 'ARRAY' or 'BOTH' (the default). A recursive setting, off by default, expands every sub-element instead of only the one at the given path.

Sources34

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.JSON paths are case-insensitive like the rest of Snowflake SQL, so src:Vehicle and src:vehicle are interchangeable.Why is that wrong?

   Only the column name is case-insensitive. JSON element names must match the key's case exactly.

   Covered in Traversing JSON with colon, dot and bracket notation

2. 2.PARSE_JSON quietly keeps the last value when an object repeats a key.Why is that wrong?

   Strict mode is the default, and repeated keys raise an error. Only the 'd' parameter makes PARSE_JSON keep a single instance with the last value.

   Covered in Turning JSON text into a VARIANT with PARSE_JSON