Fetching data from snowflake table and insert JSON values to VARIANT column in another snowflake table

Question

I am trying to fetch data from a Snowflake table Employee and insert values to VARIANT column of another snowflake table in JSON format.

EMP_ID	EMP_NAME	SALARY	POSITION
1	ABC	100	ENGINEER
2	DEF	300	MANAGER
3	GHI	500	DIRECTOR

Expected JSON format:

{
"EMP_ID":"1",
"EMP_NAME":"ABC",
"SALARY":"100",
"POSITION":"ENGINEER"
}
{
"EMP_ID":"2",
"EMP_NAME":"DEF",
"SALARY":"300",
"POSITION":"MANAGER"
}
{
"EMP_ID":"3",
"EMP_NAME":"GHI",
"SALARY":"500",
"POSITION":"DIRECTOR"
}

The above JSON formatted data should be loaded to a table EMP_JSON(load_data VARIANT, load_date TIMESTAMP_LTZ).

I tried hardcoding the values. But I would like to fetch the values dynamically.

INSERT INTO EMP_JSON
SELECT PARSE_JSON('{"EMP_ID":"1", "EMP_NAME":"ABC", "SALARY":"100", "POSITION":"ENGINEER"}'), CURRENT_TIMESTAMP AS LOAD_DATE ;

Could you please tell me how to load such JSON value to a variant column in Snowflake?

Gokhan Atil · Accepted Answer · 2021-05-11 11:04:10Z

1

As it's mentioned, you can use object_construct to generate JSON object. The important point is, you can call object_construct with * parameter to read all columns dynamically (you don't need to specify columns) and produce the JSON:

create table emp( EMP_ID number, EMP_NAME varchar, SALARY number, POSITION varchar );
INSERT INTO emp(EMP_ID,EMP_NAME,SALARY,POSITION) VALUES (1,'ABC',100,'ENGINEER');
INSERT INTO emp(EMP_ID,EMP_NAME,SALARY,POSITION) VALUES (2,'DEF',300,'MANAGER');
INSERT INTO emp(EMP_ID,EMP_NAME,SALARY,POSITION) VALUES (3,'GHI',500,'DIRECTOR');

create table EMP_JSON(load_data VARIANT, load_date TIMESTAMP_LTZ);

insert into EMP_JSON select object_construct(*), current_timestamp from emp;

select * from EMP_JSON;

If you want to select specific columns:

insert into EMP_JSON select select object_construct('EMP_ID',EMP_ID, 'EMP_NAME', EMP_NAME), current_timestamp from emp;

OBJECT_CONSTRUCT https://docs.snowflake.com/en/sql-reference/functions/object_construct.html

edited May 11, 2021 at 11:04

answered May 11, 2021 at 7:46

Gokhan Atil

10.6k2 gold badges14 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

vvazza Over a year ago

Thank you ! What if i want to select specific columns from the table?

Gokhan Atil Over a year ago

Added to my response

vvazza Over a year ago

If there is a table with 100 columns, and I would like to eliminate 3 columns from the table while using object_construct, is there any way we can eliminate three columns from the selection? Or would it require explicit selection of 97 other columns?

Gokhan Atil Over a year ago

Yes it's possible, please check OBJECT_DELETE docs.snowflake.com/en/sql-reference/functions/…

vvazza Over a year ago

Wow ! Thank you so much. Much appreciated :)

|

H Roy · Accepted Answer · 2021-05-11 04:52:45Z

1

@vvazza, use the object_construct function to select the data from table and it returns a JSON object and that can go to variant column.

answered May 11, 2021 at 4:52

H Roy

6356 silver badges10 bronze badges

4 Comments

vvazza Over a year ago

Thank you ! What if i want to select specific columns from the table?

vvazza Over a year ago

If there is a table with 100 columns, and I would like to eliminate 3 columns from the table while using object_construct, is there any way we can eliminate three columns from the selection? Or would it require explicit selection of 97 other columns?

H Roy Over a year ago

You can write a stored procedure which can describe the table and you can use result_scan table function to get the column names as table structure and then take input for the columns which need to be eliminated and process it to generate it dynamically. If time permits, I could share the code sample.

vvazza Over a year ago

That'd be really helpful :) Could you please share it? :)

Collectives™ on Stack Overflow

Fetching data from snowflake table and insert JSON values to VARIANT column in another snowflake table

2 Answers 2

6 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related