Snowflake SQL Tutorial 95 - Structured Types - ARRAY(INT), OBJECT and MAP - It Refuses

Snowflake SQL · Episode 95

In this post we are going to cover structured types in Snowflake: ARRAY(INT), OBJECT(sku VARCHAR, qty INT) and MAP(VARCHAR, INT).

A structured type is an array, an object or a map with its shape written into the column definition. A plain ARRAY will hold anything you put in it. An ARRAY(INT) will hold integers and nothing else, and it says so in the CREATE TABLE.

That one difference changes what Snowflake does with a bad value. Everything else in this level ran happily and gave us the wrong answer. This one refuses.

We will start by putting a loose column and a typed column side by side, then ask the catalog what it built, then try to break it, and finish with what the declaration buys us when we read the data back.

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

On this page

1. Let's create some sample data
2. Now let's ask the catalog what it built
3. Now let's try DESCRIBE TABLE instead
4. Can TYPEOF tell them apart?
5. Now let's build a typed table of our own
6. Let's insert a row that fits
7. Now let's break it: three casts that refuse
8. But does the column refuse?
9. A declared key is not optional
10. Now let's read a typed value with no cast at all
11. Now the same thing on the loose object
12. Do the array and object functions still work?
13. Let's flatten a typed array and a typed map
14. What is the difference between an OBJECT and a MAP?
15. Can we cast between typed and loose?
16. One last thing: reading a key that was never declared

Let's create some sample data

We need one table with both versions of each shape in it, so we can compare them in the same query. The script below builds it.

Copy this into a Snowflake worksheet and run it once.

Sample data

-- Sample data for this post. One table, with the loose and the typed
-- version of each shape side by side.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

CREATE OR REPLACE TABLE TYPED_SHAPES (
    ROW_ID        INT,
    LOOSE_ARRAY   ARRAY,                              -- anything, in any mix
    TYPED_ARRAY   ARRAY(INT),                         -- integers only
    LOOSE_OBJECT  OBJECT,                             -- any keys at all
    TYPED_OBJECT  OBJECT(sku VARCHAR, qty INT),       -- exactly these two keys
    TYPED_MAP     MAP(VARCHAR, INT)                   -- any keys, integer values
);

-- The structured values will not go in through VALUES, so this is an
-- INSERT ... SELECT. Have a look at the loose array on the first row: an
-- integer, a string and a decimal, all in the one array.
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);

Now let's look at what we just created.

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. The first column is the row id. Then there are two loose columns, the array and the object, and three typed ones, the array, the object and the map.

Have a look at the loose array on row 1. It holds 1, the word "two" and 3.5, all in one array. The typed array beside it holds 1, 2 and 3, because integers are all it is allowed to hold.

The loose object on row 1 has an extra key that the typed object does not have. Keep an eye on that key, because it comes back twice later on.

Now let's ask the catalog what it built

The declaration is in the CREATE TABLE above, but we should not have to go and read that. Let's ask INFORMATION_SCHEMA instead.

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

ARRAY, ARRAY, OBJECT, OBJECT, MAP.

Look at the first two of those. The loose array and the typed array both come back as plain ARRAY. The loose object and the typed object both come back as plain OBJECT. The catalog will not tell us which one carries a declaration.

Maybe the detail is in one of the other columns, so let's ask for all of them.

Query

SELECT COLUMN_NAME, DATA_TYPE, NUMERIC_PRECISION, NUMERIC_SCALE,
       CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME, COMMENT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'TYPED_SHAPES'
ORDER BY ORDINAL_POSITION;

Result6 rows · 7 columns · scroll →

COLUMN_NAME	DATA_TYPE	NUMERIC_PRECISION	NUMERIC_SCALE	CHARACTER_MAXIMUM_LENGTH	COLLATION_NAME	COMMENT
ROW_ID	NUMBER	38	0	NULL	NULL	NULL
LOOSE_ARRAY	ARRAY	NULL	NULL	NULL	NULL	NULL
TYPED_ARRAY	ARRAY	NULL	NULL	NULL	NULL	NULL
LOOSE_OBJECT	OBJECT	NULL	NULL	NULL	NULL	NULL
TYPED_OBJECT	OBJECT	NULL	NULL	NULL	NULL	NULL
TYPED_MAP	MAP	NULL	NULL	NULL	NULL	NULL

