Question 109718 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Json – 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?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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.


  2. 0

    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};
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search