- カテゴリ:
半構造化データ関数と構造化データ関数 (配列/オブジェクト)
ARRAY_CONTAINS¶
指定された値が指定された配列で見つかった場合、 TRUE を返します。
構文¶
ARRAY_CONTAINS( <value_expr> , <array> )
引数¶
戻り値¶
この関数は BOOLEAN 型の値または NULL を返します。
この関数は、
value_expr
がarray
に存在する場合、 TRUE を返します。value_expr
引数が NULL で、配列に SQL NULL の値がある場合(undefined
)。value_expr
引数が JSON nullで、配列に JSON null値がある場合(null
)。
この関数は、
value_expr
がarray
に存在しない場合、value_expr
引数が JSON nullで、配列内に JSON null値が存在しない場合も含めて、 FALSE を返します。この関数は、
value_expr
引数が NULL で、配列に SQL NULL 値がない場合、 NULL を返します。
配列の NULL 値の詳細については、 NULL 値 をご参照ください。
使用上の注意¶
関数は
value_expr
でワイルドカードをサポートしません。しかし、 ARRAY_TO_STRING 関数を使って配列を文字列に変換してから、ワイルドカード文字を使って文字列を検索することはできます。たとえば、 [ NOT ] LIKE や REGEXP_LIKE 関数を使って、ワイルドカードを指定して返された文字列を検索することができます。array
が半構造化配列の場合、以下のデータ型の値に対して VARIANT 値へのvalue_expr
値の 明示的なキャスト が必要です。次の例では、文字列値を VARIANT 値に明示的にキャストしています。
SELECT ARRAY_CONTAINS('mystring2'::VARIANT, ARRAY_CONSTRUCT('mystring1', 'mystring2'));
他のデータ型の値に対しては、明示的なキャストは必要ありません。
例¶
以下のクエリは ARRAY_CONTAINS 関数を SELECT リストで使用します。
この例では、 value_expr
引数が 'hello'
であり、配列に文字列 'hello'
を格納する VARIANT 値が含まれているため、関数は TRUE を返します。
SELECT ARRAY_CONTAINS('hello'::VARIANT, ARRAY_CONSTRUCT('hello', 'hi'));
+------------------------------------------------------------------+
| ARRAY_CONTAINS('HELLO'::VARIANT, ARRAY_CONSTRUCT('HELLO', 'HI')) |
|------------------------------------------------------------------|
| True |
+------------------------------------------------------------------+
この例では、 value_expr
引数は 'hello'
ですが、配列には文字列 'hello'
を格納する VARIANT 値が含まれていないため、関数は FALSE を返します。
SELECT ARRAY_CONTAINS('hello'::VARIANT, ARRAY_CONSTRUCT('hola', 'bonjour'));
+----------------------------------------------------------------------+
| ARRAY_CONTAINS('HELLO'::VARIANT, ARRAY_CONSTRUCT('HOLA', 'BONJOUR')) |
|----------------------------------------------------------------------|
| False |
+----------------------------------------------------------------------+
この例では、 value_expr
引数は NULL ですが、配列に SQL NULL 値が含まれていないため、関数は NULL を返します。
SELECT ARRAY_CONTAINS(NULL, ARRAY_CONSTRUCT('hola', 'bonjour'));
+----------------------------------------------------------+
| ARRAY_CONTAINS(NULL, ARRAY_CONSTRUCT('HOLA', 'BONJOUR')) |
|----------------------------------------------------------|
| NULL |
+----------------------------------------------------------+
この例では、 value_expr
引数が NULL で、配列に SQL NULL 値が含まれているため、関数は TRUE を返します。
SELECT ARRAY_CONTAINS(NULL, ARRAY_CONSTRUCT('hola', NULL));
+-----------------------------------------------------+
| ARRAY_CONTAINS(NULL, ARRAY_CONSTRUCT('HOLA', NULL)) |
|-----------------------------------------------------|
| True |
+-----------------------------------------------------+
この例では、 value_expr
引数が JSON null値であり、配列に JSON null値が含まれているため、関数は TRUE を返します。
SELECT ARRAY_CONTAINS(PARSE_JSON('null'), ARRAY_CONSTRUCT('hola', PARSE_JSON('null')));
+---------------------------------------------------------------------------------+
| ARRAY_CONTAINS(PARSE_JSON('NULL'), ARRAY_CONSTRUCT('HOLA', PARSE_JSON('NULL'))) |
|---------------------------------------------------------------------------------|
| True |
+---------------------------------------------------------------------------------+
この例では、 value_expr
引数は NULL ですが、配列には SQL NULL 値が含まれていないため、関数は NULL を返します(JSON null値が含まれているにもかかわらずです)。
SELECT ARRAY_CONTAINS(NULL, ARRAY_CONSTRUCT('hola', PARSE_JSON('null')));
+-------------------------------------------------------------------+
| ARRAY_CONTAINS(NULL, ARRAY_CONSTRUCT('HOLA', PARSE_JSON('NULL'))) |
|-------------------------------------------------------------------|
| NULL |
+-------------------------------------------------------------------+
以下のクエリは WHERE 句で ARRAY_CONTAINS 関数を使用します。まず、 ARRAY 列を持つテーブルを作成し、データを挿入します。
CREATE OR REPLACE TABLE array_example (id INT, array_column ARRAY);
INSERT INTO array_example (id, array_column)
SELECT 1, ARRAY_CONSTRUCT(1, 2, 3);
INSERT INTO array_example (id, array_column)
SELECT 2, ARRAY_CONSTRUCT(4, 5, 6);
SELECT * FROM array_example;
+----+--------------+
| ID | ARRAY_COLUMN |
|----+--------------|
| 1 | [ |
| | 1, |
| | 2, |
| | 3 |
| | ] |
| 2 | [ |
| | 4, |
| | 5, |
| | 6 |
| | ] |
+----+--------------+
value_expr
に検索する値と array
に ARRAY 列を指定するクエリを実行します。
SELECT * FROM array_example WHERE ARRAY_CONTAINS(5, array_column);
+----+--------------+
| ID | ARRAY_COLUMN |
|----+--------------|
| 2 | [ |
| | 4, |
| | 5, |
| | 6 |
| | ] |
+----+--------------+