Precision, scale, length, collation and comment are all NULL on every one of the five structured columns. Only ROW_ID, which is an ordinary NUMBER, has anything in them.

So INFORMATION_SCHEMA is no help here at all. That is worth knowing before you write a schema report that reads from it.

Now let's try DESCRIBE TABLE instead

There is one other place to look. Let's describe the table.

Query

DESCRIBE TABLE TYPED_SHAPES;

Result6 rows · 13 columns · scroll →

name	type	kind	null?	default	primary key	unique key	check	expression	comment	policy name	privacy domain	write default
ROW_ID	NUMBER(38,0)	COLUMN	Y	NULL	N	N	NULL	NULL	NULL	NULL	NULL	NULL
LOOSE_ARRAY	ARRAY	COLUMN	Y	NULL	N	N	NULL	NULL	NULL	NULL	NULL	NULL
TYPED_ARRAY	ARRAY(NUMBER(38,0))	COLUMN	Y	NULL	N	N	NULL	NULL	NULL	NULL	NULL	NULL
LOOSE_OBJECT	OBJECT	COLUMN	Y	NULL	N	N	NULL	NULL	NULL	NULL	NULL	NULL
TYPED_OBJECT	OBJECT(sku VARCHAR(16777216), qty NUMBER(38,0))	COLUMN	Y	NULL	N	N	NULL	NULL	NULL	NULL	NULL	NULL
TYPED_MAP	MAP(VARCHAR(16777216), NUMBER(38,0))	COLUMN	Y	NULL	N	N	NULL	NULL	NULL	NULL	NULL	NULL

And there it is. ARRAY(NUMBER(38,0)), OBJECT(sku VARCHAR(16777216), qty NUMBER(38,0)) and MAP(VARCHAR(16777216), NUMBER(38,0)).

The loose columns on the same grid are still plain ARRAY and OBJECT. So the declaration is real, it is stored, and DESCRIBE TABLE is where you can see it.

Where the INT went

Notice what happened to INT on the way in. We wrote ARRAY(INT) and Snowflake stored ARRAY(NUMBER(38,0)), because INT is a synonym for NUMBER(38,0). Episode 97 is about that and nothing else.

Can TYPEOF tell them apart?

TYPEOF was the subject of Episode 94, and it is the obvious thing to reach for here. Let's run it over all five structured columns.

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

ARRAY, ARRAY, OBJECT, OBJECT and then, for the map, OBJECT again.

So TYPEOF cannot tell typed from loose either, and on top of that it calls a MAP an OBJECT. DESCRIBE TABLE is the only one of the three that knows.

Now let's build a typed table of our own

Comparing columns is one thing. What we actually want to know is what a typed column does when we try to put something wrong in it, so let's make a table to break.

A table with three declared columns

CREATE OR REPLACE TABLE STRUCTURED_PROBE (
    ID      INT,
    SCORES  ARRAY(INT),
    ITEM    OBJECT(sku VARCHAR, qty INT),
    LOOKUP  MAP(VARCHAR, INT)
);

Result1 row · 1 column

status
Table STRUCTURED_PROBE successfully created.

Three typed columns and an id. Let's check both catalogs again, so we know we are looking at the same thing we looked at before.

INFORMATION_SCHEMA

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

Result4 rows · 3 columns

COLUMN_NAME	DATA_TYPE	IS_NULLABLE
ID	NUMBER	YES
SCORES	ARRAY	YES
ITEM	OBJECT	YES
LOOKUP	MAP	YES

DESCRIBE TABLE

DESCRIBE TABLE STRUCTURED_PROBE;

Result4 rows · 13 columns · scroll →

