Welcome To TechBrothersIT: Snowflake SQL Tutorial 86 - The VARIANT Type, TYPEOF, IS_NULL_VALUE

Snowflake SQL Tutorial 86 - The VARIANT Type, TYPEOF, IS_NULL_VALUE - Semi-Structured Data

Snowflake SQL · Episode 86

In this post we are going to cover the VARIANT type in Snowflake, along with TYPEOF and IS_NULL_VALUE.

This is where Level 8 starts, and Level 8 is about data that arrives without a schema. A VARIANT is the column it arrives in. One VARIANT column can hold a number on one row, a string on the next and a whole nested document on the one after that, and Snowflake will not complain about any of it.

That is the feature. It is also the problem, and the problem does not announce itself. We are going to spend most of this post on one column where the total comes out right and the query that looks up the row behind that total finds nothing.

We will start with four JSON documents and build it up step by step.

Every query below was run on a real Snowflake account, and every result you see is the output it gave back, errors included.

Episode 86 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page

Let's create some sample data
Let's read the same path on every row
So what can a VARIANT actually hold?
Now let's add that column up
Now let's filter on it instead
Missing is not the same as null
Let's build both cases in one row
Can a VARIANT column be NOT NULL?
What about a DEFAULT on a VARIANT column?
So which defaults do work?
What happens when we ask a VARIANT to do the wrong thing?
Can TRY_CAST save us?
Let's read an element out of an array
What if we want the type checked for us?
What about duplicate keys?
Nothing is missing, and three things are wrong
One last thing: those quotes in the grid

Let's create some sample data

We need a table with a VARIANT column in it. The script below makes one and puts four events into it, each one a JSON document.

Read the comment on event 3 before you run it. That row is awkward on purpose, and most of this post is about it.

Sample data

-- Sample data for Level 8. Run this once.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

CREATE OR REPLACE TABLE EVENTS_JSON (
    EVENT_ID  INT,
    PAYLOAD   VARIANT
);

-- PARSE_JSON cannot go in a VALUES list, so the rows come from a SELECT.
INSERT INTO EVENTS_JSON
SELECT 1, PARSE_JSON('{
  "event": "purchase",
  "amount": 4200,
  "user":   {"id": 101, "name": "Northwind Traders"},
  "device": {"os": "windows", "type": "desktop"},
  "items":  [{"sku": "A-100", "qty": 8, "price": 300},
             {"sku": "B-220", "qty": 4, "price": 450}],
  "tags":   ["priority", "repeat"]
}')
UNION ALL
SELECT 2, PARSE_JSON('{
  "event": "purchase",
  "amount": 890.5,
  "user":   {"id": 102, "name": "Blue Ridge Supply"},
  "device": {"os": "ios", "type": "mobile"},
  "items":  [{"sku": "C-310", "qty": 3, "price": 296.83}],
  "tags":   ["new"]
}')
UNION ALL
-- Event 3 is the awkward one. Its amount is a STRING, it has no device key
-- at all, and its tags array is empty. Nothing here is a mistake.
SELECT 3, PARSE_JSON('{
  "event": "refund",
  "amount": "1500.00",
  "user":   {"id": 103, "name": "Kestrel Analytics"},
  "items":  [{"sku": "A-100", "qty": 5, "price": 300}],
  "tags":   []
}')
UNION ALL
SELECT 4, PARSE_JSON('{
  "event": "purchase",
  "amount": 9250,
  "user":   {"id": 107, "name": "Meridian Health"},
  "device": {"os": "android", "type": "tablet"},
  "items":  [{"sku": "D-400", "qty": 12, "price": 500},
             {"sku": "A-100", "qty": 10, "price": 300},
             {"sku": "E-505", "qty": 8,  "price": 156.25}],
  "tags":   ["priority", "enterprise", "repeat"]
}');

Let's look at what went in.

Query

SELECT EVENT_ID, PAYLOAD
FROM EVENTS_JSON
ORDER BY EVENT_ID;

Result4 rows · 2 columns

EVENT_ID	PAYLOAD
1	{ "amount": 4200, "device": { "os": "windows", "type": "desktop" }, "event": "purchase", "items": [ { "price": 300, "qty": 8, "sku": "A-100" }, { "price": 450, "qty": 4, "sku": "B-220" } ], "tags": [ "priority", "repeat" ], "user": { "id": 101, "name": "Northwind Traders" } }
2	{ "amount": 890.5, "device": { "os": "ios", "type": "mobile" }, "event": "purchase", "items": [ { "price": 296.83, "qty": 3, "sku": "C-310" } ], "tags": [ "new" ], "user": { "id": 102, "name": "Blue Ridge Supply" } }
3	{ "amount": "1500.00", "event": "refund", "items": [ { "price": 300, "qty": 5, "sku": "A-100" } ], "tags": [], "user": { "id": 103, "name": "Kestrel Analytics" } }
4	{ "amount": 9250, "device": { "os": "android", "type": "tablet" }, "event": "purchase", "items": [ { "price": 500, "qty": 12, "sku": "D-400" }, { "price": 300, "qty": 10, "sku": "A-100" }, { "price": 156.25, "qty": 8, "sku": "E-505" } ], "tags": [ "priority", "enterprise", "repeat" ], "user": { "id": 107, "name": "Meridian Health" } }

Four rows, two columns. Snowflake prints the payload back to us formatted, with the keys in alphabetical order rather than the order we wrote them. That is a hint about how it is stored, and we will come back to it when we get to duplicate keys.

And let's check how the column is declared, since the whole post depends on it.

Query

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'EVENTS_JSON'
ORDER BY ORDINAL_POSITION;

Result2 rows · 4 columns

COLUMN_NAME	DATA_TYPE	IS_NULLABLE	COLUMN_DEFAULT
EVENT_ID	NUMBER	YES	NULL
PAYLOAD	VARIANT	YES	NULL

PAYLOAD is a VARIANT, it is nullable, and it has no default. Nothing in that catalog row says anything about the shape of what is inside it, because there is nothing to say.

Let's read the same path on every row

You read into a VARIANT with a colon and the key name. PAYLOAD:amount gets the amount out of every document.

Let's ask for the amount and the event, and ask TYPEOF what each one actually is.

Query

SELECT EVENT_ID,
       PAYLOAD:amount              AS AMOUNT_RAW,
       TYPEOF(PAYLOAD:amount)      AS ITS_TYPE,
       PAYLOAD:event::VARCHAR      AS EVENT_,
       TYPEOF(PAYLOAD:event)       AS EVENT_TYPE
FROM EVENTS_JSON
ORDER BY EVENT_ID;

Result4 rows · 5 columns

EVENT_ID	AMOUNT_RAW	ITS_TYPE	EVENT_	EVENT_TYPE
1	4200	DECIMAL	purchase	VARCHAR
2	890.5	DECIMAL	purchase	VARCHAR
3	"1500.00"	VARCHAR	refund	VARCHAR
4	9250	INTEGER	purchase	VARCHAR

