ARRAY_CONTAINS

ARRAY_CONTAINS

ARRAY_CONTAINS() 함수는 지정한 값이 지정한 배열에 있으면 TRUE를 반환해요.

출처: Snowflake SQL Reference

본문

문법

ARRAY_CONTAINS( <value_expr> , <array> )

인자

  • value_expr — 배열에서 찾을 값이에요.
  • array — 값을 검색할 배열이에요.

반환

이 함수는 BOOLEAN 타입 값 또는 NULL을 반환해요.

  • value_expr이 array에 존재하면 TRUE를 반환해요. 여기에는 다음 경우가 포함돼요.
  • value_expr이 array에 없으면 FALSE를 반환해요. 여기에는 value_expr 인자가 JSON null이면서 배열에 JSON null 값이 없는 경우도 포함돼요.
  • value_expr 인자가 NULL이면서 배열에 SQL NULL 값이 없으면 NULL을 반환해요.

배열의 NULL 값에 대한 자세한 내용은 NULL 값을 참고하세요.

사용 메모

  • 이 함수는 value_expr에 와일드카드를 지원하지 않아요. 다만 ARRAY_TO_STRING 함수로 배열을 문자열로 변환한 뒤 와일드카드 문자로 그 문자열을 검색할 수 있어요. 예를 들어 [ NOT ] LIKE 및 REGEXP_LIKE 함수로 반환된 문자열을 와일드카드로 검색할 수 있어요.
  • array가 반정형(semi-structured) 배열이면, 다음 데이터 타입의 값은 value_expr 값을 VARIANT 값으로 명시적으로 캐스팅해야 해요. 다음 예제는 문자열 값을 VARIANT 값으로 명시적으로 캐스팅해요: SELECT ARRAY_CONTAINS('mystring2'::VARIANT, ARRAY_CONSTRUCT('mystring1', 'mystring2')); 다른 데이터 타입 값은 명시적 캐스팅이 필요 없어요.

예제

다음 쿼리들은 SELECT 목록에서 ARRAY_CONTAINS 함수를 사용해요.

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 값을 포함하지 않으므로(JSON null 값은 포함하지만) 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          |
|    | ]            |
+----+--------------+

더 알아보기