name	type	kind	null?	default	primary key	unique key	check	expression	comment	policy name	privacy domain	write default
ID	NUMBER(38,0)	COLUMN	Y	NULL	N	N	NULL	NULL	NULL	NULL	NULL	NULL
SCORES	ARRAY(NUMBER(38,0))	COLUMN	Y	NULL	N	N	NULL	NULL	NULL	NULL	NULL	NULL
ITEM	OBJECT(sku VARCHAR(16777216), qty NUMBER(38,0))	COLUMN	Y	NULL	N	N	NULL	NULL	NULL	NULL	NULL	NULL
LOOKUP	MAP(VARCHAR(16777216), NUMBER(38,0))	COLUMN	Y	NULL	N	N	NULL	NULL	NULL	NULL	NULL	NULL

Same story. ARRAY, OBJECT, MAP from the catalog, and the full declaration from DESCRIBE.

Let's insert a row that fits

Before we break anything, let's prove the table works. This row matches all three declarations.

INSERT

INSERT INTO STRUCTURED_PROBE (ID, SCORES, ITEM, LOOKUP)
SELECT 1,
       [10, 20, 30]::ARRAY(INT),
       {'sku': 'A-100', 'qty': 8}::OBJECT(sku VARCHAR, qty INT),
       {'a': 1, 'b': 2}::MAP(VARCHAR, INT);

Result1 row · 1 column

number of rows inserted
1

One row inserted. Now let's read it back and do a little work on it while we are there.

Query

SELECT ID, SCORES, ITEM, LOOKUP,
       ARRAY_SIZE(SCORES)   AS SCORE_COUNT,
       ITEM:sku             AS THE_SKU,
       ITEM:qty + 1         AS QTY_PLUS_ONE
FROM STRUCTURED_PROBE
ORDER BY ID;

Result1 row · 7 columns · scroll →

ID	SCORES	ITEM	LOOKUP	SCORE_COUNT	THE_SKU	QTY_PLUS_ONE
1	[ 10, 20, 30 ]	{ "sku": "A-100", "qty": 8 }	{ "a": 1, "b": 2 }	3	A-100	9

Three scores, the sku A-100, and the quantity plus one is 9. Note what is not in that query: there is no cast anywhere in it. We will come back to that at the end, because it is the whole payoff.

Now let's break it: three casts that refuse

Let's start with casts rather than the table, because they are quicker to try. First, a typed array with a string in the middle of it.

Cast — refused

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

Typed object schema mismatch in conversion. It refused. After everything in this level that ran and gave us the wrong answer, that is a pleasant sight.

Now a typed object with a key that was never declared.

Cast — refused

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

Same error. And now a typed map with a string where the declaration says there should be an integer.

Cast — refused

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

Same error again. The element type, the key list and the value type are all checked, and all three refusals come back as 220000 (22000).

But does the column refuse?

Three refusals is encouraging, but those were casts. A cast refusing a literal we typed out by hand is a nicety. The question that matters is whether the column refuses, because that is the difference between a suggestion and a constraint.

So let's try to insert a loose array into the typed SCORES column.

INSERT — refused

INSERT INTO STRUCTURED_PROBE (ID, SCORES)
SELECT 2, [1, 'two']::ARRAY;

Query failed — Snowflake returned an error

001010 (42846): SQL compilation error:
incompatible types: [ARRAY] and [ARRAY(NUMBER(38,0))]

Incompatible types: [ARRAY] and [ARRAY(NUMBER(38,0))]. It refused at the column, and it named both sides. That is a constraint.

Now the object. Let's insert one with the qty key simply left out.

INSERT — refused

INSERT INTO STRUCTURED_PROBE (ID, ITEM)
SELECT 3, {'sku': 'B-220'}::OBJECT(sku VARCHAR);

Query failed — Snowflake returned an error

002023 (22000): SQL compilation error:
Expression type does not match column data type, expecting OBJECT(sku VARCHAR(16777216), qty NUMBER(38,0)) but got OBJECT(sku VARCHAR(134217728)) for column ITEM

