Stage with JSON load

CREATE FILE FORMAT:

  CREATE OR REPLACE FILE FORMAT my_json_format

  TYPE = 'JSON'

  COMPRESSION = 'AUTO'

  ENABLE_OCTAL = FALSE

  ALLOW_DUPLICATE = FALSE

  STRIP_OUTER_ARRAY = TRUE

  IGNORE_UTF8_ERRORS = FALSE;

CREATE INTERNAL NAMED STAGE:

  CREATE OR REPLACE STAGE my_internal_stage

  FILE_FORMAT = my_json_format;

CREATE TABLE:

CREATE OR REPLACE TABLE json_data_raw (

  id NUMBER AUTOINCREMENT,

  raw_json VARIANT,

  load_time TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP

);

LIST THE FILES IN THE STAGE:

LIST @my_internal_stage; 

REMOVE THE FILES FROM STAGE:

REMOVE @my_internal_stage; 

COPY COMMAND TO LOAD THE DATA (INTO A PARTICULAR COLUMN IN THE TABLE)

COPY INTO json_data_raw(raw_json)

FROM @my_internal_stage/samplejson.json

FILE_FORMAT = (FORMAT_NAME = 'my_json_format');

OUTPUT OF THE LOAD:

 QUERY THE TABLE:

SELECT

RAW_JSON:name::string AS emp_name,

RAW_JSON:email::string AS emp_email,

RAW_JSON:isActive::boolean AS emp_status

FROM json_data_raw;