Using Snowflake's COPY INTO command populate data to specific columns

Question

table definition:

create table dummy_data(my_name VARCHAR,
my_city VARCHAR,
my_team VARCHAR,
id VARCHAR);

Stage : mystage
file format : MY_JSON_FORMAT
filename : mydetails.json

the column names in json are defined as : my name,my city,my team (they have space in them)

how can I populate table "dummy_data" with this json file when there is a mismatch in column names.

I tried

COPY INTO dummy_data (my_name ,my_city ,my_team )
FROM @mystage/mydetails.json
FILE_FORMAT = MY_JSON_FORMAT;

I get this error: JSON file format can produce one and only one column of type variant, object, or array. Load data into separate columns using the MATCH_BY_COLUMN_NAME copy option or copy with transformation.

I need help with this, thank you.

Answer 1

If you try and load a json file “as is” you can only load it into a variant column. If you want to load fields from the json file into specific columns then this is described in the Snowflake documentation here