Expression type does not match column data type, expecting OBJECT(sku VARCHAR(16777216), qty NUMBER(38,0)) but got OBJECT(sku VARCHAR(134217728)) for column ITEM. It tells us exactly what it wanted and exactly what we sent.

Those two are not the same error as the casts above. The casts gave us 220000 and these give us 001010 and 002023. Different errors, same verdict: the row does not go in.

Let's count the table.

Query

SELECT COUNT(*) AS ROWS_IN_THE_PROBE FROM STRUCTURED_PROBE;

Result1 row · 1 column

ROWS_IN_THE_PROBE
1

One row, which is the good one from earlier. The two bad inserts left nothing behind. Nothing partially landed, nothing was quietly coerced, and nothing warned.

A declared key is not optional

Go back and read that second refusal again, because it is stricter than it first looks. We did not put a wrong value in. We left a key out, expecting a null qty, the way a missing column in an INSERT gives you a null column.

It does not work like that. So read OBJECT(sku VARCHAR, qty INT) not as these keys are allowed but as exactly these keys, all of them, every time.

That is stricter than an ordinary table, where a column you say nothing about is simply NULL. The same query written as a cast rather than an insert is in the demo script for this episode, and its output is not in the capture file, so this page shows the query without a result pane.

Cast — not captured for this episode

-- This one is in the demo script for the episode, and its output is NOT in
-- the capture file for this episode. There is no result pane under it below.
SELECT {'sku': 'C-310'}::OBJECT(sku VARCHAR, qty INT)   AS PARTIAL_,
       ARRAY_SIZE(OBJECT_KEYS(
         {'sku': 'C-310'}::OBJECT(sku VARCHAR, qty INT)::VARIANT)) AS ITS_KEY_COUNT;

The demo script records that one as failing as well. It is the author's note rather than a grid this page can show you, so take the two refusals in the section above as the evidence and this as the shape of the advice.

The design advice

If you need an optional key, a typed OBJECT is the wrong shape for your data. Use a MAP, where the keys are data rather than declaration, or use a loose OBJECT and carry on doing what Episode 88 and Episode 93 showed us.

Now let's read a typed value with no cast at all

That is the cost. Here is what we get for it, and it is quiet enough to miss.

Let's read two keys out of the typed object and the first element out of the typed array, and do arithmetic on both without casting anything.

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

9 and 5 from the object, 2 and 11 from the array. No ::INT anywhere. The sku comes back as text with no quotes around it, not as a quoted variant.

The type is already right, because we declared it once.

Now the same thing on the loose object

Let's do exactly that again on the loose column, which is the one we have been using for the whole of this level.

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

The sku comes back as "A-100", in quotes, because it is still a variant. TYPEOF says VARCHAR, and the quantity needed a ::INT before the arithmetic. That is Episode 89 all over again.

Now let's try the arithmetic without the cast and see what happens.

Query

SELECT ROW_ID,
       LOOSE_OBJECT:qty            AS QTY_AS_VARIANT,
       LOOSE_OBJECT:qty + 1        AS DID_IT_WORK
FROM TYPED_SHAPES
WHERE ROW_ID = 1;

Result1 row · 3 columns

ROW_ID	QTY_AS_VARIANT	DID_IT_WORK
1	8	9.0

9.0. Look at that carefully. It worked, no error, no warning, and the answer silently became a float. The typed column gave us 9 for the same arithmetic.

Two characters of declaration, and the type of your arithmetic changes underneath you. If that number goes into a comparison or a key, you will find out later rather than now.

Do the array and object functions still work?

Episode 92 and Episode 93 were spent on the array and object functions. Let's check they still work on a typed value.

Query

SELECT ROW_ID,
       ARRAY_SIZE(TYPED_ARRAY)                        AS ARRAY_SIZE_,
       ARRAY_CONTAINS(2::VARIANT, TYPED_ARRAY::VARIANT) AS ARRAY_CONTAINS_,
       ARRAY_SLICE(TYPED_ARRAY::VARIANT, 0, 2)        AS ARRAY_SLICE_,
       ARRAY_SORT(TYPED_ARRAY::VARIANT)               AS ARRAY_SORT_