Look at event 3. Its amount has quotes around it, and TYPEOF says VARCHAR. That amount is text.

Now look at events 1 and 4. Both of them are plain numbers with no quotes, and they still disagree with each other. 4200 is a DECIMAL and 9250 is an INTEGER.

The EVENT_TYPE column is the boring case for contrast: VARCHAR on all four rows, because all four documents wrote that one as text.

Let's count the types instead of reading them off.

Query

SELECT COUNT(*)                                AS ROWS_TOTAL,
       COUNT(DISTINCT TYPEOF(PAYLOAD:amount))  AS DISTINCT_AMOUNT_TYPES,
       COUNT_IF(TYPEOF(PAYLOAD:amount) = 'VARCHAR') AS AMOUNT_IS_TEXT,
       COUNT_IF(TYPEOF(PAYLOAD:amount) = 'DECIMAL') AS AMOUNT_IS_DECIMAL,
       COUNT_IF(TYPEOF(PAYLOAD:amount) = 'INTEGER') AS AMOUNT_IS_INTEGER
FROM EVENTS_JSON;

Result1 row · 5 columns

ROWS_TOTAL	DISTINCT_AMOUNT_TYPES	AMOUNT_IS_TEXT	AMOUNT_IS_DECIMAL	AMOUNT_IS_INTEGER
4	3	1	2	1

4 rows, 3 distinct types in one column. 1 of them is text, 2 are decimal and 1 is an integer.

No error. No warning. That is what no schema means. The column took what it was given.

So what can a VARIANT actually hold?

Let's ask it directly. Parse one of each kind of JSON value and run TYPEOF over the lot.

Query

SELECT TYPEOF(PARSE_JSON('1')::VARIANT)            AS AN_INTEGER,
       TYPEOF(PARSE_JSON('1.5')::VARIANT)          AS A_DECIMAL,
       TYPEOF(PARSE_JSON('"x"')::VARIANT)          AS A_STRING,
       TYPEOF(PARSE_JSON('true')::VARIANT)         AS A_BOOLEAN,
       TYPEOF(PARSE_JSON('null')::VARIANT)         AS A_JSON_NULL,
       TYPEOF(PARSE_JSON('[1,2]')::VARIANT)        AS AN_ARRAY,
       TYPEOF(PARSE_JSON('{"a":1}')::VARIANT)      AS AN_OBJECT;

Result1 row · 7 columns · scroll →

AN_INTEGER	A_DECIMAL	A_STRING	A_BOOLEAN	A_JSON_NULL	AN_ARRAY	AN_OBJECT
INTEGER	DECIMAL	VARCHAR	BOOLEAN	NULL_VALUE	ARRAY	OBJECT

Seven answers: INTEGER, DECIMAL, VARCHAR, BOOLEAN, NULL_VALUE, ARRAY and OBJECT.

Stop on NULL_VALUE for a moment. That is a type. A JSON null is a value whose type is NULL_VALUE, and a value is not an absence. Hold on to that, because two sections from now it is the whole point.

Now let's add that column up

We have a column with a string in the middle of it. Let's sum it with no cast at all and see what happens.

Query

SELECT SUM(PAYLOAD:amount)              AS SUM_OF_THE_VARIANT,
       COUNT(PAYLOAD:amount)            AS ROWS_COUNTED,
       COUNT(*)                         AS ROWS_IN_TABLE
FROM EVENTS_JSON;

Result1 row · 3 columns

SUM_OF_THE_VARIANT	ROWS_COUNTED	ROWS_IN_TABLE
15840.5	4	4

It worked. 15840.5, over 4 counted rows out of 4 in the table. SUM converted that string to a number by itself and added it in.

So let's do it properly, with the cast, and see how far wrong the first one was.

Query

SELECT SUM(PAYLOAD:amount::NUMBER(12,2)) AS SUM_CAST,
       COUNT(PAYLOAD:amount::NUMBER(12,2)) AS ROWS_COUNTED,
       MIN(PAYLOAD:amount::NUMBER(12,2)) AS SMALLEST,
       MAX(PAYLOAD:amount::NUMBER(12,2)) AS LARGEST
FROM EVENTS_JSON;

Result1 row · 4 columns

SUM_CAST	ROWS_COUNTED	SMALLEST	LARGEST
15840.50	4	890.50	9250.00

Same total. Let's put them in one grid and compare them rather than eyeballing two.

Query

SELECT SUM(PAYLOAD:amount)::NUMBER(12,2)  AS UNCAST_SUM,
       SUM(PAYLOAD:amount::NUMBER(12,2))  AS CAST_SUM,
       SUM(PAYLOAD:amount)::NUMBER(12,2)
         = SUM(PAYLOAD:amount::NUMBER(12,2)) AS THEY_AGREE
FROM EVENTS_JSON;

Result1 row · 3 columns

UNCAST_SUM	CAST_SUM	THEY_AGREE
15840.50	15840.50	TRUE

Identical, and THEY_AGREE says TRUE. The cast changed nothing.

Which is the opposite of the trap you were expecting, so stay with me for one more query.

Now let's filter on it instead

Same column, same string in it. This time we compare rather than add. Let's do a greater-than and an equals, each one twice, cast and uncast.

Query

SELECT COUNT(*)                                       AS ROWS_TOTAL,
       COUNT_IF(PAYLOAD:amount > 1000)                AS UNCAST_OVER_1000,
       COUNT_IF(PAYLOAD:amount::NUMBER(12,2) > 1000)  AS CAST_OVER_1000,
       COUNT_IF(PAYLOAD:amount = 1500.00)             AS UNCAST_EQUALS_1500,
       COUNT_IF(PAYLOAD:amount::NUMBER(12,2) = 1500.00) AS CAST_EQUALS_1500
FROM EVENTS_JSON;

Result1 row · 5 columns

ROWS_TOTAL	UNCAST_OVER_1000	CAST_OVER_1000	UNCAST_EQUALS_1500	CAST_EQUALS_1500
4	3	3	0	1

Greater than 1000: 3 rows uncast, 3 rows cast. They agree. So > converts the string too.

Equals 1500: 0 rows uncast, 1 row cast.

There it is. SUM coerces. Greater-than coerces. Equals does not.

This is the one to remember

So the total on your report is correct, and the query you write to look up that one order and reconcile it returns nothing at all. Same column, same row, same data, and no error anywhere in either query.
The lesson of this post is not that conversion fails. It is that conversion happens in some places and not in others, which is a far harder thing to notice.
Cast at the path. Every time. Not because it errors if you do not — because it does not.

Missing is not the same as null

Second thing. Event 3 has no device key in it at all. Let's read that path on every row anyway.

Query

