Snowflake JSONデータのロードについて　その②

前回記事からの続きです。
前回はJSON生データ用テーブルにロードして終わりました。
今回はその生データから任意のキーからバリューを取得してみたいと思います。

JSONデータ操作

cityの値を取り出す

下記JSONデータの1行目を例にしてみます。
このデータの"city":"Bakersfield"の"Bakersfield"のみを取得してみます。

{"id":1,"first_name":"Portia","last_name":"Gioani","gender":"Male","city":"Bakersfield","job":{"title":"Financial Analyst","salary":32000},"spoken_languages":[{"language":"Kazakh","level":"Advanced"},{"language":"Lao","level":"Basic"}],"prev_company":[]},

SQLでは下記のように書くことで取り出せます。
SELECT {カラム名}:{キー名} FROM {テーブル名}

SELECT RAW_FILE:city FROM OUR_FIRST_DB.PUBLIC.JSON_RAW

表示結果が長いので最初の4行のみ表示しています。

RAW_FILE:CITY
Bakersfield""
Louny""
Wates""
Umeå""

カラム名以外で値を取り出す

$(列番号)を指定しても同じ結果を得られます。
今回の場1列しか無いので$1しか指定できませんが・・・
SELECT {$(number)}:{キー名} FROM {テーブル名}

SELECT $1:city FROM OUR_FIRST_DB.PUBLIC.JSON_RAW

ロード後のカラム名の変更と邪魔なダブルクォーテーションの除去

現状カラム名がSELECT句の指定文字列になっているので、それの見栄えをよくします。
あと都市名のダブルクォーテーションも邪魔なので消していきます。

RAW_FILE:CITY
Bakersfield""

カラムの変更方法
as {変更後のカラム名}を指定することでカラム名を変更できます。
ダブルクォーテーションの消し方
::stringを指定することで文字列型に変換されダブルクォーテーションが消えます。

SELECT RAW_FILE:first_name::string as first_name  FROM OUR_FIRST_DB.PUBLIC.JSON_RAW;

FIRST_NAME
Portia
Dag
Heath
Dita

いい感じにまとめる

SELECT 
    RAW_FILE:id::int as id,  
    RAW_FILE:first_name::STRING as first_name,
    RAW_FILE:last_name::STRING as last_name,
    RAW_FILE:gender::STRING as gender

FROM OUR_FIRST_DB.PUBLIC.JSON_RAW;

ID	FIRST_NAME	LAST_NAME	GENDER
1	Portia	Gioani	Male
2	Dag	Croney	Male
3	Heath	Lackmann	Female
4	Dita	Deering	Female
5	Nikki	McCosh	Female

ネストされたデータの取り出し方

このJSONデータにはネストされたデータが含まれています。
jobキーの中にtitleとsalaryという2つのキーがあり、それぞれにバリューもあります。
"job":{"title":"Financial Analyst","salary":32000}

{"id":1,"first_name":"Portia","last_name":"Gioani","gender":"Male","city":"Bakersfield","job":{"title":"Financial Analyst","salary":32000},"spoken_languages":[{"language":"Kazakh","level":"Advanced"},{"language":"Lao","level":"Basic"}],"prev_company":[]},

そのままSELECTしてもjobのキーだけしか取り出せません。

SELECT RAW_FILE:job as job  FROM OUR_FIRST_DB.PUBLIC.JSON_RAW;

親属性キー+ドット+子属性キーで取り出す

SELECT {カラム名}:{親属性キー}.{子属性キー}:{型指定} as {カラム名} FROM {テーブル名}
実際にsalaryを取り出してみます。

SELECT RAW_FILE:job.salary::INT as salary FROM OUR_FIRST_DB.PUBLIC.JSON_RAW;

SALARY
32000
43000
40800
14400

次回配列要素の取り出し方を見ていきたいと思います！