カテゴリ：

半構造化データ関数と構造化データ関数 （配列/オブジェクト）

ARRAY_CONTAINS

指定された値が指定された配列で見つかった場合、 TRUE を返します。

構文

ARRAY_CONTAINS( <value_expr> , <array> )

引数

value_expr

array で見つかる値。

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          |
|    | ]            |
+----+--------------+