CODEWITHSUNDEEP
jsonsnowflake-stage
urmisharma
urmisharma

Reputation: 1

How to extract value from the JSON variant in snowflake?

I am copying data from S3 to snowflake as JSON variant in snowflake stage and not sure how can I extract the value from the string and load to another table.

Steps taken:

/* created json format */ create or replace file format jsonformat type='JSON' strip_outer_array=true;

/*created table to store JSON variant */ CREATE OR REPLACE TABLE xyz.abc.CAN_IB_SCHEDULING_test ( JSON_DATA VARIANT );

/*Copied the JSON data from snowflake stage to above created table */ COPY INTO CAN_IB_SCHEDULING_test FROM @xyz.abc.CAN_IB_SCHEDULING_STG FILE_FORMAT = (format_name = jsonformat);

select JSON_DATA from CAN_IB_SCHEDULING_test;

Example of the JSON variant

{"Appointment Date": "{"columnId":8275473196863364,"value":"2023-06-07","objectValue":{"objectType":"DATE","value":"2023-06-07"},"format":",,,,,,,,,12,,,,,,,"}", "Appointment ID": "{"columnId":8331000681549700,"value":"MSKW000001","objectValue":"MSKW000001","displayValue":"MSKW000001","format":",,,,,,,,,12,,,,,,,"}", "Appointment Insert Date": "{"columnId":3771873569492868,"value":"2023-06-02T18:08:56Z","objectValue":{"objectType":"DATETIME","value":"2023-06-02T18:08:56Z"},"format":",,,,,,,,,22,,,,,,,"}", "Appointment Remark": "{"columnId":2699982876397444,"value":"Carrier Requested","objectValue":"Carrier Requested","displayValue":"Carrier Requested","format":",,,,,,,,,22,,,,,,,"}"

When I select the value for a specific column it returns null.

SELECT JSON_DATA:"Appointment Date".value FROM JSON_table;

Expected ouput:

Appointment Date Appointment ID Appointment Remark 2023-06-07 DFSW000001 Carrier Requested

Upvotes: 0

Views: 421

Answers (1)

KARIM ELAISSAOUY
KARIM ELAISSAOUY

Reputation: 1

so for extract the values from the JSON variant in Snowflake , you can use Snowflake's JSON parsing functions like this : sql

    SELECT
      JSON_DATA:"Appointment Date":value::string AS "Appointment Date",
      JSON_DATA:"Appointment ID":value::string AS "Appointment ID",
      JSON_DATA:"Appointment Remark":value::string AS "Appointment Remark"
    FROM CAN_IB_SCHEDULING_test;

Upvotes: 0

Related Questions