FROM TYPED_SHAPES
ORDER BY ROW_ID;

Result2 rows · 5 columns

ROW_ID	ARRAY_SIZE_	ARRAY_CONTAINS_	ARRAY_SLICE_	ARRAY_SORT_
1	3	TRUE	[ 1, 2 ]	[ 1, 2, 3 ]
2	2	FALSE	[ 10, 20 ]	[ 10, 20 ]

ARRAY_SIZE, ARRAY_CONTAINS, ARRAY_SLICE and ARRAY_SORT all behave. ARRAY_CONTAINS is TRUE on row 1 and FALSE on row 2, which is right, because we are looking for 2.

Now OBJECT_KEYS on the typed object and on the loose one, side by side.

Query

SELECT ROW_ID,
       OBJECT_KEYS(TYPED_OBJECT::VARIANT)                   AS ITS_KEYS,
       ARRAY_SIZE(OBJECT_KEYS(TYPED_OBJECT::VARIANT))       AS KEY_COUNT,
       OBJECT_KEYS(LOOSE_OBJECT)                            AS LOOSE_KEYS,
       ARRAY_SIZE(OBJECT_KEYS(LOOSE_OBJECT))                AS LOOSE_KEY_COUNT
FROM TYPED_SHAPES
ORDER BY ROW_ID;

Result2 rows · 5 columns

ROW_ID	ITS_KEYS	KEY_COUNT	LOOSE_KEYS	LOOSE_KEY_COUNT
1	[ "qty", "sku" ]	2	[ "extra", "qty", "sku" ]	3
2	[ "qty", "sku" ]	2	[ "price", "sku" ]	2

The typed object has two keys on both rows, because two is all it can ever have. The loose object has three keys on row 1 and two on row 2, because row 1 is the one with the extra key in it.

That ragged key count is the thing Episode 93 spent a whole post on. A typed object cannot be ragged.

Let's flatten a typed array and a typed map

FLATTEN was Episode 90 and Episode 91. Let's flatten the typed array first.

Query

SELECT T.ROW_ID, F.INDEX, F.VALUE, TYPEOF(F.VALUE) AS ELEMENT_TYPE
FROM TYPED_SHAPES T,
     LATERAL FLATTEN(INPUT => T.TYPED_ARRAY::VARIANT) F
ORDER BY T.ROW_ID, F.INDEX;

Result5 rows · 4 columns

ROW_ID	INDEX	VALUE	ELEMENT_TYPE
1	0	1	INTEGER
1	1	2	INTEGER
1	2	3	INTEGER
2	0	10	INTEGER
2	1	20	INTEGER

Five rows, three from the first array and two from the second, and every element type is INTEGER. Not a mixture, because a mixture cannot get in.

Now the map, which gives us a key as well as a value.

Query

SELECT T.ROW_ID, F.KEY, F.VALUE, TYPEOF(F.VALUE) AS VALUE_TYPE
FROM TYPED_SHAPES T,
     LATERAL FLATTEN(INPUT => T.TYPED_MAP::VARIANT) F
ORDER BY T.ROW_ID, F.KEY;

Result3 rows · 4 columns

ROW_ID	KEY	VALUE	VALUE_TYPE
1	a	1	INTEGER
1	b	2	INTEGER
2	x	9	INTEGER

Three rows, and the value type is INTEGER on every one of them. FLATTEN over a map gives you the KEY column filled in, the way it does over an object.

What is the difference between an OBJECT and a MAP?

They both read back as OBJECT from TYPEOF, so this is a fair question. The answer is in one line: an object's keys are declaration and a map's keys are data.

Let's take two map literals with different numbers of keys and cast both to the same MAP(VARCHAR, INT).

Query