SELECT EVENT_ID,
       PAYLOAD:device                  AS DEVICE_RAW,
       PAYLOAD:device.os::VARCHAR      AS DEVICE_OS,
       TYPEOF(PAYLOAD:device)          AS DEVICE_TYPE,
       PAYLOAD:device IS NULL          AS SQL_NULL,
       IS_NULL_VALUE(PAYLOAD:device)   AS JSON_NULL
FROM EVENTS_JSON
ORDER BY EVENT_ID;

Result4 rows · 6 columns · scroll →

EVENT_ID	DEVICE_RAW	DEVICE_OS	DEVICE_TYPE	SQL_NULL	JSON_NULL
1	{ "os": "windows", "type": "desktop" }	windows	OBJECT	FALSE	FALSE
2	{ "os": "ios", "type": "mobile" }	ios	OBJECT	FALSE	FALSE
3	NULL	NULL	NULL	TRUE	NULL
4	{ "os": "android", "type": "tablet" }	android	OBJECT	FALSE	FALSE

Three rows gave us an object. Event 3 gave us NULL all the way across, including from TYPEOF.

Now the interesting pair of columns. Is that a SQL NULL, or is the key simply not there? Those are two different questions, and the grid answers them differently.

SQL_NULL says TRUE. And JSON_NULL — which is IS_NULL_VALUE — says NULL. Not FALSE. NULL.

The function that tells you whether a value is a JSON null cannot answer, because there is no value there to ask about.

Let's count all of that over the table.

Query

SELECT COUNT(*)                                  AS ROWS_TOTAL,
       COUNT(PAYLOAD:device)                     AS DEVICE_COUNTED,
       COUNT_IF(PAYLOAD:device IS NULL)          AS DEVICE_IS_SQL_NULL,
       COUNT_IF(IS_NULL_VALUE(PAYLOAD:device))   AS DEVICE_IS_JSON_NULL,
       COUNT_IF(PAYLOAD:nothing_like_this IS NULL) AS A_KEY_THAT_NEVER_EXISTED
FROM EVENTS_JSON;

Result1 row · 5 columns

ROWS_TOTAL	DEVICE_COUNTED	DEVICE_IS_SQL_NULL	DEVICE_IS_JSON_NULL	A_KEY_THAT_NEVER_EXISTED
4	3	1	0	4

4 rows. 3 have a device. 1 is a SQL NULL. 0 are a JSON null. And the last column asks the same question about a key no document has ever contained, which comes back 4.

So a key nobody ever wrote looks exactly like a key that was written and set to null. From here you cannot tell them apart.

Let's build both cases in one row

Rather than argue about it, let's make a document that has both: a key present and explicitly null, and a key that is not there. Then ask both questions about both of them.

Query

SELECT PARSE_JSON('{"a": null}'):a                  AS PRESENT_BUT_JSON_NULL,
       PARSE_JSON('{"a": null}'):a IS NULL          AS THAT_IS_SQL_NULL,
       IS_NULL_VALUE(PARSE_JSON('{"a": null}'):a)   AS THAT_IS_JSON_NULL,
       PARSE_JSON('{"a": null}'):b                  AS ABSENT,
       PARSE_JSON('{"a": null}'):b IS NULL          AS ABSENT_IS_SQL_NULL,
       IS_NULL_VALUE(PARSE_JSON('{"a": null}'):b)   AS ABSENT_IS_JSON_NULL;

Result1 row · 6 columns · scroll →

PRESENT_BUT_JSON_NULL	THAT_IS_SQL_NULL	THAT_IS_JSON_NULL	ABSENT	ABSENT_IS_SQL_NULL	ABSENT_IS_JSON_NULL
null	FALSE	TRUE	NULL	TRUE	NULL

Before the answers, look at the two value columns. PRESENT_BUT_JSON_NULL and ABSENT are drawn in that grid as the same empty NULL cell, and that is the problem in one picture. You cannot tell these two apart by looking at them. The boolean columns are the only thing that separates them.

The present-but-null key: IS NULL says FALSE and IS_NULL_VALUE says TRUE. There is a value there, and that value is a JSON null.

The absent key: IS NULL says TRUE and IS_NULL_VALUE says NULL.

Three answers from a yes-or-no-looking function

IS_NULL_VALUE has three answers, not two. TRUE for a JSON null, FALSE for any other value, and NULL when there is nothing to look at.
And those two states are genuinely different facts. Present and null says we know this one is empty. Absent says we were never told. If you are loading other people's data, you will want to keep them apart.

Can a VARIANT column be NOT NULL?

If that distinction is real, Snowflake should enforce it. Let's find out. A table with a VARIANT column declared NOT NULL.

Query

CREATE OR REPLACE TABLE VARIANT_PROBE (
    ID   INT,
    DOC  VARIANT NOT NULL
);

Result1 row · 1 column

status
Table VARIANT_PROBE successfully created.

Accepted. And the catalog agrees.

Query

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'VARIANT_PROBE'
ORDER BY ORDINAL_POSITION;

Result2 rows · 3 columns

COLUMN_NAME	DATA_TYPE	IS_NULLABLE
ID	NUMBER	YES
DOC	VARIANT	NO

DOC is a VARIANT and IS_NULLABLE is NO. Now let's put a SQL NULL in it.

The insert that fails

INSERT INTO VARIANT_PROBE SELECT 1, NULL;

Query failed — Snowflake returned an error

100072 (22000): DML operation to table VARIANT_PROBE failed on column DOC with error: NULL result in a non-nullable column

Rejected, as you would want. Now the same column, and a JSON null this time.

Query

INSERT INTO VARIANT_PROBE SELECT 2, PARSE_JSON('null');

Result1 row · 1 column

number of rows inserted
1

One row inserted. The constraint let it through.

Let's look at what landed.

Query

SELECT ID, DOC, DOC IS NULL AS SQL_NULL, IS_NULL_VALUE(DOC) AS JSON_NULL,
       TYPEOF(DOC) AS ITS_TYPE
FROM VARIANT_PROBE
ORDER BY ID;

Result1 row · 5 columns

ID	DOC	SQL_NULL	JSON_NULL	ITS_TYPE
2	null	FALSE	TRUE	NULL_VALUE

There is one row in the table, the one with id 2. The DOC cell is drawn empty again, and again that is not the whole story: SQL_NULL is FALSE, JSON_NULL is TRUE, and TYPEOF says NULL_VALUE. There is a value in that column, and its type is the JSON null type.

That is the best argument I can give you that this distinction is not pedantry. A NOT NULL constraint rejects one and admits the other, so Snowflake itself treats a JSON null as a value.

Let's clear that table away.

Query

DROP TABLE IF EXISTS VARIANT_PROBE;

Result1 row · 1 column

status
VARIANT_PROBE successfully dropped.

What about a DEFAULT on a VARIANT column?

I got this one wrong, and the wrong prediction is more useful than the right answer would have been.

I expected a VARIANT column with a default of 'not json' to be refused at create time, because that text is not JSON and never will be.

Query

