upload json into a snowflake table-CodePudding

I created an empty table in Snowflake.

I want to upload a json file into this table now. One possibility would be to add the entire json's value into the first row of the first column. However, when I upload the file from my computer, and select JSON as the type, I get this error

Unable to copy files into table.
SQL compilation error: JSON file format can produce one and only one column of type variant or object or array. Use CSV file format if you want to load more than one column.

What am I doing wrong? Am I supposed to use another file type instead?

CodePudding user response：

Your column type is VARCHAR, as the error suggests, it needs to be of type variant or object or array if you want to load everything into one column from the JSON file.

CodePudding user response：

When you try to load data from a JSON file, it's expected to have only one variant column. If you want to parse JSON and load the relevant fields to specific column, you need to use transformation feature of COPY command:

copy into home_sales(city, state, zip, sale_date, price)
   from (select substr(parse_json($1):location.state_city,4), substr(parse_json($1):location.state_city,1,2),
                parse_json($1):location.zip, to_timestamp_ntz(parse_json($1):sale_date), parse_json($1):price
         from @sf_tut_stage/sales.json.gz t)

https://docs.snowflake.com/en/user-guide/script-data-load-transform-json.html