Load JSON Data into Snow flake table

My Data is follows:

 [  {
        "InvestorID": "10014-49",
        "InvestorName": "Blackstone",
        "LastUpdated": "11/23/2021"
      },
      {
        "InvestorID": "15713-74",
        "InvestorName": "Bay Grove Capital",
        "LastUpdated": "11/19/2021"
      }]

So Far Tried:

CREATE OR REPLACE TABLE STG_PB_INVESTOR (
  
  Investor_ID string,   Investor_Name string,Last_Updated DATETIME  
  
);  Created table 

create or replace file format investorformat
  type = 'JSON'
  strip_outer_array = true;
 created file format

  
  create or replace stage investor_stage
  file_format = investorformat;
  created stage
 


copy into STG_PB_INVESTOR from @investor_stage

I am getting an error:

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.

Solution 1:

You should be loading your JSON data into a table with a single column that is a VARIANT. Once in Snowflake you can either flatten that data out with a view or a subsequent table load. You could also flatten it on the way in using a SELECT on your COPY statement, but that tends to be a little slower.

Try something like this:

CREATE OR REPLACE TABLE STG_PB_INVESTOR_JSON (
  var variant 
);

create or replace file format investorformat
  type = 'JSON'
  strip_outer_array = true;
  
create or replace stage investor_stage
  file_format = investorformat;

copy into STG_PB_INVESTOR_JSON from @investor_stage;

create or replace table STG_PB_INVESTOR as
   SELECT 
     var:InvestorID::string as Investor_id,
     var:InvestorName::string as Investor_Name,
     TO_DATE(var:LastUpdated::string,'MM/DD/YYYY') as last_updated
   FROM STG_PB_INVESTOR_JSON;