構造化データ型¶
Snowflakeの構造化型は ARRAY、 OBJECT、 MAP です。構造化型には、特定の Snowflakeデータ型 を持つ要素またはキーと値のペアが含まれます。以下は構造化型の例です。
INTEGER 要素の ARRAY。
VARCHAR および NUMBER のキーと値のペアを含む OBJECT。
VARCHAR キーを DOUBLE 値に関連付ける MAP。
構造化型は次の方法で使用できます。
Apache Iceberg™テーブル に構造化型の列を定義できます。
Apache Iceberg™データ型
list
、struct
、map
は、Snowflakeの構造化 ARRAY、構造化 OBJECT、 MAP 型に対応します。構造化型列は最大1000個のサブ列をサポートします。
Icebergテーブルの構造化型列のデータにアクセスする場合は、構造化型を使用します。
半構造化の ARRAY、 OBJECT、または VARIANT 値は、対応する構造化型にキャストできます(例: ARRAY 値を INTEGER 要素の ARRAY 値にキャスト)。半構造化型の構造化型をキャストすることもできます。
注釈
現在、Icebergテーブル以外のテーブルは構造化型をサポートしていません。構造化型の列を通常のテーブルに追加することはできません。
このトピックでは、Snowflakeでの構造化型の使用方法について説明します。
構造化型の指定¶
構造化型の列を定義する場合、または値を構造化型にキャストする場合、次のセクションで説明する構文を使用します。
構造化 ARRAY 型の指定¶
構造化 ARRAY 型を指定するには、以下の構文を使用します。
ARRAY( <element_type> [ NOT NULL ] )
条件:
element_type
はこの ARRAY の要素の Snowflakeデータ型 です。要素の型として、構造化 ARRAY、構造化 OBJECT、または MAP を指定することもできます。
注釈
Icebergテーブル列の定義では、 ARRAY 要素の型として VARIANT、半構造化 ARRAY、または半構造化 OBJECT を指定することはできません。
NOT NULL は ARRAY が NULL の要素を含むことができないことを指定します。
例えば、次のステートメントで SYSTEM$TYPEOF 関数が返す型を比較します。
最初の列式は、半構造化 ARRAY 値を構造化 ARRAY 値(NUMBER 要素の ARRAY)にキャストします。
2番目の列式は、半構造化 ARRAY 値を指定します。
SELECT
SYSTEM$TYPEOF(
[1, 2, 3]::ARRAY(NUMBER)
) AS structured_array,
SYSTEM$TYPEOF(
[1, 2, 3]
) AS semi_structured_array;
+-------------------------------+-----------------------+
| STRUCTURED_ARRAY | SEMI_STRUCTURED_ARRAY |
|-------------------------------+-----------------------|
| ARRAY(NUMBER(38,0))[LOB] | ARRAY[LOB] |
+-------------------------------+-----------------------+
構造化 OBJECT 型の指定¶
構造化 OBJECT 型を指定するには、以下の構文を使用します。
OBJECT(
[
<key> <value_type> [ NOT NULL ]
[ , <key> <value_type> [ NOT NULL ] ]
[ , ... ]
]
)
条件:
key
は OBJECT 型のキーを指定します。OBJECT 定義の各
key
は一意にする必要があります。キーの順序は OBJECT 定義の一部です。同じキーを持つ2つの OBJECT 値を異なる順序で比較することは許可されていません。(コンパイル時にエラーが発生します)。
キーを指定せずに括弧を指定した場合(つまり
OBJECT()
を使用した場合)、結果として得られる型は、キーを含まない構造化 OBJECT になります。キーを持たない構造化 OBJECT は、半構造化 OBJECT とは異なります。
value_type
は、キーに対応する値の Snowflakeデータ型 です。値の型として、構造化 ARRAY、構造化 OBJECT、または MAP を指定することもできます。
注釈
Icebergテーブルの列の定義では、 OBJECT キーに対応する値の型として VARIANT、半構造化 ARRAY、または半構造化 OBJECT を指定することはできません。
NOT NULL は、キーに対応する値は NULL にできないことを指定します。
例えば、次のステートメントで SYSTEM$TYPEOF 関数が返す型を比較します。
最初の列式は、半構造化 OBJECT 値を、以下のキーと値を含む構造化 OBJECT 値にキャストします。
NULL ではない VARCHAR 値を持つ
str
という名前のキー。NUMBER 値を持つ
num
という名前のキー。
2番目の列式は、半構造化 OBJECT 値を指定します。
SELECT
SYSTEM$TYPEOF(
{
'str': 'test',
'num': 1
}::OBJECT(
str VARCHAR NOT NULL,
num NUMBER
)
) AS structured_object,
SYSTEM$TYPEOF(
{
'str': 'test',
'num': 1
}
) AS semi_structured_object;
+---------------------------------------------------------------+------------------------+
| STRUCTURED_OBJECT | SEMI_STRUCTURED_OBJECT |
|---------------------------------------------------------------+------------------------|
| OBJECT(str VARCHAR(16777216) NOT NULL, num NUMBER(38,0))[LOB] | OBJECT[LOB] |
+---------------------------------------------------------------+------------------------+
MAP 型の指定¶
MAP 型を指定するには、次の構文を使用します。
MAP( <key_type> , <value_type> [ NOT NULL ] )
条件:
key_type
は、マップの Snowflake データ型 のキーです。次のいずれかの型をキーに使用する必要があります。VARCHAR
スケール0の NUMBER
浮動小数点データ型をキーの型として使うことはできません。
マッピングキーは NULL にはできません。
value_type
は、マップ内の値の Snowflakeデータ型 です。値の型として、構造化 ARRAY、構造化 OBJECT、または MAP を指定することもできます。
注釈
Icebergテーブル列の定義では、 MAP の値の型として VARIANT、半構造化 ARRAY、または半構造化 OBJECT を指定することはできません。
NOT NULL は、キーに対応する値は NULL にできないことを指定します。
次の例では、半構造化 OBJECT 値を MAP 値にキャストし、 SYSTEM$TYPEOF 関数を使用してその結果の値の型を表示します。MAP は VARCHAR キーと VARCHAR 値を関連付けます。
SELECT
SYSTEM$TYPEOF(
{
'a_key': 'a_val',
'b_key': 'b_val'
}::MAP(VARCHAR, VARCHAR)
) AS map_example;
+------------------------------------------------+
| MAP_EXAMPLE |
|------------------------------------------------|
| MAP(VARCHAR(16777216), VARCHAR(16777216))[LOB] |
+------------------------------------------------+
半構造化型での構造化型の使用¶
VARIANT、半構造化 OBJECT、または半構造化 ARRAY 値内で、 MAP、構造化 OBJECT、構造化 ARRAY 値は使用できません。次のような場合にエラーが発生します。
OBJECT 定数 または ARRAY 定数 内では MAP、構造化 OBJECT、または構造化 ARRAY 値を使用できます。
OBJECT または ARRAY コンストラクター関数 に MAP、構造化 OBJECT、または構造化 ARRAY 値を渡します。
構造化型と半構造化型の変換¶
次のテーブルは、構造化 OBJECT、構造化 ARRAY、および MAP 値を半構造化 OBJECT、 ARRAY、および VARIANT 値(またはその逆)に 変換する ルールをまとめたものです。
ソースデータ型 |
ターゲットデータ型 |
||
---|---|---|---|
半構造化 ARRAY |
構造化 ARRAY |
✔ |
❌ |
半構造化 OBJECT |
|
✔ |
❌ |
半構造化 VARIANT |
|
✔ |
❌ |
構造化 ARRAY |
半構造化 ARRAY |
✔ |
❌ |
|
半構造化 OBJECT |
✔ |
❌ |
|
半構造化 VARIANT |
✔ |
❌ |
次のセクションでは、これらのルールについて詳しく説明します。
半構造化型を構造化型に明示的にキャストする¶
半構造化型の値を構造化型の値に明示的にキャストするには、 CAST 関数を呼び出すか、:: 演算子 を使用します。
注釈
TRY_CAST は構造化型ではサポートされていません。
以下の半構造化型の値のみを、対応する構造化型の値にキャストします。そうしないと、ランタイムのエラーが発生します。
半構造化型 |
キャストできる構造化型 |
---|---|
ARRAY |
構造化 ARRAY |
OBJECT |
MAP または構造化 OBJECT |
VARIANT |
MAP、構造化 ARRAY または OBJECT |
次のセクションでは、どのように型がキャスティングされるかについて詳しく説明します。
半構造化 ARRAY および VARIANT 値の構造化 ARRAY 値へのキャスト¶
次のステップは、半構造化 ARRAY または VARIANT 値を、 NUMBER 要素の ARRAY 値にキャストする方法を示しています。
SELECT
SYSTEM$TYPEOF(
CAST ([1,2,3] AS ARRAY(NUMBER))
) AS array_cast_type,
SYSTEM$TYPEOF(
CAST ([1,2,3]::VARIANT AS ARRAY(NUMBER))
) AS variant_cast_type;
または、
SELECT
SYSTEM$TYPEOF(
[1,2,3]::ARRAY(NUMBER)
) AS array_cast_type,
SYSTEM$TYPEOF(
[1,2,3]::VARIANT::ARRAY(NUMBER)
) AS variant_cast_type;
+--------------------------+--------------------------+
| ARRAY_CAST_TYPE | VARIANT_CAST_TYPE |
|--------------------------+--------------------------|
| ARRAY(NUMBER(38,0))[LOB] | ARRAY(NUMBER(38,0))[LOB] |
+--------------------------+--------------------------+
半構造化 ARRAY または VARIANT 値を構造化 ARRAY 値にキャストする場合は、以下の点に注意してください。
ARRAY 値の各要素は、 ARRAY の指定された型にキャストされます。
ARRAY 列を ARRAY (VARCHAR)にキャストすると、各値は VARCHAR 値に変換されます。
SELECT CAST ([1,2,3] AS ARRAY(VARCHAR)) AS varchar_array, SYSTEM$TYPEOF(varchar_array) AS array_cast_type;
+---------------+-------------------------------+ | VARCHAR_ARRAY | ARRAY_CAST_TYPE | |---------------+-------------------------------| | [ | ARRAY(VARCHAR(16777216))[LOB] | | "1", | | | "2", | | | "3" | | | ] | | +---------------+-------------------------------+
要素を指定された型にキャストできない(例:
['a', 'b', 'c']
を ARRAY(NUMBER)にキャストする)場合、キャストは失敗します。ARRAY 値が NULL 要素を含んでいて、 ARRAY 型が NOT NULL を指定している(例:
[1, NULL, 3]
を ARRAY(NUMBER NOT NULL)にキャストする)場合、キャストは失敗します。JSON null値 である要素は、ターゲット要素型が JSON nullをサポートしていない場合(つまり、ターゲット型が半構造化 ARRAY、 OBJECT、 VARIANT ではない場合)、 NULL に変換されます。
たとえば、 ARRAY(NUMBER)にキャストする場合、 NUMBER は JSON nullをサポートしていないため、 JSON null値は NULL に変換されます。
一方、 ARRAY(VARIANT)にキャストする場合、 VARIANT は JSON nullをサポートしているため、 JSON null値は NULL に変換されません。
半構造化 OBJECT および VARIANT 値の構造化 OBJECT 値へのキャスト¶
次のステップは、半構造化 OBJECT または VARIANT 値を、 city
と state
のキーと値のペア(これは VARCHAR 値)を含む構造化 OBJECT 値にキャストする方法を示しています。
SELECT
SYSTEM$TYPEOF(
CAST ({'city':'San Mateo','state':'CA'} AS OBJECT(city VARCHAR, state VARCHAR))
) AS object_cast_type,
SYSTEM$TYPEOF(
CAST ({'city':'San Mateo','state':'CA'}::VARIANT AS OBJECT(city VARCHAR, state VARCHAR))
) AS variant_cast_type;
または、
SELECT
SYSTEM$TYPEOF(
{'city':'San Mateo','state':'CA'}::OBJECT(city VARCHAR, state VARCHAR)
) AS object_cast_type,
SYSTEM$TYPEOF(
{'city':'San Mateo','state':'CA'}::VARIANT::OBJECT(city VARCHAR, state VARCHAR)
) AS variant_cast_type;
+--------------------------------------------------------------+--------------------------------------------------------------+
| OBJECT_CAST_TYPE | VARIANT_CAST_TYPE |
|--------------------------------------------------------------+--------------------------------------------------------------|
| OBJECT(city VARCHAR(16777216), state VARCHAR(16777216))[LOB] | OBJECT(city VARCHAR(16777216), state VARCHAR(16777216))[LOB] |
+--------------------------------------------------------------+--------------------------------------------------------------+
半構造化 OBJECT または VARIANT 値を構造化 OBJECT 値にキャストする場合は、以下の点に注意してください。
OBJECT 値には、 OBJECT 型で指定されていない追加のキーを含むことはできません。
追加のキーがある場合、キャストは失敗します。
OBJECT 値に OBJECT 型で指定されているキーがない場合、キャストは失敗します。
OBJECT 値の各キーの値は、そのキーに対して指定された型に変換されます。
指定した型に値をキャストできない場合、キャストは失敗します。
キーの値が JSON null値 の場合、ターゲット値の型が JSON nullをサポートしていない(つまり、ターゲット型が半構造化 ARRAY、 OBJECT、 VARIANT ではない)場合、値は NULL に変換されます。
たとえば、 OBJECT(city VARCHAR)にキャストする場合、 VARCHAR は JSON nullをサポートしていないため、 JSON null値は NULL に変換されます。
一方、 OBJECT(city VARIANT)にキャストする場合、 VARIANT は JSON nullをサポートしているため、 JSON null値は NULL に変換されません。
半構造化 OBJECT および VARIANT 値の MAP 値へのキャスト¶
次のステートメントは、半構造化 OBJECT または VARIANT 値を VARCHAR キーと VARCHAR 値を関連付ける MAP 値にキャストする方法を示しています。
SELECT
SYSTEM$TYPEOF(
CAST ({'my_key':'my_value'} AS MAP(VARCHAR, VARCHAR))
) AS map_cast_type,
SYSTEM$TYPEOF(
CAST ({'my_key':'my_value'} AS MAP(VARCHAR, VARCHAR))
) AS variant_cast_type;
または、
SELECT
SYSTEM$TYPEOF(
{'my_key':'my_value'}::MAP(VARCHAR, VARCHAR)
) AS map_cast_type,
SYSTEM$TYPEOF(
{'my_key':'my_value'}::VARIANT::MAP(VARCHAR, VARCHAR)
) AS variant_cast_type;
+------------------------------------------------+------------------------------------------------+
| MAP_CAST_TYPE | VARIANT_CAST_TYPE |
|------------------------------------------------+------------------------------------------------|
| MAP(VARCHAR(16777216), VARCHAR(16777216))[LOB] | MAP(VARCHAR(16777216), VARCHAR(16777216))[LOB] |
+------------------------------------------------+------------------------------------------------+
半構造化 OBJECT または VARIANT 値を MAP 値にキャストする場合は、以下の点に注意してください。
キーと値が指定された型と一致しない場合、キーと値は指定された型に変換されます。
キーと値が指定された型にキャストできない場合、キャストは失敗します。
キーの値が JSON null値 の場合、ターゲット値の型が JSON nullをサポートしていない(つまり、ターゲット型が半構造化 ARRAY、 OBJECT、 VARIANT ではない)場合、値は NULL に変換されます。
たとえば、 MAP(VARCHAR, VARCHAR)にキャストする場合、 VARCHAR は JSON nullをサポートしていないため、 JSON null値は NULL に変換されます。
一方、 MAP(VARCHAR, VARIANT)にキャストする場合、 VARIANT は JSON nullをサポートしているため、 JSON null値は NULL に変換されません。
構造化型を半構造化型に明示的にキャストする¶
構造化型の値を半構造化型の値に明示的にキャストするには、 CAST 関数を呼び出すか、:: 演算子 を使用するか、変換関数の1つを呼び出します(例: TO_ARRAY、 TO_OBJECT、 TO_VARIANT)。
注釈
TRY_CAST は構造化型ではサポートされていません。
構造化型 |
キャストできる半構造化型 |
---|---|
構造化 ARRAY |
ARRAY |
MAP または構造化 OBJECT |
OBJECT |
MAP、構造化 ARRAY、または構造化 OBJECT |
VARIANT |
例:
col_structured_array
が ARRAY(VARCHAR)型の場合、CAST(col_structured_array AS ARRAY)は半構造化 ARRAY 値を返します。
CAST(col_structured_array AS VARIANT)は、半構造化 ARRAY 値を保持する VARIANT 値を返します。
col_structured_object
が OBJECT(name VARCHAR, state VARCHAR)型の場合、CAST(col_structured_object AS OBJECT)は、半構造化 OBJECT 値を返します。
CAST(col_structured_object AS VARIANT)は、半構造化 OBJECT 値を保持する VARIANT 値を返します。
col_map
が MAP(VARCHAR, VARCHAR)型の場合、CAST(col_map AS OBJECT)は、半構造化 OBJECT 値を返します。
CAST(col_map AS VARIANT)は、半構造化 OBJECT 値を保持する VARIANT 値を返します。
次の点に注意してください。
半構造化 OBJECT 値にキャストする場合、構造化 OBJECT 値のキーの順序は保持されません。
構造化 OBJECT または MAP 値を半構造化 OBJECT または VARIANT 値にキャストする場合、 NULL 値はすべて JSON null値 に変換されます。
構造化された ARRAY 値を VARIANT 値にキャストする場合、 NULL 値はそのまま保持されます。
SELECT [1,2,NULL,3]::ARRAY(INTEGER)::VARIANT;
+---------------------------------------+ | [1,2,NULL,3]::ARRAY(INTEGER)::VARIANT | |---------------------------------------| | [ | | 1, | | 2, | | undefined, | | 3 | | ] | +---------------------------------------+
キーに NUMBER 型を使用する MAP 値をキャストする場合、 MAP キーは返される OBJECT 値内で文字列に変換されます。
値の暗黙のキャスト(強制)¶
ある構造化型の値から別の構造化型の値に 暗黙的にキャスト(強制) するには、以下のルールが適用されます。
構造化型の値は、2つの基本型が同じであれば、別の構造化型の値に強制することができます。
1つの型の ARRAY 値は、最初の要素型が2番目の要素型に強制可能であれば、別の型の ARRAY 値に強制することができます。
要素型は次のいずれかの場合で別の要素型に強制できます。
どちらの型も数値である。次のケースがサポートされています。
どちらも同じ数値型を使用しているけれども、精度やスケールが異なる可能性がある。
NUMBER を FLOAT に強制する(逆も同様)。
どちらの型もタイムスタンプである。次のケースがサポートされています。
どちらも同じ型を使用しているけれども、精度が異なる可能性がある。
TIMESTAMP_LTZ を TIMESTAMP_TZ に強制する(逆も同様)。
例:
ARRAY(NUMBER)値を ARRAY(DOUBLE)値に強制できる。
ARRAY(DATE)値を ARRAY(NUMBER)値に強制できない。
ある型定義の OBJECT 値を、別の型定義の OBJECT 値に強制することができるのは、次のすべてが真である場合に限られます。
どちらの OBJECT 型も同じ数のキーを持っている。
どちらの OBJECT 型もキーに同じ名前を使用している。
両方の OBJECT 型のキーが同じ順番である。
一方の OBJECT 型の各値の型は、もう一方の OBJECT 型の対応する値の型に強制することができる。
構造化 ARRAY 値の要素型の場合と同様に、ある値の型を別の型に強制できるのは次の場合のみです。
どちらの型も数値である。次のケースがサポートされています。
どちらも同じ数値型を使用しているけれども、精度やスケールが異なる可能性がある。
NUMBER を FLOAT に強制する(逆も同様)。
どちらの型もタイムスタンプである。次のケースがサポートされています。
どちらも同じ型を使用しているけれども、精度が異なる可能性がある。
TIMESTAMP_LTZ を TIMESTAMP_TZ に強制する(逆も同様)。
例:
OBJECT(city VARCHAR, zipcode NUMBER)値を OBJECT(city VARCHAR, zipcode DOUBLE)値に強制できる。
OBJECT(city VARCHAR, zipcode NUMBER)値を OBJECT(city VARCHAR, zipcode DATE)値に強制できない。
1つの値型を持つ MAP 値は、次の場合に異なる値型を持つ MAP 値に強制することができます。
どちらも数値型である。次のケースがサポートされています。
どちらも同じ数値型を使用しているけれども、精度やスケールが異なる可能性がある。
NUMBER を FLOAT に強制する(逆も同様)。
どちらの値もタイムスタンプである。次のケースがサポートされています。
どちらも同じ型を使用しているけれども、精度が異なる可能性がある。
TIMESTAMP_LTZ を TIMESTAMP_TZ に強制する(逆も同様)。
たとえば、 MAP(VARCHAR, NUMBER)値を MAP(VARCHAR, DOUBLE)値に強制できます。
1つのキー型を持つ MAP 値は、両方のキー型が精度のみが異なる同じ整数 NUMERIC 型を使用する場合に異なるキー型を持つ MAP 値に強制することができます。
たとえば、 MAP(VARCHAR, NUMBER)値を MAP(NUMBER, NUMBER)値に強制することはできません。
構造化型の値を半構造化型の値に強制することはできません(その逆も同様)。
VARCHAR 値を構造化型の値に強制することはできません。
ある構造化型から別の構造型へのキャスト¶
CAST 関数を呼び出すか、 :: 演算子を使用 して、ある構造化型の値から別の構造化型の値にキャストすることができます。以下の構造化型から、またはその型に値をキャストできます。
構造化 ARRAYs の場合:
構造化 OBJECTs の場合:
キャストを使用して、 OBJECT 値内の キーと値のペアの順序を変更 できます。
キャストを使用して、 OBJECT 値内の キーの名前を変更 できます。
キャストを使用して、 OBJECT 値に キーを追加 できます。
構造化された OBJECT 値を MAP 値にキャストできます。
MAP 値の場合、
特定の型のキーと値を持つ MAP 値を、異なる型のキーと値を持つ MAP 値にキャストできます。
MAP 値を構造化された OBJECT 値にキャストできます。
注釈
TRY_CAST は構造化型ではサポートされていません。
一方の型から他方の型に値をキャストできない場合、キャストは失敗します。たとえば、 ARRAY(BOOLEAN)値を ARRAY(DATE)値にキャストしようとすると失敗します。
例: ある型の ARRAY 値から別の型へのキャスト¶
次の例は ARRAY(NUMBER)値を ARRAY(VARCHAR)値にキャストします。
SELECT CAST(
CAST([1,2,3] AS ARRAY(NUMBER))
AS ARRAY(VARCHAR)) AS cast_array;
+------------+
| CAST_ARRAY |
|------------|
| [ |
| "1", |
| "2", |
| "3" |
| ] |
+------------+
例: OBJECT 値内にあるキーと値のペアの順序変更¶
次の例は、構造化 OBJECT 値内のキーと値のペアの順序を変更します。
SELECT CAST(
{'city': 'San Mateo','state': 'CA'}::OBJECT(city VARCHAR, state VARCHAR)
AS OBJECT(state VARCHAR, city VARCHAR)) AS object_value_order;
+-----------------------+
| OBJECT_VALUE_ORDER |
|-----------------------|
| { |
| "state": "CA", |
| "city": "San Mateo" |
| } |
+-----------------------+
例: OBJECT 値内のキー名の変更¶
構造化 OBJECT 値のキー名を変更するには、 CAST の最後に RENAME FIELDS キーワードを指定します。例:
SELECT CAST({'city':'San Mateo','state': 'CA'}::OBJECT(city VARCHAR, state VARCHAR)
AS OBJECT(city_name VARCHAR, state_name VARCHAR) RENAME FIELDS) AS object_value_key_names;
+-----------------------------+
| OBJECT_VALUE_KEY_NAMES |
|-----------------------------|
| { |
| "city_name": "San Mateo", |
| "state_name": "CA" |
| } |
+-----------------------------+
例: OBJECT 値へのキーの追加¶
キャスト先の型に、オリジナルの構造化 OBJECT 値には存在しない追加のキーと値のペアがある場合は、 ADD FIELDS キーワードを CAST の末尾に指定します。例:
SELECT CAST({'city':'San Mateo','state': 'CA'}::OBJECT(city VARCHAR, state VARCHAR)
AS OBJECT(city VARCHAR, state VARCHAR, zipcode NUMBER) ADD FIELDS) AS add_fields;
+------------------------+
| ADD_FIELDS |
|------------------------|
| { |
| "city": "San Mateo", |
| "state": "CA", |
| "zipcode": null |
| } |
+------------------------+
新しく追加されたキーの値は NULL に設定されます。これらのキーに値を割り当てる場合は、 OBJECT_INSERT 関数を代わりに呼び出します。
構造化 ARRAY 、構造化 OBJECT 、および MAP 値の構築¶
以下のセクションでは、構造化 ARRAY、構造化 OBJECT、および MAP 値の構築方法について説明します。
SQL 関数を使用した構造化 ARRAY と OBJECT 値の構築¶
以下の関数は、半構造化 ARRAY 値を構築します。
以下の関数は、半構造化 OBJECT 値を構築します。
構造化された ARRAY または OBJECT 値を構築するには、これらの関数を使用し、関数の戻り値を明示的にキャストします。例:
SELECT ARRAY_CONSTRUCT(10, 20, 30)::ARRAY(NUMBER);
SELECT OBJECT_CONSTRUCT(
'oname', 'abc',
'created_date', '2020-01-18'::DATE
)::OBJECT(
oname VARCHAR,
created_date DATE
);
詳細については、 半構造化型を構造化型に明示的にキャストする をご参照ください。
注釈
これらの関数に構造化 ARRAY、構造化 OBJECT、構造化 MAP 値を渡すことはできません。そうすると、構造化型が暗黙のうちに半構造化型にキャストされることになり、 値の暗黙のキャスト(強制) にあるように、これは許可されません。
ARRAY と OBJECT 定数を使用した構造化 ARRAY と OBJECT 値の構築¶
ARRAY 定数 または OBJECT 定数 を指定する場合は、半構造化 ARRAY または OBJECT 値を指定していることになります。
構造化 ARRAY または OBJECT 値を構築するには、式を明示的にキャストする必要があります。例:
SELECT [10, 20, 30]::ARRAY(NUMBER);
SELECT {
'oname': 'abc',
'created_date': '2020-01-18'::DATE
}::OBJECT(
oname VARCHAR,
created_date DATE
);
詳細については、 半構造化型を構造化型に明示的にキャストする をご参照ください。
MAP 値の構築¶
MAP 値を構築するには、半構造化 OBJECT 値を構築し、 OBJECT 値を MAP 値にキャストします。
たとえば、以下のステートメントはどちらも MAP 値 {'city'->'San Mateo','state'->'CA'}
を生成します。
SELECT OBJECT_CONSTRUCT(
'city', 'San Mateo',
'state', 'CA'
)::MAP(
VARCHAR,
VARCHAR
);
SELECT {
'city': 'San Mateo',
'state': 'CA'
}::MAP(
VARCHAR,
VARCHAR
);
次のステートメントは、 MAP 値 {-10->'CA',-20->'OR'}
を生成します。
SELECT {
'-10': 'CA',
'-20': 'OR'
}::MAP(
NUMBER,
VARCHAR
);
詳細については、 半構造化 OBJECT および VARIANT 値の MAP 値へのキャスト をご参照ください。
構造化型の値のキー、値、および要素の操作¶
以下のセクションでは、構造化タイプの値におけるキー、値、および要素の使用方法について説明します。
構造化 OBJECT 値からのキーのリスト取得¶
構造化 OBJECT 値のキーのリストを取得するには、 OBJECT_KEYS 関数を呼び出します。
SELECT OBJECT_KEYS({'city':'San Mateo','state':'CA'}::OBJECT(city VARCHAR, state VARCHAR));
入力が構造化 OBJECT 値の場合、この関数はキーを含む ARRAY(VARCHAR)値を返します。入力が半構造化 OBJECT 値の場合、関数は ARRAY 値を返します。
MAP 値からのキーのリスト取得¶
MAP 値のキーのリストを取得するには、 MAP_KEYS 関数を呼び出します。
SELECT MAP_KEYS({'my_key':'my_value'}::MAP(VARCHAR,VARCHAR));
構造化型の値からの値や要素へのアクセス¶
次のメソッドを使用して、構造化 ARRAY、構造化 OBJECT、 MAP 値から値や要素にアクセスできます。
戻り値や要素は、 VARIANT ではなく、構造化値に指定された型を持ちます。
次の例では、半構造化 ARRAY 値の最初の要素と ARRAY(VARCHAR)値を SYSTEM$TYPEOF 関数に渡して、その要素のデータ型を返しています。
SELECT
SYSTEM$TYPEOF(
ARRAY_CONSTRUCT('San Mateo')[0]
) AS semi_structured_array_element,
SYSTEM$TYPEOF(
CAST(
ARRAY_CONSTRUCT('San Mateo') AS ARRAY(VARCHAR)
)[0]
) AS structured_array_element;
+-------------------------------+-----------------------------+
| SEMI_STRUCTURED_ARRAY_ELEMENT | STRUCTURED_ARRAY_ELEMENT |
|-------------------------------+-----------------------------|
| VARIANT[LOB] | VARCHAR(16777216)[LOB] |
+-------------------------------+-----------------------------+
次の点に注意してください。
構造化 OBJECT 値を GET または GET_IGNORE_CASE 関数に渡す場合は、キーに定数を指定する必要があります。
GET 関数に MAP または構造化 ARRAY 値を渡す場合は、定数を指定する必要はありません。
また、 GET_IGNORE_CASE 関数に MAP 値を渡す場合は、定数を指定する必要はありません。
構造化 OBJECT、構造化 ARRAY、または MAP 値を GET_PATH 関数に渡す場合は、パス名に定数を指定する必要があります。
構造化 OBJECT 値の場合、 OBJECT キーまたは存在しないパスを使用すると、コンパイル時にエラーが発生します。
対照的に、存在しないインデックス、キー、パスを半構造化 OBJECT 値で使用すると、関数は NULL を返します。
構造化 ARRAY 値サイズの決定¶
構造化 ARRAY 値のサイズを決定するには、 ARRAY 値を ARRAY_SIZE 関数に渡します。
SELECT ARRAY_SIZE([1,2,3]::ARRAY(NUMBER));
MAP 値のサイズの決定¶
MAP 値のサイズを決定するには、 MAP 値を MAP_SIZE 関数に渡します。
SELECT MAP_SIZE({'my_key':'my_value'}::MAP(VARCHAR,VARCHAR));
構造化 ARRAY 値内の要素の検索¶
ある要素が構造化 ARRAY 値に存在するかどうかを判断するには、 ARRAY_CONTAINS 関数を呼び出します。例:
SELECT ARRAY_CONTAINS(10, [1, 10, 100]::ARRAY(NUMBER));
構造化 ARRAY 値内の要素の位置を決定するには、 ARRAY_POSITION 関数を呼び出します。例:
SELECT ARRAY_POSITION(10, [1, 10, 100]::ARRAY(NUMBER));
MAP 値にキーが含まれているかどうかの判定¶
MAP 値にキーが含まれているかどうかを調べるには、 MAP_CONTAINS_KEY 関数を呼び出します。
例:
SELECT MAP_CONTAINS_KEY('key_to_find', my_map);
SELECT MAP_CONTAINS_KEY(10, my_map);
値の比較¶
以下のセクションでは、値の比較方法について説明します。
構造化値と半構造化値の比較¶
構造化 ARRAY、構造化 OBJECT、または MAP 値を、半構造化 ARRAY、 OBJECT、または VARIANT 値と比較することはできません。
構造化値と他の構造化値の比較¶
同じ型の2つの値を比較することができます(例: 2つの構造化 ARRAY 値、2つの構造化 OBJECT 値、または2つの MAP 値)。
現在、構造化値の値を比較するために、以下の比較演算子がサポートされています。
=
!=
<
<=
>=
>
2つの構造化値が等しいかどうか比較する場合は、以下の点に注意してください。
一方の型をもう一方の型に 強制 できない場合、比較は失敗します。
数値キーを持つ MAP 値を比較する場合、キーは(VARCHAR 値としてではなく)数値として比較されます。
<
、 <=
、 >=
、 >
を使用して2つの構造化値を比較する場合、構造化値フィールドはアルファベット順に比較されます。たとえば、以下の値は、
{'a':2,'b':1}::OBJECT(b INTEGER,a INTEGER)
次よりも大きいです:
{'a':1,'b':2}::OBJECT(b INTEGER,a INTEGER)
2つの ARRAY 値が重なるかどうかの判定¶
2つの構造化 ARRAY 値の要素が重なっているかどうかを判断するには、 ARRAYS_OVERLAP 関数を呼び出します。例:
SELECT ARRAYS_OVERLAP(numeric_array, other_numeric_array);
ARRAY 値は、 比較可能な型 にする必要があります。
この関数に半構造化 ARRAY 値と構造化 ARRAY 値を渡すことはできません。ARRAY 値の両方とも構造化にするか、半構造化にする必要があります。
構造化型の値の変換¶
以下のセクションでは、構造化 ARRAY、構造化 OBJECT、および MAP 値の変換方法について説明します。
構造化 ARRAY 値の変換¶
これらの関数に構造化 ARRAY 値を渡すと、関数は同じ型の構造化 ARRAY 値を返します。
次のセクションでは、これらの関数が構造化 ARRAY 値でどのように動作するかを説明します。
ARRAY 値に要素を追加する関数¶
以下の関数は、 ARRAY 値に要素を追加します。
これらの関数の場合、要素の型は ARRAY 値の型に 強制可能 である必要があります。
たとえば、 NUMBER 値は、 DOUBLE 値(ARRAY 値の型)に強制できるため、次の呼び出しは成功します。
SELECT ARRAY_APPEND( [1,2]::ARRAY(DOUBLE), 3::NUMBER );
VARCHAR 値は DOUBLE 値に強制できるため、次の呼び出しは成功します。
SELECT ARRAY_APPEND( [1,2]::ARRAY(DOUBLE), '3' );
DATE 値は NUMBER 値に強制できないため、次の呼び出しは失敗します。
SELECT ARRAY_APPEND( [1,2]::ARRAY(NUMBER), '2022-02-02'::DATE );
入力として複数の ARRAY 値を受け付ける関数¶
次の関数は入力引数として複数の ARRAY 値を受け付けます。
これらの関数を呼び出す場合は、両方の引数が構造化 ARRAY 値または半構造化 ARRAY 値のどちらか一方にする必要があります。たとえば、次の呼び出しは、一方の引数が構造化 ARRAY 値で、もう一方の引数が半構造化 ARRAY 値であるため、失敗します。
SELECT ARRAY_CAT( [1,2]::ARRAY(NUMBER), ['3','4'] );
SELECT ARRAY_CAT( [1,2], ['3','4']::ARRAY(VARCHAR) );
ARRAY_EXCEPT 関数は、第1引数の ARRAY 値と同じ型の ARRAY 値を返します。
ARRAY_CAT および ARRAY_INTERSECTION 関数は、両方の入力値に対応できる型の ARRAY 値を返します。
たとえば、次の ARRAY_CAT の呼び出しは、2つの構造化 ARRAY 値を渡します。
最初の構造化 ARRAY 値は NULLs を許容せず、0のスケールを持つ NUMBER 値を含みます(NUMBER(38, 0))。
2つ目の構造化 ARRAY 値は、 NULL と NUMBER 値を含み、そのスケールは1です。
ARRAY_CAT によって返される ARRAY 値は、 NULLs を許容し、 NUMBER 値を1のスケールで含みます。
SELECT
ARRAY_CAT(
[1, 2, 3]::ARRAY(NUMBER NOT NULL),
[5.5, NULL]::ARRAY(NUMBER(2, 1))
) AS concatenated_array,
SYSTEM$TYPEOF(concatenated_array);
+--------------------+-----------------------------------+
| CONCATENATED_ARRAY | SYSTEM$TYPEOF(CONCATENATED_ARRAY) |
|--------------------+-----------------------------------|
| [ | ARRAY(NUMBER(38,1))[LOB] |
| 1, | |
| 2, | |
| 3, | |
| 5.5, | |
| undefined | |
| ] | |
+--------------------+-----------------------------------+
ARRAY_CAT 関数の場合、第2引数の ARRAY 値は第1引数の型に 強制可能 である必要があります。
ARRAY_EXCEPT および ARRAY_INTERSECTION 関数の場合、第2引数の ARRAY 値は、第1引数の ARRAY 値に対して 比較可能 でなければなりません。
たとえば、 ARRAY(NUMBER)の値は、 ARRAY(DOUBLE)の値と比較できるため、次の呼び出しは成功します。
SELECT ARRAY_EXCEPT( [1,2]::ARRAY(NUMBER), [2,3]::ARRAY(DOUBLE) );
たとえば、 ARRAY(NUMBER)の値は、 ARRAY(VARCHAR)の値と比較できないため、次の呼び出しは失敗します。
SELECT ARRAY_EXCEPT( [1,2]::ARRAY(NUMBER), ['2','3']::ARRAY(VARCHAR) );
構造化 OBJECT 値の変換¶
以下のセクションでは、別の OBJECT 値から変換された構造化 OBJECT 値を返す方法について説明します。
キーと値のペアの順序を変更したり、キーの名前を変更したり、値を指定せずにキーを追加したりするには、 CAST 関数または :: 演算子 を使用します。詳細については、 ある構造化型から別の構造型へのキャスト をご参照ください。
キーと値のペアの削除¶
特定のキーと値のペアを削除した既存の OBJECT 値のキーと値のペアを含む新しい OBJECT 値を返すには、 OBJECT_DELETE 関数を呼び出します。
この関数を呼び出す際には、次の点に注意してください。
キーとなる引数には、定数を指定する必要があります。
指定されたキーが OBJECT 型定義の一部でない場合、呼び出しは失敗します。たとえば、 OBJECT 値には指定されたキー
zip_code
が含まれていないため、以下の呼び出しは失敗します。SELECT OBJECT_DELETE( {'city':'San Mateo','state':'CA'}::OBJECT(city VARCHAR,state VARCHAR), 'zip_code' );
093201 (23001): Function OBJECT_DELETE: expected structured object to contain field zip_code but it did not.
この関数は、構造化 OBJECT 値を返します。OBJECT 値の型は、削除されたキーを除きます。例えば、
city
キーを削除するとします。SELECT OBJECT_DELETE( {'city':'San Mateo','state':'CA'}::OBJECT(city VARCHAR,state VARCHAR), 'city' ) AS new_object, SYSTEM$TYPEOF(new_object);
この関数は、
city
キーを含まないOBJECT(state VARCHAR)
型の OBJECT 値を返します。+-----------------+--------------------------------------+ | NEW_OBJECT | SYSTEM$TYPEOF(NEW_OBJECT) | |-----------------+--------------------------------------| | { | OBJECT(state VARCHAR(16777216))[LOB] | | "state": "CA" | | | } | | +-----------------+--------------------------------------+
関数が OBJECT 値からすべてのキーを削除した場合、関数は
OBJECT()
型の空の構造化 OBJECT 値を返します。SELECT OBJECT_DELETE( {'state':'CA'}::OBJECT(state VARCHAR), 'state' ) AS new_object, SYSTEM$TYPEOF(new_object);
+------------+---------------------------+ | NEW_OBJECT | SYSTEM$TYPEOF(NEW_OBJECT) | |------------+---------------------------| | {} | OBJECT()[LOB] | +------------+---------------------------+
構造化 OBJECT 値の型にキーと値のペアが含まれる場合、それらのペアの名前と型は型の括弧内に含まれます(例: OBJECT(city VARCHAR))。空の構造化 OBJECT 値にはキーと値のペアが含まれないため、括弧は空です。
キーと値のペアの挿入と値の更新¶
既存の OBJECT 値にキーと値のペアを追加した新しい OBJECT 値、またはキーに新しい値を返すには、 OBJECT_INSERT 関数を呼び出します。
この関数を呼び出す際には、次の点に注意してください。
キーとなる引数には、定数を指定する必要があります。
updateFlag
引数が FALSE の場合(新しいキーと値のペアを挿入する場合):OBJECT 値にすでに存在するキーを指定するとエラーになります。
SELECT OBJECT_INSERT( {'city':'San Mateo','state':'CA'}::OBJECT(city VARCHAR,state VARCHAR), 'city', 'San Jose', false );
093202 (23001): Function OBJECT_INSERT: expected structured object to not contain field city but it did.
この関数は、構造化 OBJECT 値を返します。OBJECT 値の型には、新しく挿入されたキーが含まれます。例えば、 FLOAT 値の
94402
を持つzipcode
キーを追加するとします。SELECT OBJECT_INSERT( {'city':'San Mateo','state':'CA'}::OBJECT(city VARCHAR,state VARCHAR), 'zip_code', 94402::FLOAT, false ) AS new_object, SYSTEM$TYPEOF(new_object) AS type;
+-------------------------------------+---------------------------------------------------------------------------------------+ | NEW_OBJECT | TYPE | |-------------------------------------+---------------------------------------------------------------------------------------| | { | OBJECT(city VARCHAR(16777216), state VARCHAR(16777216), zip_code FLOAT NOT NULL)[LOB] | | "city": "San Mateo", | | | "state": "CA", | | | "zip_code": 9.440200000000000e+04 | | | } | | +-------------------------------------+---------------------------------------------------------------------------------------+
挿入される値の型は OBJECT 型の定義に追加される型を決定します。この場合、
zipcode
の値は FLOAT にキャストされた値であるため、zipcode
の型は FLOAT になります。
updateFlag
引数が TRUE の場合(既存のキーと値のペアを置き換える場合):OBJECT 値に存在しないキーを指定するとエラーになります。
この関数は、同じ型の構造化 OBJECT 値を返します。
挿入された値の型は、既存のキーの型に 強制 されます。
既存の OBJECT からのキーと値のペアの選択¶
既存の OBJECT 値から選択されたキーと値のペアを含む新しい OBJECT 値を返すには、 OBJECT_PICK 関数を呼び出します。
この関数を呼び出す際には、次の点に注意してください。
キーとなる引数には、定数を指定する必要があります。
第2引数に ARRAY のキーを渡すことはできません。各キーを個別の引数として指定する必要があります。
この関数は、構造化 OBJECT 値を返します。OBJECT 値の型には、指定された順序のキーが含まれます。
例えば、
state
とcity
のキーをこの順序で選択するとします。SELECT OBJECT_PICK( {'city':'San Mateo','state':'CA','zip_code':94402}::OBJECT(city VARCHAR,state VARCHAR,zip_code DOUBLE), 'state', 'city') AS new_object, SYSTEM$TYPEOF(new_object);
この関数は、
OBJECT(state VARCHAR, city VARCHAR)
型の OBJECT 値を返します。+-----------------------+--------------------------------------------------------------+ | NEW_OBJECT | SYSTEM$TYPEOF(NEW_OBJECT) | |-----------------------+--------------------------------------------------------------| | { | OBJECT(state VARCHAR(16777216), city VARCHAR(16777216))[LOB] | | "state": "CA", | | | "city": "San Mateo" | | | } | | +-----------------------+--------------------------------------------------------------+
MAP 値の変換¶
MAP 値を変換するには、以下の関数を使用します。
構造化型の操作¶
次のセクションでは、構造化型の値で、さまざまな SQL 関数とセット演算子を使用する方法について説明します。
構造化型の値をともなう FLATTEN 関数の使用¶
構造化 ARRAY、構造化 OBJECT、 MAP 値を FLATTEN 関数に渡すことができます。半構造化データ型の場合と同様に、PATH 引数を使用してフラット化される値を指定することができます。
フラット化される値が構造化 ARRAY 値で RECURSIVE 引数が FALSE の場合、
value
列には ARRAY 値と同じ型の値が含まれます。例:
SELECT value, SYSTEM$TYPEOF(value) FROM TABLE(FLATTEN(INPUT => [1.08, 2.13, 3.14]::ARRAY(DOUBLE)));
+-------+----------------------+ | VALUE | SYSTEM$TYPEOF(VALUE) | |-------+----------------------| | 1.08 | FLOAT[DOUBLE] | | 2.13 | FLOAT[DOUBLE] | | 3.14 | FLOAT[DOUBLE] | +-------+----------------------+
フラット化される値が MAP 値で、 RECURSIVE 引数が FALSE の場合、
key
列には MAP キーと同じ型のキーが含まれ、value
列には MAP 値と同じ型の値が含まれます。例:
SELECT key, SYSTEM$TYPEOF(key), value, SYSTEM$TYPEOF(value) FROM TABLE(FLATTEN(INPUT => {'my_key': 'my_value'}::MAP(VARCHAR, VARCHAR)));
+--------+------------------------+----------+------------------------+ | KEY | SYSTEM$TYPEOF(KEY) | VALUE | SYSTEM$TYPEOF(VALUE) | |--------+------------------------+----------+------------------------| | my_key | VARCHAR(16777216)[LOB] | my_value | VARCHAR(16777216)[LOB] | +--------+------------------------+----------+------------------------+
それ以外の場合、
key
とvalue
の列は VARIANT 型になります。
MAP 値の場合、返されるキーと値の順序は不確定です。
PARSE_JSON 関数の使用¶
PARSE_JSON 関数は構造化型を返しません。
集合演算子と CASE 式での構造化型の使用¶
構造化 ARRAY、構造化 OBJECT、 MAP 値は次で使用することができます。
集合演算子の場合、異なる型が異なる式で使用されている場合(例: 一方の型が ARRAY(NUMBER)で、もう一方が ARRAY(DOUBLE)の場合)、一方の型はもう一方の型に 強制 されます。
他の半構造化関数の操作¶
次の関数は、構造化 ARRAY、構造化 OBJECT、または MAP 値を入力引数として受け付けません。
構造化型値を入力として渡すとエラーになります。
ドライバーを使用したアプリケーションでの構造化型へのアクセス¶
ドライバーを使用するアプリケーション(例: ODBC または JDBC ドライバー、Python用Snowflake Connectorなど)では、構造化型値は半構造化型値として返されます。例:
構造化 ARRAY 列の値は、半構造化 ARRAY 値としてクライアントアプリケーションに返されます。
構造化 OBJECT または MAP 列の値は、半構造化 OBJECT 値としてクライアントアプリケーションに返されます。
注釈
JDBC ドライバーを使用するクライアントアプリケーションでは、取得するクエリ結果に構造化 ARRAY 値と NULL 値が含まれている場合、 ResultSet.getArray()
メソッドはエラーを返します。
代わりに文字列表現を取得するには、 ResultSet.getString()
メソッドを使用します。
String result = resultSet.getString(1);
ユーザー定義関数(UDFs)とストアドプロシージャでの構造化型の使用¶
ユーザー定義関数(UDF)、ユーザー定義テーブル関数(UDTF)、またはストアドプロシージャを SQL、 Snowflakeスクリプト、 Java、 Python、または Scala で作成する場合は、引数と戻り値に構造化型を使用できます。例:
CREATE OR REPLACE FUNCTION my_udf(
location OBJECT(city VARCHAR, zipcode NUMBER, val ARRAY(BOOLEAN)))
RETURNS VARCHAR
AS
$$
...
$$;
CREATE OR REPLACE FUNCTION my_udtf(check BOOLEAN)
RETURNS TABLE(col1 ARRAY(VARCHAR))
AS
$$
...
$$;
CREATE OR REPLACE PROCEDURE my_procedure(values ARRAY(INTEGER))
RETURNS ARRAY(INTEGER)
LANGUAGE SQL
AS
$$
...
$$;
CREATE OR REPLACE FUNCTION my_function(values ARRAY(INTEGER))
RETURNS ARRAY(INTEGER)
LANGUAGE PYTHON
RUNTIME_VERSION=3.10
AS
$$
...
$$;
注釈
UDFs、 UDTFs、 JavaScript のストアドプロシージャでは、構造化型はまだサポートされていません。
構造化型に関する情報の表示¶
次のセクションでは、構造化型に関する情報を表示するために使用できるビューとコマンドについて説明します。
SHOW COLUMNS コマンドを使用した構造化型情報の表示¶
SHOW COLUMNS コマンドの出力では、 data_type
列に要素、キー、値の型に関する情報が含まれます。
DESCRIBE とその他の SHOW コマンドを使用した構造化型情報の表示¶
次のコマンドの出力には、構造化型に関する情報が含まれます。
例えば、 DESCRIBE RESULT 出力では、 MAP(VARCHAR, VARCHAR) 列の行には type
列に次の値が含まれます。
map(VARCHAR(16777216), VARCHAR(16777216))
ARRAY(NUMBER) 列の行には、 type
列に次の値が含まれます。
ARRAY(NUMBER(38,0))
データベースで使用される構造化型に関する情報の表示¶
構造化型の列の場合、 INFORMATION_SCHEMA COLUMNS ビュー は列の基本データ型(ARRAY、 OBJECT、または MAP)に関する情報のみを提供します。
たとえば、 data_type
列には ARRAY
、 OBJECT
、または MAP
のみが含まれます。列には、要素、キー、値の型は含まれません。
要素、キー、値の型に関する情報を表示するには、次のビューを使用します。
構造化 ARRAY 型にある要素の型に関する情報については、 INFORMATION_SCHEMA の ELEMENT_TYPES ビュー または ACCOUNT_USAGE の ELEMENT_TYPES ビュー をクエリします。
構造化 OBJECT と MAP 型にあるキーと値の型に関する情報については、 INFORMATION_SCHEMA の FIELDS ビュー または ACCOUNT_USAGE の FIELDS ビュー をクエリします。