CREATE OR REPLACE TABLE VARIANT_DEFAULT_PROBE (
    ID   INT,
    DOC  VARIANT DEFAULT 'not json'
);

Result1 row · 1 column

status
Table VARIANT_DEFAULT_PROBE successfully created.

Created, no complaint. And the catalog records the default quite happily.

Query

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'VARIANT_DEFAULT_PROBE'
ORDER BY ORDINAL_POSITION;

Result2 rows · 4 columns

COLUMN_NAME	DATA_TYPE	IS_NULLABLE	COLUMN_DEFAULT
ID	NUMBER	YES	NULL
DOC	VARIANT	YES	'not json'

Now let's insert a row that relies on it.

The insert that fails

INSERT INTO VARIANT_DEFAULT_PROBE (ID) VALUES (1);

Query failed — Snowflake returned an error

100069 (22P02): Error parsing JSON: unknown keyword "not", pos 4

Error parsing JSON, unknown keyword not at position 4.

A column that is broken from birth

So you can create a column whose default can never be used, and nothing tells you at create time. Not the CREATE, not the catalog. The first row that needs the default is what tells you, and that may be months later and in somebody else's job.

So which defaults do work?

Two of them do. The obvious one is to parse the JSON in the default itself.

Query

CREATE OR REPLACE TABLE VARIANT_DEFAULT_PROBE2 (
    ID   INT,
    DOC  VARIANT DEFAULT PARSE_JSON('{"from":"the default"}')
);

Result1 row · 1 column

status
Table VARIANT_DEFAULT_PROBE2 successfully created.

Query

INSERT INTO VARIANT_DEFAULT_PROBE2 (ID) VALUES (1);

Result1 row · 1 column

number of rows inserted
1

One row inserted, and that time it went in. Let's read it back.

Query

SELECT ID, DOC, TYPEOF(DOC) AS ITS_TYPE,
       DOC:from::VARCHAR AS THE_KEY_READ_BACK
FROM VARIANT_DEFAULT_PROBE2
ORDER BY ID;

Result1 row · 4 columns

ID	DOC	ITS_TYPE	THE_KEY_READ_BACK
1	{ "from": "the default" }	OBJECT	the default

An OBJECT, and the key reads back out of it normally.

The less obvious one is that a text default works fine so long as the text happens to be valid JSON.

Query

CREATE OR REPLACE TABLE VARIANT_DEFAULT_PROBE3 (
    ID   INT,
    DOC  VARIANT DEFAULT '{"from":"a string default"}'
);

Result1 row · 1 column

status
Table VARIANT_DEFAULT_PROBE3 successfully created.

Query

INSERT INTO VARIANT_DEFAULT_PROBE3 (ID) VALUES (1);

Result1 row · 1 column

number of rows inserted
1

That inserted too. So the text is not rejected for being text. Let's see what it actually stored.

Query

SELECT ID, DOC, TYPEOF(DOC) AS ITS_TYPE,
       DOC:from::VARCHAR AS READ_BACK,
       TO_JSON(DOC)      AS AS_JSON_TEXT
FROM VARIANT_DEFAULT_PROBE3
ORDER BY ID;

Result1 row · 5 columns

ID	DOC	ITS_TYPE	READ_BACK	AS_JSON_TEXT
1	{ "from": "a string default" }	OBJECT	a string default	{"from":"a string default"}

TYPEOF says OBJECT, the key reads back, and TO_JSON gives the text form again.

So the default was parsed on the way in, not stored as a string. Which is exactly why 'not json' failed — the parse is the step that was always going to happen, and it was deferred to the first insert.

Let's tidy up the three probe tables.

Query

DROP TABLE IF EXISTS VARIANT_DEFAULT_PROBE;

Result1 row · 1 column

status
VARIANT_DEFAULT_PROBE successfully dropped.

Query

DROP TABLE IF EXISTS VARIANT_DEFAULT_PROBE2;

Result1 row · 1 column

status
VARIANT_DEFAULT_PROBE2 successfully dropped.

Query

DROP TABLE IF EXISTS VARIANT_DEFAULT_PROBE3;

Result1 row · 1 column

status
VARIANT_DEFAULT_PROBE3 successfully dropped.

What happens when we ask a VARIANT to do the wrong thing?

A VARIANT will hold anything, so the type it happens to be holding decides which functions work on it. Some of them refuse, and a couple of them do something worse than refusing.

Let's start with OBJECT_KEYS on something that is not an object.

The query that fails

SELECT OBJECT_KEYS(PARSE_JSON('[1,2,3]')) AS SHOULD_NOT_GET_THIS_FAR;

Query failed — Snowflake returned an error

100071 (22000): Failed to cast variant value [1,2,3] to OBJECT

Refused, and the message names the value it could not cast. That is a good error.

Now casting between types. A JSON boolean to a number.

Query

SELECT PARSE_JSON('true')::INT    AS TRUE_AS_INT,
       PARSE_JSON('false')::INT   AS FALSE_AS_INT,
       TRUE::INT                  AS SQL_TRUE_AS_INT;

Result1 row · 3 columns

TRUE_AS_INT	FALSE_AS_INT	SQL_TRUE_AS_INT
1	0	1

true gives 1 and false gives 0, the same as casting a SQL boolean. So that direction works.

Let's try it the other way round.

The query that fails

SELECT PARSE_JSON('1')::BOOLEAN AS SHOULD_NOT_GET_THIS_FAR;

Query failed — Snowflake returned an error

100071 (22000): Failed to cast variant value 1 to BOOLEAN

Refused. So the conversion is not symmetric: a boolean out of a VARIANT becomes a number, and a number out of a VARIANT will not become a boolean.

Now the one that does something worse than refusing. ARRAY_CAT on an object.

The query that fails

SELECT ARRAY_CAT(PAYLOAD:device, ARRAY_CONSTRUCT('audited')) AS SHOULD_NOT_GET_THIS_FAR
FROM EVENTS_JSON WHERE EVENT_ID = 1;

Query failed — Snowflake returned an error

100098 (22000): Left argument of ARRAY_CAT is not an array

Refused, and clearly. Good. But watch what happens if you take the hint and add a cast to make it an array.

Query

SELECT EVENT_ID,
       PAYLOAD:device                                             AS THE_OBJECT,
       PAYLOAD:device::ARRAY                                      AS CAST_TO_ARRAY,
       ARRAY_SIZE(PAYLOAD:device::ARRAY)                          AS ITS_SIZE,
       ARRAY_CAT(PAYLOAD:device::ARRAY, ARRAY_CONSTRUCT('audited')) AS CAT_RESULT
FROM EVENTS_JSON
WHERE EVENT_ID = 1;

Result1 row · 5 columns

EVENT_ID	THE_OBJECT	CAST_TO_ARRAY	ITS_SIZE	CAT_RESULT
1	{ "os": "windows", "type": "desktop" }	[ { "os": "windows", "type": "desktop" } ]	1	[ { "os": "windows", "type": "desktop" }, "audited" ]

