insert into snowflake table from multiple values with arrays and objects

Question

I'm trying to insert multiple rows with arrays and structs, via an SQL statement, into Snowflake. To insert arrays of values into a column I am using the ARRAY_CONSTUCT function and to insert the structures/dictionaries/objects I am using the OBJECT_CONSTRUCT function.

E.g.

insert into "MY_DB"."MY_SCHEMA"."MY_TABLE"
("ID", "TS", "TEXT", "DATEONLY", "ARRAY", "OBJ", "BOOL", "DOUBLE", "INT", "DEC_18_9")
values
('id1', '2020-11-26 14:01:27.868', '19', '2020-11-26',
  ARRAY_CONSTRUCT(0, 1, 2), OBJECT_CONSTRUCT('this', 'is', 'my', 'object', 'query',
  OBJECT_CONSTRUCT('field1', 'one', 'field2', ARRAY_CONSTRUCT('field2a', 'two')),
  'field3', ARRAY_CONSTRUCT(3, 4, 5)), FALSE, 178482300.96318725, 9, 12345619.876543190),
('id2', '2020-11-26 14:01:27.868', '19', '2020-11-26',
  ARRAY_CONSTRUCT(0, 1, 2), OBJECT_CONSTRUCT('this', 'is', 'my', 'object', 'query',
  OBJECT_CONSTRUCT('field1', 'one', 'field2', ARRAY_CONSTRUCT('field2a', 'two')),
  'field3', ARRAY_CONSTRUCT(3, 4, 5)), FALSE, 178482300.96318725, 9, 12345619.876543190)
;

This results in an exception:

SQL compilation error: Invalid expression [ARRAY_CONSTRUCT(0, 1, 2)] in VALUES clause

Inserting a single row using this syntax works:

insert into "MY_DB"."MY_SCHEMA"."MY_TABLE"
("ID", "TS", "TEXT", "DATEONLY", "ARRAY", "OBJ", "BOOL", "DOUBLE", "INT", "DEC_18_9")
select 'id1', '2020-11-26 14:01:27.868', '19', '2020-11-26',
  ARRAY_CONSTRUCT(0, 1, 2), OBJECT_CONSTRUCT('this', 'is', 'my', 'object', 'query',
  OBJECT_CONSTRUCT('field1', 'one', 'field2', ARRAY_CONSTRUCT('field2a', 'two')),
  'field3', ARRAY_CONSTRUCT(3, 4, 5)), FALSE, 178482300.96318725, 9, 12345619.876543190
;

However, it is unclear if and how this can be used for inserting multiple rows.

The table definition is:

create or replace temporary table "MY_DB"."MY_SCHEMA"."MY_TABLE"
("ID" STRING NOT NULL, "TS" TIMESTAMP NOT NULL, "TEXT" STRING,
  "DATEONLY" DATE, "ARRAY" ARRAY, "OBJ" OBJECT, "BOOL" BOOLEAN,
  "DOUBLE" DOUBLE, "INT" BIGINT, "DEC_18_9" NUMBER (18, 9)
);

What is the correct way to do this? (Do I need to spill the data into a file and load from there or is there a direct way to do this?)

The only mention of this I found was in an "answer" here.

To clarify, I am inserting data into a temporary table, from which I merge into another table, since I could not find a way to merge data via an SQL statement from values (i.e. not from a table).

Answer 1

VALUES does not support ARRAY_CONSTRUCT expression, this is why you get "Invalid expression" error:

https://docs.snowflake.com/en/sql-reference/constructs/values.html#syntax

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

You may use SELECT + UNION ALL instead of VALUES:

insert into "MY_DB"."MY_SCHEMA"."MY_TABLE"
("ID", "TS", "TEXT", "DATEONLY", "ARRAY", "OBJ", "BOOL", "DOUBLE", "INT", "DEC_18_9")
SELECT 'id1', '2020-11-26 14:01:27.868', '19', '2020-11-26',
  ARRAY_CONSTRUCT(0, 1, 2), OBJECT_CONSTRUCT('this', 'is', 'my', 'object', 'query',
  OBJECT_CONSTRUCT('field1', 'one', 'field2', ARRAY_CONSTRUCT('field2a', 'two')),
  'field3', ARRAY_CONSTRUCT(3, 4, 5)), FALSE, 178482300.96318725, 9, 12345619.876543190
UNION ALL  
SELECT 'id2', '2020-11-26 14:01:27.868', '19', '2020-11-26',
  ARRAY_CONSTRUCT(0, 1, 2), OBJECT_CONSTRUCT('this', 'is', 'my', 'object', 'query',
  OBJECT_CONSTRUCT('field1', 'one', 'field2', ARRAY_CONSTRUCT('field2a', 'two')),
  'field3', ARRAY_CONSTRUCT(3, 4, 5)), FALSE, 178482300.96318725, 9, 12345619.876543190;

Answer 2

Via trial and error, I found this solution:

INSERT INTO "MY_DB"."MY_SCHEMA"."MY_TABLE"
SELECT $1, $2, $3, $4, PARSE_JSON($5), PARSE_JSON($6), $7, $8, $9, $10
from values
('id1', '2020-11-26 14:01:27.868', '19', '2020-11-26', '[0, 1, 2]',
  '{"this": "is", "my": "object",
    "query": {"field1": "one", "field2": ["field2a", "two"], "field3": [3, 4, 5]}}',
   FALSE, 178482300.96318725, 9, 12345619.876543190),
('id2', '2020-11-26 14:01:27.868', '19', '2020-11-26', '[0, 1, 2]',
  '{"this": "is", "my": "object",
    "query": {"field1": "one", "field2": ["field2a", "two"], "field3": [3, 4, 5]}}',
   FALSE, 178482300.96318725, 9, 12345619.876543190)
;

FYI, from a brief testing, Snowflake identifies NaN, -inf and inf within the JSON without the quotes around them as floating point values e.g. {"my_field": NaN}.