How to insert VARIANT data into a Snowflake table?

Using this table:

CREATE TABLE t (i int PRIMARY KEY, j variant);

I'm trying to insert some JSON object into the table, e.g.

INSERT INTO t (i, j) VALUES (1, object_construct('a', 1));

However, that fails with:

SQL Error [2014] [22000]: SQL compilation error:
Invalid expression [OBJECT_CONSTRUCT('a', 1)] in VALUES clause

I can work around the problem with this:

INSERT INTO t (i, j) VALUES (1, null);
UPDATE t SET j = object_construct('a', 1) WHERE i = 1;

Why can't I use OBJECT_CONSTRUCT in an INSERT statement?

2 Answers

INSERT INTO ... VALUES:

Some expressions cannot be specified in the VALUES clause. As an alternative, specify the expression in a query clause. For example, you can replace:

INSERT INTO table1 (ID, varchar1, variant1)
VALUES (4, 'Fourier', PARSE_JSON('{ "key1": "value1", "key2": "value2" }'));

with:

INSERT INTO table1 (ID, varchar1, variant1)
SELECT 4, 'Fourier', PARSE_JSON('{ "key1": "value1", "key2": "value2" }');

VALUES:

<expr>

Each expression must be a constant, or an expression that can be evaluated as a constant during compilation of the SQL statement.

Most simple arithmetic expressions and string functions can be evaluated at compile time, but most other expressions cannot.

Same applies to both OBJECT_CONSTRUCT and OBJECT curly braces :

INSERT INTO t (i, j) SELECT 1, {'a', 1};

The manual suggests using INSERT .. SELECT:

To insert VARIANT data directly, use IIS (INSERT INTO ... SELECT).

I.e. this works:

INSERT INTO t (i, j) SELECT 1, object_construct('a', 1);

However, it does not offer any explanation with respect to the limitation, or why this particular workaround is needed.