::ARRAY on an object does not fail and does not unpack it. It wraps it, giving a one-element array whose single element is the whole object. The ARRAY_CAT then succeeds and returns something that is almost certainly not what was wanted.

The fix that is not a fix

The error message said “not an array”, the obvious fix was to cast it to an array, and the cast silently changed the question rather than answering it. An error you can read is worth more than a result you cannot.

Can TRY_CAST save us?

If a cast can fail, the natural move is to reach for TRY_CAST and get a NULL instead of an error. Let's try that on a VARIANT.

The query that fails

SELECT TRY_CAST(PARSE_JSON('42') AS VARCHAR) AS SHOULD_NOT_GET_THIS_FAR;

Query failed — Snowflake returned an error

001065 (22023): SQL compilation error:
Function TRY_CAST cannot be used with arguments of types VARIANT and VARCHAR(134217728)

TRY_CAST will not take a VARIANT at all. It is a compile error, not a runtime one.

And it will not produce one either.

The query that fails

SELECT TRY_CAST('[1,2,3]' AS ARRAY) AS SHOULD_NOT_GET_THIS_FAR;

Query failed — Snowflake returned an error

001065 (22023): SQL compilation error:
Function TRY_CAST cannot be used with arguments of types VARCHAR(7) and ARRAY

So TRY_CAST is for the fixed SQL types, and the semi-structured ones are outside it in both directions.

What does work is going through text and using the TRY_TO_ family. Let's build the whole matrix: eight different source values, each cast to VARCHAR and then offered to three TRY_TO_ functions.

Query

SELECT TYPEOF(V)                                      AS SOURCE_TYPE,
       V::VARCHAR                                     AS AS_TEXT,
       TRY_TO_NUMBER(V::VARCHAR)                      AS TO_NUMBER,
       TRY_TO_BOOLEAN(V::VARCHAR)                     AS TO_BOOLEAN,
       TRY_TO_DATE(V::VARCHAR)                        AS TO_DATE
FROM (SELECT PARSE_JSON('42')     AS V
      UNION ALL SELECT PARSE_JSON('42.7')
      UNION ALL SELECT PARSE_JSON('"42"')
      UNION ALL SELECT PARSE_JSON('"hello"')
      UNION ALL SELECT PARSE_JSON('true')
      UNION ALL SELECT PARSE_JSON('"true"')
      UNION ALL SELECT PARSE_JSON('"2024-01-15"')
      UNION ALL SELECT PARSE_JSON('null'));

Result8 rows · 5 columns

SOURCE_TYPE	AS_TEXT	TO_NUMBER	TO_BOOLEAN	TO_DATE
INTEGER	42	42	NULL	1970-01-01
DECIMAL	42.7	43	NULL	NULL
VARCHAR	42	42	NULL	1970-01-01
VARCHAR	hello	NULL	NULL	NULL
BOOLEAN	true	NULL	TRUE	NULL
VARCHAR	true	NULL	TRUE	NULL
VARCHAR	2024-01-15	NULL	NULL	2024-01-15
NULL_VALUE	NULL	NULL	NULL	NULL

Read that one slowly, because three rows in it are surprising.

The integer 42 and the string "42" behave identically once they are text. Both convert to a number, and both convert to the date 1970-01-01, because TRY_TO_DATE read them as an epoch day count.
42.7 came back from TRY_TO_NUMBER as 43. That is the same rounding we saw in Episode 84, and it is not a TRY_ failure — it is a successful conversion that lost the fraction.
The JSON boolean true and the string "true" are indistinguishable by the time they reach TRY_TO_BOOLEAN. Both give TRUE. Only the SOURCE_TYPE column remembers which was which.
And the JSON null, on the last row, gives NULL from all three, with SOURCE_TYPE reading NULL_VALUE.

Casting to VARCHAR first is what threw the type information away. That is the thing to notice, and it is why TYPEOF is worth reaching for before the cast rather than after it.

Let's read an element out of an array

Event 4 has three items in it. Let's pull elements out by position with GET, including positions that do not exist.

Query

SELECT GET(PAYLOAD:items, 0):sku::VARCHAR     AS GET_0,
       GET(PAYLOAD:items, 2):sku::VARCHAR     AS GET_2_LAST,
       GET(PAYLOAD:items, 3)                  AS GET_3_ONE_PAST,
       GET(PAYLOAD:items, 99)                 AS GET_99,
       GET(PAYLOAD:items, 99) IS NULL         AS PAST_THE_END_IS_NULL,
       ARRAY_SIZE(PAYLOAD:items)              AS ACTUAL_SIZE
FROM EVENTS_JSON WHERE EVENT_ID = 4;

Result1 row · 6 columns · scroll →

GET_0	GET_2_LAST	GET_3_ONE_PAST	GET_99	PAST_THE_END_IS_NULL	ACTUAL_SIZE
D-400	E-505	NULL	NULL	TRUE	3

The first two columns reach into the element and pull its sku out. Index 0 got the first item and index 2 got the last, because the array has 3 elements and the indexing is zero-based. Index 3 is one past the end and index 99 is nowhere near it, and both of those come back NULL rather than erroring.

So reading past the end of an array is quiet. Let's try reading before the beginning of one.

The query that fails

SELECT GET(PAYLOAD:items, -1) AS SHOULD_NOT_GET_THIS_FAR
FROM EVENTS_JSON WHERE EVENT_ID = 4;

Query failed — Snowflake returned an error

001852 (22023): Invalid extraction path '-1': array index -1 is off limits; must be between 0 and 2,147,483,647.

That is an error, and it is a compile-time one. Past the end is NULL, before the start is a refusal. Not symmetric.

Which matters more than it sounds, because of how you normally reach for the last element. Let's take the last tag of every event with ARRAY_SIZE - 1.

Query

SELECT EVENT_ID,
       ARRAY_SIZE(PAYLOAD:tags)                                   AS N_TAGS,
       ARRAY_SIZE(PAYLOAD:tags) - 1                               AS THE_INDEX_USED,
       PAYLOAD:tags[ARRAY_SIZE(PAYLOAD:tags) - 1]::VARCHAR        AS COMPUTED_INDEX_RESULT,
       PAYLOAD:tags[ARRAY_SIZE(PAYLOAD:tags) - 1] IS NULL         AS IT_CAME_BACK_NULL
FROM EVENTS_JSON
ORDER BY EVENT_ID;

Result4 rows · 5 columns

EVENT_ID	N_TAGS	THE_INDEX_USED	COMPUTED_INDEX_RESULT	IT_CAME_BACK_NULL
1	2	1	repeat	FALSE
2	1	0	new	FALSE
3	0	-1	NULL	TRUE
4	3	2	repeat	FALSE

Three of the four events work fine. Event 3 has an empty tags array, so the index came out as -1, and the result came back NULL instead of raising anything.

Now write that very same -1 as a literal instead of computing it.

