// First step: Load Raw JSON
CREATE OR REPLACE stage MANAGE_DB.EXTERNAL_STAGES.JSONSTAGE
url='s3://bucketsnowflake-jsondemo';
CREATE OR REPLACE file format MANAGE_DB.FILE_FORMATS.JSONFORMAT
TYPE = JSON;
CREATE OR REPLACE table OUR_FIRST_DB.PUBLIC.JSON_RAW (
raw_file variant);
COPY INTO OUR_FIRST_DB.PUBLIC.JSON_RAW
FROM @MANAGE_DB.EXTERNAL_STAGES.JSONSTAGE
file_format= MANAGE_DB.FILE_FORMATS.JSONFORMAT
files = ('HR_data.json');
SELECT * FROM OUR_FIRST_DB.PUBLIC.JSON_RAW;
------------------ PARSING -----------------
// Handling nested data
SELECT RAW_FILE:job as job FROM OUR_FIRST_DB.PUBLIC.JSON_RAW;
SELECT
RAW_FILE:[Link]::INT as salary
FROM OUR_FIRST_DB.PUBLIC.JSON_RAW;
SELECT
RAW_FILE:first_name::STRING as first_name,
RAW_FILE:[Link]::INT as salary,
RAW_FILE:[Link]::STRING as title
FROM OUR_FIRST_DB.PUBLIC.JSON_RAW;
// Handling arrays
SELECT
RAW_FILE:prev_company as prev_company
FROM OUR_FIRST_DB.PUBLIC.JSON_RAW;
SELECT
RAW_FILE:prev_company[1]::STRING as prev_company
FROM OUR_FIRST_DB.PUBLIC.JSON_RAW;
SELECT
ARRAY_SIZE(RAW_FILE:prev_company) as prev_company
FROM OUR_FIRST_DB.PUBLIC.JSON_RAW;
SELECT
RAW_FILE:id::int as id,
RAW_FILE:first_name::STRING as first_name,
RAW_FILE:prev_company[0]::STRING as prev_company
FROM OUR_FIRST_DB.PUBLIC.JSON_RAW
UNION ALL
SELECT
RAW_FILE:id::int as id,
RAW_FILE:first_name::STRING as first_name,
RAW_FILE:prev_company[1]::STRING as prev_company
FROM OUR_FIRST_DB.PUBLIC.JSON_RAW
ORDER BY id;
SELECT
RAW_FILE:spoken_languages as spoken_languages
FROM OUR_FIRST_DB.PUBLIC.JSON_RAW;
SELECT * FROM OUR_FIRST_DB.PUBLIC.JSON_RAW;
SELECT
array_size(RAW_FILE:spoken_languages) as spoken_languages
FROM OUR_FIRST_DB.PUBLIC.JSON_RAW;
SELECT
RAW_FILE:first_name::STRING as first_name,
array_size(RAW_FILE:spoken_languages) as spoken_languages
FROM OUR_FIRST_DB.PUBLIC.JSON_RAW;
SELECT
RAW_FILE:spoken_languages[0] as First_language
FROM OUR_FIRST_DB.PUBLIC.JSON_RAW;
SELECT
RAW_FILE:first_name::STRING as first_name,
RAW_FILE:spoken_languages[0] as First_language
FROM OUR_FIRST_DB.PUBLIC.JSON_RAW;
SELECT
RAW_FILE:first_name::STRING as First_name,
RAW_FILE:spoken_languages[0].language::STRING as First_language,
RAW_FILE:spoken_languages[0].level::STRING as Level_spoken
FROM OUR_FIRST_DB.PUBLIC.JSON_RAW;
SELECT
RAW_FILE:id::int as id,
RAW_FILE:first_name::STRING as First_name,
RAW_FILE:spoken_languages[0].language::STRING as First_language,
RAW_FILE:spoken_languages[0].level::STRING as Level_spoken
FROM OUR_FIRST_DB.PUBLIC.JSON_RAW
UNION ALL
SELECT
RAW_FILE:id::int as id,
RAW_FILE:first_name::STRING as First_name,
RAW_FILE:spoken_languages[1].language::STRING as First_language,
RAW_FILE:spoken_languages[1].level::STRING as Level_spoken
FROM OUR_FIRST_DB.PUBLIC.JSON_RAW
UNION ALL
SELECT
RAW_FILE:id::int as id,
RAW_FILE:first_name::STRING as First_name,
RAW_FILE:spoken_languages[2].language::STRING as First_language,
RAW_FILE:spoken_languages[2].level::STRING as Level_spoken
FROM OUR_FIRST_DB.PUBLIC.JSON_RAW
ORDER BY ID;
// Option 1: CREATE TABLE AS
CREATE OR REPLACE TABLE Languages AS
select
RAW_FILE:first_name::STRING as First_name,
[Link]:language::STRING as First_language,
[Link]:level::STRING as Level_spoken
from OUR_FIRST_DB.PUBLIC.JSON_RAW, table(flatten(RAW_FILE:spoken_languages)) f;
SELECT * FROM Languages;
truncate table languages;
// Option 2: INSERT INTO
INSERT INTO Languages
select
RAW_FILE:first_name::STRING as First_name,
[Link]:language::STRING as First_language,
[Link]:level::STRING as Level_spoken
from OUR_FIRST_DB.PUBLIC.JSON_RAW, table(flatten(RAW_FILE:spoken_languages)) f;
SELECT * FROM Languages;
select
RAW_FILE:first_name::STRING as First_name,
[Link]:language::STRING as First_language,
[Link]:level::STRING as Level_spoken
from OUR_FIRST_DB.PUBLIC.JSON_RAW, table(flatten(RAW_FILE:spoken_languages)) f;