snowflake parse_json - by shigemk2

このようなJSONデータがあったとして、

{
   "location": {
      "state_city": "MA-Lexington",
      "zip": "40503"
   },
   "sale_date": "2017-3-5",
   "price": "275836"
}

以下クエリでcopy intoする。

copy into home_sales(city, state, zip, sale_date, price)
   from (select substr(parse_json($1):location.state_city,4), substr(parse_json($1):location.state_city,1,2),
                parse_json($1):location.zip, to_timestamp_ntz(parse_json($1):sale_date), parse_json($1):price
         from @sf_tut_stage/sales.json.gz t)
   on_error = 'continue';

ちなみにキーにドルマークがあったら、

{
   "location": {
      "$state_city": "MA-Lexington",
      "zip": "40503"
   },
   "sale_date": "2017-3-5",
   "price": "275836"
}

ダブルクォートで囲めばいい。

copy into home_sales(city, state, zip, sale_date, price)
   from (select substr(parse_json($1):location."$state_city",4), substr(parse_json($1):location.state_city,1,2),
                parse_json($1):location.zip, to_timestamp_ntz(parse_json($1):sale_date), parse_json($1):price
         from @sf_tut_stage/sales.json.gz t)
   on_error = 'continue';

docs.snowflake.com