The query that fails

SELECT PAYLOAD:tags[-1]::VARCHAR AS SHOULD_NOT_GET_THIS_FAR
FROM EVENTS_JSON WHERE EVENT_ID = 3;

Query failed — Snowflake returned an error

001852 (22023): Invalid extraction path '-1': array index -1 is off limits; must be between 0 and 2,147,483,647.

Same index, same array, and this time it is an error.

The difference is when the value is known. A literal -1 is visible to the compiler, which rejects it. A computed -1 is not, so it reaches the runtime and the runtime hands back NULL. Your empty-array rows will not raise. They will just go quiet, which is the harder failure to find.

One more on arrays, because it contrasts with the object case from two sections ago.

Query

SELECT EVENT_ID,
       ARRAY_SIZE(PAYLOAD:tags)                                   AS BEFORE_,
       ARRAY_CAT(PAYLOAD:tags, ARRAY_CONSTRUCT('audited'))        AS AFTER_,
       ARRAY_SIZE(ARRAY_CAT(PAYLOAD:tags,
                            ARRAY_CONSTRUCT('audited')))          AS SIZE_AFTER
FROM EVENTS_JSON
ORDER BY EVENT_ID;

Result4 rows · 4 columns

EVENT_ID	BEFORE_	AFTER_	SIZE_AFTER
1	2	[ "priority", "repeat", "audited" ]	3
2	1	[ "new", "audited" ]	2
3	0	[ "audited" ]	1
4	3	[ "priority", "enterprise", "repeat", "audited" ]	4

ARRAY_CAT on something that really is an array appends properly on all four rows, and the sizes go up by one each time. Including event 3, whose empty array becomes a one-element array rather than staying empty or going NULL.

What if we want the type checked for us?

Everything so far has been a loose VARIANT. Snowflake also has structured versions — ARRAY(INT), OBJECT(sku VARCHAR, qty INT) and MAP(VARCHAR, INT) — where you say up front what is allowed.

Let's put the loose and the typed versions side by side in one table.

Sample data

-- The typed-shapes table, for the structured-types section further down.
CREATE OR REPLACE TABLE TYPED_SHAPES (
    ROW_ID        INT,
    LOOSE_ARRAY   ARRAY,
    TYPED_ARRAY   ARRAY(INT),
    LOOSE_OBJECT  OBJECT,
    TYPED_OBJECT  OBJECT(sku VARCHAR, qty INT),
    TYPED_MAP     MAP(VARCHAR, INT)
);

INSERT INTO TYPED_SHAPES
SELECT 1,
       [1, 'two', 3.5]::ARRAY,
       [1, 2, 3]::ARRAY(INT),
       {'sku': 'A-100', 'qty': 8, 'extra': TRUE}::OBJECT,
       {'sku': 'A-100', 'qty': 8}::OBJECT(sku VARCHAR, qty INT),
       {'a': 1, 'b': 2}::MAP(VARCHAR, INT)
UNION ALL
SELECT 2,
       [10, 'twenty']::ARRAY,
       [10, 20]::ARRAY(INT),
       {'sku': 'B-220', 'price': 450}::OBJECT,
       {'sku': 'B-220', 'qty': 4}::OBJECT(sku VARCHAR, qty INT),
       {'x': 9}::MAP(VARCHAR, INT);

Query

SELECT * FROM TYPED_SHAPES ORDER BY ROW_ID;

Result2 rows · 6 columns · scroll →

ROW_ID	LOOSE_ARRAY	TYPED_ARRAY	LOOSE_OBJECT	TYPED_OBJECT	TYPED_MAP
1	[ 1, "two", 3.5 ]	[ 1, 2, 3 ]	{ "extra": true, "qty": 8, "sku": "A-100" }	{ "sku": "A-100", "qty": 8 }	{ "a": 1, "b": 2 }
2	[ 10, "twenty" ]	[ 10, 20 ]	{ "price": 450, "sku": "B-220" }	{ "sku": "B-220", "qty": 4 }	{ "x": 9 }

Two rows and six columns. Look at the LOOSE_ARRAY column: a number, a string and a decimal all in one array. The TYPED_ARRAY beside it holds only integers.

Here is how they are declared.

Query

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'TYPED_SHAPES'
ORDER BY ORDINAL_POSITION;

Result6 rows · 3 columns

COLUMN_NAME	DATA_TYPE	IS_NULLABLE
ROW_ID	NUMBER	YES
LOOSE_ARRAY	ARRAY	YES
TYPED_ARRAY	ARRAY	YES
LOOSE_OBJECT	OBJECT	YES
TYPED_OBJECT	OBJECT	YES
TYPED_MAP	MAP	YES

The catalog reports the base types — ARRAY, OBJECT, MAP — and does not show the element types at all. So the catalog alone will not tell you which of your columns are typed.

And TYPEOF does not either.

Query

SELECT ROW_ID,
       TYPEOF(LOOSE_ARRAY::VARIANT)   AS LOOSE_ARRAY_TYPE,
       TYPEOF(TYPED_ARRAY::VARIANT)   AS TYPED_ARRAY_TYPE,
       TYPEOF(LOOSE_OBJECT::VARIANT)  AS LOOSE_OBJECT_TYPE,
       TYPEOF(TYPED_OBJECT::VARIANT)  AS TYPED_OBJECT_TYPE,
       TYPEOF(TYPED_MAP::VARIANT)     AS TYPED_MAP_TYPE
FROM TYPED_SHAPES
ORDER BY ROW_ID;

Result2 rows · 6 columns · scroll →

ROW_ID	LOOSE_ARRAY_TYPE	TYPED_ARRAY_TYPE	LOOSE_OBJECT_TYPE	TYPED_OBJECT_TYPE	TYPED_MAP_TYPE
1	ARRAY	ARRAY	OBJECT	OBJECT	OBJECT
2	ARRAY	ARRAY	OBJECT	OBJECT	OBJECT

Loose and typed both report ARRAY. The MAP reports OBJECT. So TYPEOF tells you the shape and not the contract.

So where does the typing actually show up?

On the way in. Let's build a loose array out of three different kinds of thing.

Query

SELECT [1, 'two', 3.5]::ARRAY                      AS LOOSE_,
       TYPEOF([1, 'two', 3.5]::ARRAY::VARIANT)     AS ITS_TYPE,
       ARRAY_SIZE([1, 'two', 3.5]::ARRAY)          AS ITS_SIZE;

Result1 row · 3 columns

LOOSE_	ITS_TYPE	ITS_SIZE
[ 1, "two", 3.5 ]	ARRAY	3

Accepted, size 3. Now the same three values into an ARRAY(INT).

The query that fails

SELECT [1, 'two', 3]::ARRAY(INT) AS SHOULD_NOT_GET_THIS_FAR;

Query failed — Snowflake returned an error

220000 (22000): Typed object schema mismatch in conversion

