CODEWITHSUNDEEP
jsonsnowflake-cloud-data-platform
vvazza
vvazza

Reputation: 397

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

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?

Upvotes: 0

Views: 1167

Answers (2)

Gokhan Atil
Gokhan Atil

Reputation: 10144

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

Upvotes: 1

H Roy
H Roy

Reputation: 635

@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.

Upvotes: 1

Related Questions