Categorias:

Funções de dados semiestruturados e estruturados (Matriz/objeto)

ARRAY_CONTAINS

Retorna TRUE se o valor especificado for encontrado na matriz especificada.

Sintaxe

ARRAY_CONTAINS( <value_expr> , <array> )
Copy

Argumentos

value_expr

Valor a ser encontrado em array.

array

A matriz a ser pesquisada.

Retornos

Esta função retorna um valor do tipo BOOLEAN ou NULL:

  • A função retorna TRUE se value_expr estiver presente em array, incluindo os seguintes casos:

    • Quando o argumento value_expr for NULL e houver um valor SQL NULL na matriz (undefined).

    • Quando o argumento value_expr for JSON nulo e houver um valor JSON nulo na matriz (null).

  • A função retorna FALSE se value_expr não estiver presente em array, inclusive quando o argumento value_expr for JSON nulo e não houver valores JSON nulos na matriz.

  • A função retorna NULL se o argumento value_expr for NULL e não houver valores SQL NULL na matriz.

Para obter mais informações sobre valores NULL em matrizes, consulte Valores NULL.

Notas de uso

  • A função não oferece suporte a curingas em value_expr. No entanto, você pode usar a função ARRAY_TO_STRING para converter uma matriz em uma cadeia de caracteres e, em seguida, pesquisar a cadeia de caracteres com caracteres curinga. Por exemplo, você pode especificar curingas para pesquisar a cadeia de caracteres retornada usando as funções [ NOT ] LIKE e REGEXP_LIKE.

  • Se array for uma matriz semiestruturada, a conversão explícita do valor value_expr para um valor VARIANT será necessária para os valores dos seguintes tipos de dados:

    O exemplo a seguir converte explicitamente um valor de cadeia de caracteres em um valor de VARIANT:

    SELECT ARRAY_CONTAINS('mystring2'::VARIANT, ARRAY_CONSTRUCT('mystring1', 'mystring2'));
    
    Copy

    A conversão explícita não é necessária para valores de outros tipos de dados.

Exemplos

As consultas a seguir usam a função ARRAY_CONTAINS em uma lista SELECT.

Neste exemplo, a função retorna TRUE porque o argumento value_expr é 'hello' e a matriz contém um valor VARIANT que armazena a cadeia de caracteres 'hello':

SELECT ARRAY_CONTAINS('hello'::VARIANT, ARRAY_CONSTRUCT('hello', 'hi'));
Copy
+------------------------------------------------------------------+
| ARRAY_CONTAINS('HELLO'::VARIANT, ARRAY_CONSTRUCT('HELLO', 'HI')) |
|------------------------------------------------------------------|
| True                                                             |
+------------------------------------------------------------------+

Neste exemplo, a função retorna FALSE porque o argumento value_expr é 'hello', mas a matriz não contém um valor VARIANT que armazene a cadeia de caracteres 'hello':

SELECT ARRAY_CONTAINS('hello'::VARIANT, ARRAY_CONSTRUCT('hola', 'bonjour'));
Copy
+----------------------------------------------------------------------+
| ARRAY_CONTAINS('HELLO'::VARIANT, ARRAY_CONSTRUCT('HOLA', 'BONJOUR')) |
|----------------------------------------------------------------------|
| False                                                                |
+----------------------------------------------------------------------+

Neste exemplo, a função retorna NULL porque o argumento value_expr é NULL, mas a matriz não contém um valor SQL NULL:

SELECT ARRAY_CONTAINS(NULL, ARRAY_CONSTRUCT('hola', 'bonjour'));
Copy
+----------------------------------------------------------+
| ARRAY_CONTAINS(NULL, ARRAY_CONSTRUCT('HOLA', 'BONJOUR')) |
|----------------------------------------------------------|
| NULL                                                     |
+----------------------------------------------------------+

Neste exemplo, a função retorna TRUE porque o argumento value_expr é NULL e a matriz contém um valor SQL NULL:

SELECT ARRAY_CONTAINS(NULL, ARRAY_CONSTRUCT('hola', NULL));
Copy
+-----------------------------------------------------+
| ARRAY_CONTAINS(NULL, ARRAY_CONSTRUCT('HOLA', NULL)) |
|-----------------------------------------------------|
| True                                                |
+-----------------------------------------------------+

Neste exemplo, a função retorna TRUE porque o argumento value_expr é um valor nulo JSON e a matriz contém um valor JSON nulo:

SELECT ARRAY_CONTAINS(PARSE_JSON('null'), ARRAY_CONSTRUCT('hola', PARSE_JSON('null')));
Copy
+---------------------------------------------------------------------------------+
| ARRAY_CONTAINS(PARSE_JSON('NULL'), ARRAY_CONSTRUCT('HOLA', PARSE_JSON('NULL'))) |
|---------------------------------------------------------------------------------|
| True                                                                            |
+---------------------------------------------------------------------------------+

Neste exemplo, a função retorna NULL porque o argumento value_expr é NULL, mas a matriz não contém um valor SQL NULL (embora contenha um valor nulo JSON):

SELECT ARRAY_CONTAINS(NULL, ARRAY_CONSTRUCT('hola', PARSE_JSON('null')));
Copy
+-------------------------------------------------------------------+
| ARRAY_CONTAINS(NULL, ARRAY_CONSTRUCT('HOLA', PARSE_JSON('NULL'))) |
|-------------------------------------------------------------------|
| NULL                                                              |
+-------------------------------------------------------------------+

A consulta a seguir usa a função ARRAY_CONTAINS em uma cláusula WHERE. Primeiro, crie uma tabela com uma coluna ARRAY e insira os dados:

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;
Copy
+----+--------------+
| ID | ARRAY_COLUMN |
|----+--------------|
|  1 | [            |
|    |   1,         |
|    |   2,         |
|    |   3          |
|    | ]            |
|  2 | [            |
|    |   4,         |
|    |   5,         |
|    |   6          |
|    | ]            |
+----+--------------+

Execute uma consulta que especifique o valor a ser encontrado para value_expr e a coluna ARRAY para array:

SELECT * FROM array_example WHERE ARRAY_CONTAINS(5, array_column);
Copy
+----+--------------+
| ID | ARRAY_COLUMN |
|----+--------------|
|  2 | [            |
|    |   4,         |
|    |   5,         |
|    |   6          |
|    | ]            |
+----+--------------+