SnowflakeでのJSON内のテキストデータの扱い方で気づいたこと（AS_VARCHAR）

以前、「SnowflakeのFLATTEN、完全に理解した」とか言って以下の投稿をしました。

その際には件数だけを書いていて実データについては書いてはいないのですが、
JSONデータをselectすると、テキストデータはダブルクォーテーションで囲って出力されたんです。

再現してみます。

準備

--DB作成
create database TEST_DB;
--スキーマ作成
create schema TEST_SCHEMA;
--JSONテスト用テーブル作成
create table TEST_JSON(JSON_DATA VARIANT) COMMENT='JSONテスト用テーブル';
--テストデータを3件INSERT
INSERT INTO TEST_JSON SELECT TO_VARIANT(PARSE_JSON('{"id":"A1","object":"A","amount":100}'));
INSERT INTO TEST_JSON SELECT TO_VARIANT(PARSE_JSON('{"id":"B1","object":"B","amount":200}'));
INSERT INTO TEST_JSON SELECT TO_VARIANT(PARSE_JSON('{"id":"B2","object":"B","amount":10}'));
--INSERT結果確認
select * from TEST_JSON;

データ入ってますね。
ダブルクォーテーション、気になります。

JSONを分解して出力します。

--JSONを分解
select
    t1.JSON_DATA:amount as amount,
    t1.JSON_DATA:id as id,
    t1.JSON_DATA:object as object
from TEST_JSON t1;

ここで”A1”というようにテキストの値はダブルクォーテーションが付いてきます。
数値項目と解釈されているAmountの値はそのままです。

実際データとして使う時に邪魔だなーと思い、
replace(対象カラム,'"') as なんちゃら
というようにダブルクォーテーションを除去するようにしてました。

思い付き実装で対応

--replace関数をかます
select
    t1.JSON_DATA:amount as amount,
    replace(t1.JSON_DATA:id,'"') as id,
    replace(t1.JSON_DATA:object,'"') as object
from TEST_JSON t1;

求めていた結果としては正しい。

追記です。
ひょっとしてreplace関数だとダブルクォーテーションは除去できても実は型はVariantのままなのでは？と疑問に思い再度確認したところ、textってなってました。

黄色に塗ったところ、Aにカーソルを乗せると「text」となっていました。
replace関数をかます前の結果の"A1"の値の方は「variant」となっています。
replace関数をかますことで文字列型へのキャストが行われていることが確認できました。
間違ったことを書いてなくてよかったです。

でも、「これってなんかもっとスマートにやる方法があるはず。
こんな泥臭いことをわざわざやるなんて、そんなわけない。」
って思ってたんです。（思ってたけど放置。）

で、UdemyでSnowPro対策の講座見ていて発見しました。↓

AS_VARCHAR(対象カラム) as なんちゃら

うおー、顧客が求めていたもの。
これで行けました。

発見した技（これが正解）

select
    t1.JSON_DATA:amount as amount,
    AS_VARCHAR(t1.JSON_DATA:id) as id,
    AS_VARCHAR(t1.JSON_DATA:object) as object
from TEST_JSON t1;

おしまい。