Refused: typed object schema mismatch. That is the schema you gave up, handed back to you.

An object with a key that was not declared?

The query that fails

SELECT {'sku': 'A-100', 'nonsense': 1}::OBJECT(sku VARCHAR, qty INT)
         AS SHOULD_NOT_GET_THIS_FAR;

Query failed — Snowflake returned an error

220000 (22000): Typed object schema mismatch in conversion

Also refused. And a map with the wrong value type?

The query that fails

SELECT {'a': 'not an int'}::MAP(VARCHAR, INT) AS SHOULD_NOT_GET_THIS_FAR;

Query failed — Snowflake returned an error

220000 (22000): Typed object schema mismatch in conversion

Refused as well, with the same message all three times.

And there is a second payoff, on the way out

A typed value comes out already typed, so you do not have to cast it at the path.

Query

SELECT ROW_ID,
       TYPED_OBJECT:sku          AS SKU_NO_CAST,
       TYPED_OBJECT:qty          AS QTY_NO_CAST,
       TYPED_OBJECT:qty + 1      AS QTY_IS_ALREADY_A_NUMBER,
       TYPED_ARRAY[0]            AS FIRST_TYPED_ELEMENT,
       TYPED_ARRAY[0] + 1        AS ALSO_ALREADY_A_NUMBER
FROM TYPED_SHAPES
ORDER BY ROW_ID;

Result2 rows · 6 columns · scroll →

ROW_ID	SKU_NO_CAST	QTY_NO_CAST	QTY_IS_ALREADY_A_NUMBER	FIRST_TYPED_ELEMENT	ALSO_ALREADY_A_NUMBER
1	A-100	8	9	1	2
2	B-220	4	5	10	11

TYPED_OBJECT:qty + 1 works with no cast at all, and so does arithmetic on an element of the typed array.

Compare that with the loose object next to it.

Query

SELECT ROW_ID,
       LOOSE_OBJECT:sku                AS SKU_AS_VARIANT,
       TYPEOF(LOOSE_OBJECT:sku)        AS ITS_TYPE,
       LOOSE_OBJECT:qty::INT + 1       AS NEEDED_A_CAST
FROM TYPED_SHAPES
WHERE ROW_ID = 1;

Result1 row · 4 columns

ROW_ID	SKU_AS_VARIANT	ITS_TYPE	NEEDED_A_CAST
1	"A-100"	VARCHAR	9

Reading the loose object gives a VARIANT back — the sku still has its quotes on, and TYPEOF says VARCHAR — and the arithmetic needed an explicit ::INT.

Which is the whole trade in one pair of grids. Declare the shape and Snowflake checks it on the way in and hands it back typed. Leave it loose and both of those jobs are yours.

What about duplicate keys?

A JSON object with the same key twice is a real thing that turns up in real feeds. Let's find out what Snowflake does with it, through four different doors.

First, building one by hand.

The query that fails

SELECT OBJECT_CONSTRUCT('a', 1, 'a', 2) AS SHOULD_NOT_GET_THIS_FAR;

Query failed — Snowflake returned an error

100103 (22000): Duplicate field key 'a'

Then parsing one out of text.

The query that fails

SELECT PARSE_JSON('{"a":1,"a":2}') AS SHOULD_NOT_GET_THIS_FAR;

Query failed — Snowflake returned an error

100069 (22P02): Error parsing JSON: duplicate object attribute "a", pos 10

Then inserting a key that is already there.

The query that fails

SELECT OBJECT_INSERT(OBJECT_CONSTRUCT('a', 1), 'a', 2) AS SHOULD_NOT_GET_THIS_FAR;

Query failed — Snowflake returned an error

100103 (22000): Duplicate field key 'a'

And then aggregating rows into an object.

The query that fails

SELECT OBJECT_AGG(K, V) AS SHOULD_NOT_GET_THIS_FAR
FROM (SELECT 'a' AS K, 1::VARIANT AS V UNION ALL SELECT 'a', 2::VARIANT);

Query failed — Snowflake returned an error

100103 (22000): Duplicate field key 'a'

All four refused. So a Snowflake OBJECT cannot hold a duplicate key by any route, which is the thing to know: it is a property of the storage, not of one function.

There is exactly one way to put a value where a key already exists, and it is to say so out loud.

Query

SELECT OBJECT_INSERT(OBJECT_CONSTRUCT('a', 1), 'a', 2, TRUE)         AS WITH_THE_FLAG,
       OBJECT_INSERT(OBJECT_CONSTRUCT('a', 1), 'a', 2, TRUE):a       AS THE_VALUE_NOW,
       ARRAY_SIZE(OBJECT_KEYS(
         OBJECT_INSERT(OBJECT_CONSTRUCT('a', 1), 'a', 2, TRUE)))     AS KEY_COUNT;

Result1 row · 3 columns

WITH_THE_FLAG	THE_VALUE_NOW	KEY_COUNT
{ "a": 2 }	2	1

OBJECT_INSERT takes a fourth argument that means update if it is already there. With that TRUE in place the value becomes 2 and the key count is still 1.

One last wrinkle, and it is a nasty one. CHECK_JSON is the function you would use to validate text before loading it, and it returns NULL when the text is fine.

Query

SELECT CHECK_JSON('{"a":1,"a":2}')                AS DUPLICATE_COMPLAINT,
       CHECK_JSON('{"a":1,"a":2}') IS NULL        AS CALLED_VALID,
       CHECK_JSON('{"a":1,"b":2}') IS NULL        AS NO_DUPLICATE_IS_VALID,
       CHECK_JSON('{"a":1,}') IS NULL             AS TRAILING_COMMA_IS_VALID;

Result1 row · 4 columns

DUPLICATE_COMPLAINT	CALLED_VALID	NO_DUPLICATE_IS_VALID	TRAILING_COMMA_IS_VALID
duplicate object attribute "a", pos 10	FALSE	TRUE	TRUE

It does complain about the duplicate, and the complaint is word for word the text PARSE_JSON put inside its own error above. So CALLED_VALID is FALSE.

But look at the last column. A trailing comma passes. CHECK_JSON called {"a":1,} valid, and that is not valid JSON by the standard.

We will take that apart properly in the next post, which is about exactly this question.

Nothing is missing, and three things are wrong

Let's step back and ask what the VARIANT type has cost us. Here are the four documents unpacked into columns, by hand.

Query

SELECT EVENT_ID,
       PAYLOAD:event::VARCHAR            AS EVENT_,
       PAYLOAD:user.id::INT              AS USER_ID,
       PAYLOAD:user.name::VARCHAR        AS USER_NAME,
       PAYLOAD:amount::NUMBER(12,2)      AS AMOUNT,
       PAYLOAD:device.type::VARCHAR      AS DEVICE_TYPE,
       ARRAY_SIZE(PAYLOAD:items)         AS ITEM_COUNT,
       ARRAY_SIZE(PAYLOAD:tags)          AS TAG_COUNT
