create and populate variant data type table with JSON values

Hi,

I am creating a Snowflake table :

CREATE TABLE test_json (
id INTEGER NOT NULL,
json_data variant DEFAULT ''
);
and then trying to insert value in it using below:
insert into test_json (id, json_data)
values (1,'{"firstname":"robert","lastname":"rao","address":{"street":"7777 myroad","city":"mycity"}}');

I am getting the error:
[22000][2023] SQL compilation error: Expression type does not match column data type, expecting VARIANT but got VARCHAR(90) for column JSON_DATA

do I need to do any conversion while inserting the data?
3 comments
Comment actions Permalink

If you select the whole statement and run it, does it work?

0
Comment actions Permalink

Having the same issue on update of a json field.  And there doesn't seem to be any way to tell DataGrip to cast to json, not varying.

 

The problem is that I'm trying to simply modify a JSON field object, say change {"title: ""} to {"title":"test"}  DataGrip should recognize that it's a JSON field and treat it as such.  The only alternative is to create a query manually which we shouldn't have to do.

0
Comment actions Permalink

You can use PARSE_JSON

0

Please sign in to leave a comment.