SELECT {'a': 1, 'b': 2}::MAP(VARCHAR, INT)                      AS A_MAP,
       ARRAY_SIZE(OBJECT_KEYS({'a': 1, 'b': 2}::MAP(VARCHAR, INT)::VARIANT))
         AS MAP_KEY_COUNT,
       {'a': 1, 'b': 2, 'c': 3}::MAP(VARCHAR, INT)              AS A_BIGGER_MAP,
       ARRAY_SIZE(OBJECT_KEYS(
         {'a': 1, 'b': 2, 'c': 3}::MAP(VARCHAR, INT)::VARIANT)) AS BIGGER_KEY_COUNT;

Result1 row · 4 columns

A_MAP	MAP_KEY_COUNT	A_BIGGER_MAP	BIGGER_KEY_COUNT
{ "a": 1, "b": 2 }	2	{ "a": 1, "b": 2, "c": 3 }	3

Two keys in the first one and three in the second, and the same declaration accepted both. A typed object could not do that, as we saw two sections ago.

And the keys themselves can be anything, as long as they are the declared key type.

Query

SELECT {'anything': 1, 'at_all': 2}::MAP(VARCHAR, INT)          AS ARBITRARY_KEYS,
       ARRAY_SIZE(OBJECT_KEYS(
         {'anything': 1, 'at_all': 2}::MAP(VARCHAR, INT)::VARIANT)) AS HOW_MANY;

Result1 row · 2 columns

ARBITRARY_KEYS	HOW_MANY
{ "anything": 1, "at_all": 2 }	2

Query

SELECT {'1': 10, '2': 20}::MAP(VARCHAR, INT)  AS STRING_KEYS,
       TYPEOF({'1': 10, '2': 20}::MAP(VARCHAR, INT)::VARIANT) AS ITS_TYPE;

Result1 row · 2 columns

STRING_KEYS	ITS_TYPE
{ "1": 10, "2": 20 }	OBJECT

anything and at_all went in without being named anywhere, and so did the keys '1' and '2'. The last column is the reminder: TYPEOF on that map still says OBJECT.

Can we cast between typed and loose?

In one direction, easily. Let's start with a reminder of what a loose array will take.

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

An integer, a string and a decimal, size three, type ARRAY. The loose array takes the mixture happily, exactly as it has all level.

Now let's go from typed to loose, which is throwing information away and should therefore be fine.

Query

SELECT ROW_ID,
       TYPED_ARRAY                       AS AS_DECLARED,
       TYPED_ARRAY::ARRAY                AS AS_A_LOOSE_ARRAY,
       TYPED_ARRAY::VARIANT              AS AS_A_VARIANT,
       TYPEOF(TYPED_ARRAY::ARRAY::VARIANT) AS LOOSE_TYPE
FROM TYPED_SHAPES
WHERE ROW_ID = 1;

Result1 row · 5 columns

ROW_ID	AS_DECLARED	AS_A_LOOSE_ARRAY	AS_A_VARIANT	LOOSE_TYPE
1	[ 1, 2, 3 ]	[ 1, 2, 3 ]	[ 1, 2, 3 ]	ARRAY

The same three elements as declared, as a loose array and as a variant, and TYPEOF on the loose version says ARRAY. Nothing complained.

Here is the typed column cast to ARRAY(INT) with the loose column printed beside it, so you can see what we are about to ask for.

Query

SELECT ROW_ID,
       LOOSE_ARRAY                           AS THE_LOOSE_ONE,
       TYPED_ARRAY::ARRAY(INT)               AS ALREADY_TYPED
FROM TYPED_SHAPES
WHERE ROW_ID = 1;

Result1 row · 3 columns

ROW_ID	THE_LOOSE_ONE	ALREADY_TYPED
1	[ 1, "two", 3.5 ]	[ 1, 2, 3 ]

That one only re-types a column that was already typed, so of course it works. The interesting cast is the loose column, the one with the word "two" in it, going into ARRAY(INT).

Cast — refused

SELECT LOOSE_ARRAY::ARRAY(INT) AS SHOULD_NOT_GET_THIS_FAR
FROM TYPED_SHAPES
WHERE ROW_ID = 1;