FROM EVENTS_JSON
ORDER BY EVENT_ID;

Result4 rows · 8 columns · scroll →

EVENT_ID	EVENT_	USER_ID	USER_NAME	AMOUNT	DEVICE_TYPE	ITEM_COUNT	TAG_COUNT
1	purchase	101	Northwind Traders	4200.00	desktop	2	2
2	purchase	102	Blue Ridge Supply	890.50	mobile	1	1
3	refund	103	Kestrel Analytics	1500.00	NULL	1	0
4	purchase	107	Meridian Health	9250.00	tablet	3	3

That is readable, and it is the shape you would have had from a proper schema. But every one of those paths is a decision somebody made, and nothing checked any of them.

So let's write the checks a schema would have done for us, and see what they find.

Query

SELECT COUNT(*)                                          AS ROWS_TOTAL,
       COUNT_IF(PAYLOAD:event IS NULL)                   AS MISSING_EVENT,
       COUNT_IF(PAYLOAD:user.id IS NULL)                 AS MISSING_USER_ID,
       COUNT_IF(PAYLOAD:amount IS NULL)                  AS MISSING_AMOUNT,
       COUNT_IF(TYPEOF(PAYLOAD:amount) NOT IN ('DECIMAL','INTEGER'))
                                                         AS AMOUNT_NOT_A_NUMBER,
       COUNT_IF(PAYLOAD:device IS NULL)                  AS MISSING_DEVICE,
       COUNT_IF(ARRAY_SIZE(PAYLOAD:items) = 0)           AS NO_ITEMS,
       COUNT_IF(ARRAY_SIZE(PAYLOAD:tags) = 0)            AS NO_TAGS
FROM EVENTS_JSON;

Result1 row · 8 columns · scroll →

ROWS_TOTAL	MISSING_EVENT	MISSING_USER_ID	MISSING_AMOUNT	AMOUNT_NOT_A_NUMBER	MISSING_DEVICE	NO_ITEMS	NO_TAGS
4	0	0	0	1	1	0	1

Missing event: 0. Missing user id: 0. Missing amount: 0. So nothing is missing. Every document has every key we asked for.

And yet. Amount not a number: 1. Missing device: 1. No tags: 1.

Three problems in four rows, and not one of them is a missing field. A column type would have caught the first one. NOT NULL would have caught none of them, because in every case the value is present — it is just the wrong kind of thing, or an empty container.

That is the trade. You gave up the schema in order to accept any shape, so the shape checks are now yours to write, and yours to remember to write.

One last thing: those quotes in the grid

We have seen quoted values in several grids now, and they confuse people, so let's settle it. Let's read a name path raw, read it cast, and measure both.

Query

SELECT PAYLOAD:user.name                AS NAME_AS_VARIANT,
       PAYLOAD:user.name::VARCHAR       AS NAME_AS_VARCHAR,
       LENGTH(PAYLOAD:user.name::VARCHAR) AS VARCHAR_LENGTH,
       LENGTH(TO_VARCHAR(PAYLOAD:user.name)) AS TO_VARCHAR_LENGTH,
       PAYLOAD:user.name::VARCHAR = TO_VARCHAR(PAYLOAD:user.name) AS SAME_THING
FROM EVENTS_JSON
WHERE EVENT_ID = 1;

Result1 row · 5 columns

NAME_AS_VARIANT	NAME_AS_VARCHAR	VARCHAR_LENGTH	TO_VARCHAR_LENGTH	SAME_THING
"Northwind Traders"	Northwind Traders	17	17	TRUE

The raw VARIANT prints with quotes around it. Cast it to VARCHAR and they are gone.

The two lengths in that grid are both measurements of a cast result — one through ::VARCHAR and one through TO_VARCHAR. Both are 17, and the last column confirms the two cast forms are the same value.

So the quotes were never part of the value. They are the grid telling you that what you are looking at is a VARIANT holding a string, rather than a string. Which is useful information, once you know that is what it is.

Key points

A VARIANT column has no schema, so one column can hold a different type on every row and nothing warns you. TYPEOF is how you find out.
A VARIANT can hold seven things: INTEGER, DECIMAL, VARCHAR, BOOLEAN, ARRAY, OBJECT and NULL_VALUE. A JSON null is a type, not an absence.
SUM coerces a numeric string. > coerces it. = does not. So a total can balance while the lookup behind it returns nothing. Cast at the path, every time.
Missing, JSON null and SQL NULL are three different states, and IS_NULL_VALUE has three answers: TRUE, FALSE and NULL.
NOT NULL on a VARIANT rejects a SQL NULL and admits a JSON null, which is Snowflake agreeing that the two are different.
A VARIANT column can carry a default that is not valid JSON. It is accepted at create time and fails on the first insert that uses it.
TRY_CAST will not take a VARIANT or produce one. Going through VARCHAR and the TRY_TO_ family works, and throws the type information away on the way.
Reading past the end of an array gives NULL. A literal negative index is a compile error, and a computed one is NULL — so an empty array goes quiet rather than raising.
::ARRAY on an object wraps it in a one-element array instead of failing, which turns a clear error into a wrong answer.
Structured ARRAY(t), OBJECT(...) and MAP(k,v) check the shape on the way in and come back already typed, so no cast at the path. Neither the catalog nor TYPEOF shows that a column is typed.
An OBJECT cannot hold a duplicate key by any route. OBJECT_INSERT with its fourth argument set to TRUE is the only way to overwrite one.
Nothing was missing from these four documents and three things were still wrong. NOT NULL catches none of them. The shape checks are yours to write.

Next in this series

Episode 87 — getting text into a VARIANT. PARSE_JSON, TRY_PARSE_JSON, CHECK_JSON and TO_JSON, and what Snowflake is willing to call valid JSON — starting with that trailing comma.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the demo script for this episode, including the queries that failed, which are shown as the errors Snowflake returned. The EVENTS_JSON and TYPED_SHAPES data is invented demo data created by the setup scripts above. Nothing here is timed and no performance claims are made anywhere in this series. Type behaviour follows the Snowflake semi-structured data type documentation.

Welcome To TechBrothersIT

Label

Snowflake SQL Tutorial 86 - The VARIANT Type, TYPEOF, IS_NULL_VALUE - Semi-Structured Data

Let's create some sample data

Let's read the same path on every row

So what can a VARIANT actually hold?

Now let's add that column up

Now let's filter on it instead

Missing is not the same as null

Let's build both cases in one row

Can a VARIANT column be NOT NULL?

What about a DEFAULT on a VARIANT column?

So which defaults do work?

What happens when we ask a VARIANT to do the wrong thing?

Can TRY_CAST save us?

Let's read an element out of an array

What if we want the type checked for us?

So where does the typing actually show up?

And there is a second payoff, on the way out

What about duplicate keys?

Nothing is missing, and three things are wrong

One last thing: those quotes in the grid

Key points

No comments:

Post a Comment