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
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" }');
<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.
2ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJobm5tYGyFeoCOoaawZaSkeqq60p6prWWmlr%2Bqrc2tZJ2ZpJZ6qrrTqGSaZaOjvLiyy5qinmWklq%2BtsQ%3D%3D