ruchi khanuja

Loading JSON data into snowflake by running copy command.Here the column type of the table where JSON data will be loaded is of VARIANT type.

copy into <table name> from @stagename file_format = (type=json);

For querying the JSON data either parse through JSON arrays or use FLATTEN function as shown below:

select <table_column_name>:<json_element_name> 
from
<table_name>;


select <table_column_name>:<json_element_name>::<casting_data_type>
from
<table_name>;

select value:<repeating_json_element>::<casting_data_type>
from
<table_name>,
table (flatten( <variant_column_name>,<json_element_name_tobe_flattened> )) ;

Using GET_PATH extract value of JSON data by providing the path name.

select get_path( <variant_column_name>,<root_json_element>.<repeating_json_element>) 
from
<table_name>;

--

--