Query failed — Snowflake returned an error

220000 (22000): Typed object schema mismatch in conversion

Typed object schema mismatch in conversion. It refuses, which means a typed cast is also a way to check a loose column rather than just convert it.

One last thing: reading a key that was never declared

There is one more half of this to cover, and it is the direct answer to Episode 88. In that post PAYLOAD:Amount with a capital A returned NULL on every row, SUM returned NULL, and nothing anywhere said why.

The question is what a typed object does when you ask it for a key it does not have. Our typed object has sku and qty, and the loose one beside it is the one with extra in it, as the key counts two sections ago showed.

Query — not captured for this episode

-- Also in the demo script, also not in the capture file. There is no result
-- pane under this one either.
SELECT ROW_ID,
       TYPED_OBJECT:sku           AS A_DECLARED_KEY,
       TYPED_OBJECT:extra         AS AN_UNDECLARED_KEY,
       TYPED_OBJECT:extra IS NULL AS IT_IS_NULL,
       LOOSE_OBJECT:extra         AS THE_LOOSE_ONE_HAS_IT
FROM TYPED_SHAPES
WHERE ROW_ID = 1;

This is the second of the two queries whose output is in the demo script but not in the capture file, so there is no result pane under it. The demo script records it as failing, with the field name in the message, rather than returning a NULL column.

Take that as the author's account and not as evidence from this page. What this page has shown you is the shape it fits: every other wrong thing we tried in this post came back as an error with the detail in it, where the loose equivalent would have run.

Last of all, let's put the probe table away.

Cleanup

DROP TABLE IF EXISTS STRUCTURED_PROBE;

Result1 row · 1 column

status
STRUCTURED_PROBE successfully dropped.

Key points

• A structured type is an array, object or map with its shape in the column definition: ARRAY(INT), OBJECT(sku VARCHAR, qty INT), MAP(VARCHAR, INT).
• INFORMATION_SCHEMA hides the declaration. It reports ARRAY, OBJECT and MAP and leaves precision, scale, length and collation NULL.
• DESCRIBE TABLE is the only place the real declaration appears. TYPEOF cannot tell typed from loose, and it calls a MAP an OBJECT.
• A typed cast refuses the wrong element type, an undeclared key and a wrong value type, all with 220000 (22000) Typed object schema mismatch in conversion.
• The column refuses too, with its own errors: 001010 for incompatible array types and 002023 for an object whose shape does not match. That is a constraint, not a nicety.
• A declared key is not optional. Leaving one out is refused, which is stricter than a table column, where a value you do not supply is simply NULL.
• So read OBJECT(a, b) as exactly these keys, every time. If you need an optional key, use a MAP or a loose OBJECT.
• The payoff is on the read. TYPED_OBJECT:qty + 1 is 9 with no cast, while the loose version of the same arithmetic is 9.0 — it worked, and it silently became a float.
• ARRAY_SIZE, ARRAY_CONTAINS, ARRAY_SLICE, ARRAY_SORT, OBJECT_KEYS and FLATTEN all work on typed values.
• An object's keys are declaration. A map's keys are data. Both read back as OBJECT, which is why DESCRIBE matters.
• Typed to loose is free. Loose to typed is a check, and it fails on a mixed array.

Next in this series

Episode 96 — XML: PARSE_XML, CHECK_XML and XMLGET. It is the last post of Level 8, and it is the one where TRY_PARSE_XML turns out not to exist.

Every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the demo script for this episode. Two queries appear without a result pane and the page says so where they appear: the missing-key cast, and the read of an undeclared key from a typed object. Both are in the episode's demo script with their output recorded there, and neither is in this episode's capture file, so they are the author's account rather than grids this page can show. The TYPED_SHAPES rows are invented demo data, and the extra key on the first loose object is there on purpose. Every count in the text above is read off one of the grids on this page. Nothing is timed and no performance claims are made anywhere in this series. Type behaviour follows the Snowflake structured types documentation.