Tipos de dados estruturados¶

Como especificar uma ARRAY estruturada¶

Para especificar uma ARRAY estruturada, use a seguinte sintaxe:

ARRAY( <element_type> [ NOT NULL ] )

Onde:

• element_type é o tipo de dados Snowflake dos elementos nesta ARRAY.

  Você também pode especificar uma ARRAY estruturada, um OBJECT estruturado ou um MAP como o tipo do elemento.

  Nota

  Na definição de uma coluna da tabela Iceberg, não é possível especificar uma VARIANT, ARRAY semiestruturada ou OBJECT semiestruturado como o tipo do elemento ARRAY.

• NOT NULL especifica que ARRAY não pode conter nenhum elemento que seja NULL.

Por exemplo, compare os tipos retornados pela função SYSTEM$TYPEOF na instrução a seguir:

• A expressão da primeira coluna converte uma ARRAY semiestruturada em uma ARRAY estruturada (uma ARRAY de elementos NUMBER).

• A expressão da segunda coluna especifica uma ARRAY semiestruturada.

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

Como especificar um OBJECT estruturado¶

Para especificar um OBJECT estruturado, use a seguinte sintaxe:

OBJECT(
  [
    <key> <value_type> [ NOT NULL ]
    [ , <key> <value_type> [ NOT NULL ] ]
    [ , ... ]
  ]
)

Onde:

• key especifica uma chave para o OBJECT.

  • Cada key em uma definição de objeto deve ser único.

  • A ordem das chaves faz parte da definição do objeto. Não é permitido comparar dois OBJECTs que possuem as mesmas chaves em uma ordem diferente. (Ocorre um erro de tempo de compilação.)

  • Se você não especificar nenhuma chave, mas especificar os parênteses (ou seja, se usar OBJECT()), o tipo resultante será um OBJECT estruturado que não contém chaves. Um OBJECT estruturado sem chaves é diferente de um OBJECT semiestruturado.

• value_type é o tipo de dados Snowflake do valor correspondente à chave.

  Você também pode especificar uma ARRAY estruturada, um OBJECT estruturado ou um MAP como o tipo de valor.

  Nota

  Na definição de uma coluna da tabela Iceberg, você não pode especificar VARIANT, ARRAY semiestruturada ou OBJECT semiestruturado como o tipo de valor correspondente à chave OBJECT.

• NOT NULL especifica que o valor correspondente à chave não pode ser NULL.

Por exemplo, compare os tipos retornados pela função SYSTEM$TYPEOF na instrução a seguir:

• A expressão da primeira coluna converte um OBJECT semiestruturado em um OBJECT estruturado que contém as seguintes chaves e valores:

  • Uma chave chamada str com um valor VARCHAR que não é NULL.

  • Uma chave chamada num com um valor NUMBER.

• A expressão da segunda coluna especifica um OBJECT semiestruturado.

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), num NUMBER(38,0) NOT NULL )[LOB] | OBJECT[LOB]            |
+----------------------------------------------------------------+------------------------+

Como especificar um MAP¶

Para especificar um MAP, use a seguinte sintaxe:

MAP( <key_type> , <value_type> [ NOT NULL ] )

Onde:

• key_type é o tipo de dados Snowflake da chave do mapa. Você deve usar um dos seguintes tipos de chaves:

  • VARCHAR

  • NUMBER com a escala 0

  Você não pode usar um tipo de dados de ponto flutuante como tipo de chave.

  Observe que as chaves do mapa não podem ser NULL.

• value_type é o tipo de dados Snowflake dos valores no mapa.

  Você também pode especificar uma ARRAY estruturada, um OBJECT estruturado ou um MAP como o tipo de valores.

  Nota

  Na definição de uma coluna da tabela Iceberg, não é possível especificar uma VARIANT, ARRAY semiestruturada ou OBJECT semiestruturado como o tipo de valor no MAP.

• NOT NULL especifica que o valor correspondente à chave não pode ser NULL. NOT NULL especifica que o valor não pode ser NULL.

O exemplo a seguir converte um OBJECT semiestruturado em um MAP e usa a função SYSTEM$TYPEOF para imprimir o tipo resultante do objeto. O MAP associa chaves VARCHAR a valores 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] |
+------------------------------------------------+

Conversão explícita de um tipo semiestruturado em um tipo estruturado¶

Para converter explicitamente um objeto semiestruturado em um tipo estruturado, você pode chamar a função CAST ou usar o operador ::.

Você só pode converter os seguintes objetos semiestruturados no tipo estruturado correspondente; caso contrário, ocorrerá um erro de tempo de execução.

As próximas seções fornecem mais detalhes sobre como os tipos são convertidos:

• Conversão de ARRAYs e VARIANTs semiestruturadas para ARRAYs estruturadas

• Conversão de OBJECTs e VARIANTs semiestruturados em OBJECTs estruturados

• Conversão de OBJECTs e VARIANTs semiestruturados em MAPs

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

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

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

Conversão explícita de um tipo estruturado em um tipo semiestruturado¶

Para converter explicitamente um tipo estruturado em um tipo semiestruturado, você pode chamar a função CAST, usar o operador :: ou chamar uma das funções de conversão (por exemplo, TO_ARRAY, TO_OBJECT ou TO_VARIANT).

Por exemplo:

• Se col_structured_array for ARRAY(VARCHAR):

  • CAST(col_structured_array AS ARRAY) retorna uma ARRAY semiestruturada.

  • CAST(col_structured_array AS VARIANT) retorna uma VARIANT que contém uma ARRAY semiestruturada.

• Se col_structured_object for OBJECT(nome VARCHAR, estado VARCHAR):

  • CAST(col_structured_object AS OBJECT) retorna um OBJECT semiestruturado.

  • CAST(col_structured_object AS VARIANT) retorna uma VARIANT que contém um OBJECT semiestruturado.

• Se col_map for MAP(VARCHAR, VARCHAR):

  • CAST(col_map AS OBJECT) retorna um OBJECT semiestruturado.

  • CAST(col_map AS VARIANT) retorna uma VARIANT que contém um OBJECT semiestruturado.

Observe o seguinte:

• Ao converter em um OBJECT semiestruturado, a ordem das chaves no OBJECT estruturado não é preservada.

• Ao converter um OBJECT ou MAP estruturado em um OBJECT ou VARIANT semiestruturado, quaisquer valores NULL são convertidos em valores JSON nulos.

  Se você estiver convertendo uma ARRAY estruturada em uma VARIANT, os valores NULL serão preservados como estão.

  SELECT [1,2,NULL,3]::ARRAY(INTEGER)::VARIANT;
  

  Copy

  +---------------------------------------+
  | [1,2,NULL,3]::ARRAY(INTEGER)::VARIANT |
  |---------------------------------------|
  | [                                     |
  |   1,                                  |
  |   2,                                  |
  |   undefined,                          |
  |   3                                   |
  | ]                                     |
  +---------------------------------------+
  

• Se você estiver convertendo um MAP que usa um tipo NUMBER para chaves, as chaves MAP serão convertidas em cadeias de caracteres no OBJECT retornado.

Conversão implícita de um valor (coerção)¶

As regras a seguir se aplicam à conversão implícita (coerção) de um tipo estruturado em outro tipo estruturado:

• Um tipo estruturado pode ser forçado a outro tipo estruturado se os dois tipos básicos de objetos forem iguais:

  • Uma ARRAY de um tipo pode ser forçada para uma ARRAY de outro tipo, desde que o primeiro tipo de elemento seja coercível para o segundo tipo de elemento.

    Um tipo de elemento pode ser forçado a outro tipo de elemento em qualquer um dos seguintes casos:

    • Ambos os tipos são numéricos. Os seguintes casos são suportados:

      • Ambos usam o mesmo tipo numérico, mas possivelmente diferem em precisão e/ou escala.

      • Como forçar NUMBER a FLOAT (e vice-versa).

    • Ambos os tipos são carimbos de data/hora. Os seguintes casos são suportados:

      • Ambos usam o mesmo tipo, mas possivelmente diferem em precisão.

      • Como forçar TIMESTAMP_LTZ a TIMESTAMP_TZ (e vice-versa).

    Por exemplo:

    • Uma ARRAY(NUMBER) pode ser forçada a uma ARRAY(DOUBLE).

    • Uma ARRAY(DATE) não pode ser forçada a uma ARRAY(NUMBER).

  • Um OBJECT com uma definição de tipo pode ser forçado a um OBJECT com outra definição de tipo somente se todas as afirmações a seguir forem verdadeiras:

    • Ambos os tipos de OBJECT têm o mesmo número de chaves.

    • Ambos os tipos de OBJECT usam os mesmos nomes para chaves.

    • As chaves em ambos os tipos de OBJECT estão na mesma ordem.

    • O tipo de cada valor em um tipo de OBJECT pode ser forçado para o tipo do valor correspondente no outro tipo de OBJECT.

      Como é o caso dos tipos de elementos em ARRAYs estruturadas, você pode forçar o tipo de um valor para outro tipo somente se:

      • Ambos os tipos são numéricos. Os seguintes casos são suportados:

        • Ambos usam o mesmo tipo numérico, mas possivelmente diferem em precisão e/ou escala.

        • Como forçar NUMBER a FLOAT (e vice-versa).

      • Ambos os tipos são carimbos de data/hora. Os seguintes casos são suportados:

        • Ambos usam o mesmo tipo, mas possivelmente diferem em precisão.

        • Como forçar TIMESTAMP_LTZ a TIMESTAMP_TZ (e vice-versa).

    Por exemplo:

    • Um OBJECT(cidade VARCHAR, CEP NUMBER) puder ser forçado a um OBJECT(cidade VARCHAR, CEP DOUBLE).

    • Um OBJECT(cidade VARCHAR, CEP NUMBER) não puder ser forçado a um OBJECT(cidade VARCHAR, CEP DATE).

  • Um MAP com um tipo de valor pode ser forçado a um MAP com um tipo de valor diferente se:

    • Ambos os tipos de valor forem numéricos. Os seguintes casos são suportados:

      • Ambos usam o mesmo tipo numérico, mas possivelmente diferem em precisão e/ou escala.

      • Como forçar NUMBER a FLOAT (e vice-versa).

    • Ambos os tipos de valor forem carimbos de data/hora. Os seguintes casos são suportados:

      • Ambos usam o mesmo tipo, mas possivelmente diferem em precisão.

      • Como forçar TIMESTAMP_LTZ a TIMESTAMP_TZ (e vice-versa).

    Por exemplo, um MAP(VARCHAR, NUMBER) pode ser forçado a um MAP(VARCHAR, DOUBLE).

  • Um MAP com um tipo de chave pode ser forçado a um MAP com um tipo de chave diferente se ambos os tipos de chave usarem o mesmo tipo NUMERIC inteiro que difere apenas na precisão.

    Por exemplo, um MAP(VARCHAR, NUMBER) não pode ser forçado a um MAP(NUMBER, NUMBER).

• Um tipo estruturado não pode ser forçado a um objeto semiestruturado (e vice-versa).

• Um valor VARCHAR não pode ser forçado a um tipo estruturado.

Conversão de um tipo estruturado em outro¶

Você pode chamar a função CAST ou usar o operador :: para converter um tipo estruturado em outro. Você pode converter de e nos seguintes tipos estruturados:

• Para ARRAYs estruturadas:

  Você pode converter uma ARRAY de um tipo em uma ARRAY de outro tipo.

• Para OBJECTs estruturados:

  • Você pode usar uma conversão para alterar a ordem dos pares chave-valor em um OBJECT.

  • Você pode usar uma conversão para alterar os nomes das chaves em um OBJECT.

  • Você pode usar uma conversão para adicionar chaves a um OBJECT.

  • Você pode converter um OBJECT estruturado em um MAP.

• Para MAPs:

  • Você pode converter um MAP com chaves e valores de um tipo específico em um MAP com chaves e valores de um tipo diferente.

  • Você pode converter um MAP em um OBJECT estruturado.

Se não for possível converter os valores de um tipo em outro, a conversão falhará. Por exemplo, a tentativa de converter uma ARRAY(BOOLEAN) em uma ARRAY(DATE) falha.

SELECT CAST(
  CAST([1,2,3] AS ARRAY(NUMBER))
  AS ARRAY(VARCHAR)
);

SELECT CAST(
  {'city': 'San Mateo','state': 'CA'}::OBJECT(city VARCHAR, state VARCHAR)
  AS OBJECT(state VARCHAR, city VARCHAR)
);

SELECT CAST({'city':'San Mateo','state': 'CA'}::OBJECT(city VARCHAR, state VARCHAR)
  AS OBJECT(city_name VARCHAR, state_name VARCHAR) RENAME FIELDS);

+------------------------------------------------------------------------------+
| CAST({'CITY':'SAN MATEO','STATE': 'CA'}::OBJECT(CITY VARCHAR, STATE VARCHAR) |
|   AS OBJECT(CITY_NAME VARCHAR, STATE_NAME VARCHAR) RENAME FIELDS)            |
|------------------------------------------------------------------------------|
| {                                                                            |
|   "city_name": "San Mateo",                                                  |
|   "state_name": "CA"                                                         |
| }                                                                            |
+------------------------------------------------------------------------------+

SELECT CAST({'city':'San Mateo','state': 'CA'}::OBJECT(city VARCHAR, state VARCHAR)
  AS OBJECT(city VARCHAR, state VARCHAR, zipcode NUMBER) ADD FIELDS);

+------------------------------------------------------------------------------+
| CAST({'CITY':'SAN MATEO','STATE': 'CA'}::OBJECT(CITY VARCHAR, STATE VARCHAR) |
|   AS OBJECT(CITY VARCHAR, STATE VARCHAR, ZIPCODE NUMBER) ADD FIELDS)         |
|------------------------------------------------------------------------------|
| {                                                                            |
|   "city": "San Mateo",                                                       |
|   "state": "CA",                                                             |
|   "zipcode": null                                                            |
| }                                                                            |
+------------------------------------------------------------------------------+

Como usar funções SQL para criar ARRAYs e OBJECTs estruturados¶

As seguintes funções criam ARRAYs semiestruturadas:

• ARRAY_CONSTRUCT

• ARRAY_CONSTRUCT_COMPACT

• ARRAY_AGG

• TO_ARRAY

As seguintes funções criam OBJECTs semiestruturados:

• OBJECT_CONSTRUCT

• OBJECT_CONSTRUCT_KEEP_NULL

• OBJECT_AGG

• TO_OBJECT

Para criar uma ARRAY ou OBJECT estruturado, use essas funções e converta explicitamente o valor de retorno da função. Por exemplo:

SELECT ARRAY_CONSTRUCT(10, 20, 30)::ARRAY(NUMBER);

SELECT OBJECT_CONSTRUCT(
  'name', 'abc',
  'created_date', '2020-01-18'::DATE
)::OBJECT(
  name VARCHAR,
  created_date DATE
);

Para obter mais detalhes, consulte Conversão explícita de um tipo semiestruturado em um tipo estruturado.

Uso de constantes de ARRAY e OBJECT para criar ARRAYs e OBJECTs estruturados¶

Ao especificar uma constante de ARRAY ou uma constante de OBJECT, você está especificando uma ARRAY ou um OBJECT semiestruturado.

Para criar uma ARRAY ou um OBJECT estruturado, você deve converter explicitamente a expressão. Por exemplo:

SELECT [10, 20, 30]::ARRAY(NUMBER);

SELECT {
  'name': 'abc',
  'created_date': '2020-01-18'::DATE
}::OBJECT(
  name VARCHAR,
  created_date DATE
);

Para obter mais detalhes, consulte Conversão explícita de um tipo semiestruturado em um tipo estruturado.

Como criar um MAP¶

Para criar um MAP, crie um objeto semiestruturado e converta o OBJECT em um MAP.

Por exemplo, as instruções a seguir produzem 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
);

A instrução a seguir produz o MAP {-10->'CA',-20->'OR'}:

SELECT {
  '-10': 'CA',
  '-20': 'OR'
}::MAP(
  NUMBER,
  VARCHAR
)

Para obter mais detalhes, consulte Conversão de OBJECTs e VARIANTs semiestruturados em MAPs.

Como obter a lista de chaves de um OBJECT estruturado¶

Para obter a lista de chaves em um OBJECT estruturado, chame a função OBJECT_KEYS:

SELECT OBJECT_KEYS({'city':'San Mateo','state':'CA'}::OBJECT(city VARCHAR, state VARCHAR));

Se o valor de entrada for um OBJECT estruturado, a função retornará uma ARRAY(VARCHAR) contendo as chaves. Se o valor de entrada for um OBJECT semiestruturado, a função retornará uma ARRAY.

Como obter a lista de chaves de um MAP¶

Para obter a lista de chaves em um MAP, chame a função MAP_KEYS:

SELECT MAP_KEYS({'my_key':'my_value'}::MAP(VARCHAR,VARCHAR));

Acesso aos valores e elementos de tipos estruturados¶

Você pode usar os seguintes métodos para acessar valores e elementos de ARRAYs estruturadas, OBJECTs estruturados e MAPs:

• A função GET

• A função GET_IGNORE_CASE

• A função GET_PATH

• Notação de pontos

• Notação de parênteses

Os valores e elementos retornados têm o tipo especificado para o objeto, em vez de VARIANT.

O exemplo a seguir passa o primeiro elemento de uma ARRAY semiestruturada e uma ARRAY(VARCHAR) para a função SYSTEM$TYPEOF para retornar o tipo de dados desse elemento:

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

Observe o seguinte:

• Ao passar um OBJECT estruturado para a função GET ou GET_IGNORE_CASE, você deve especificar uma constante para a chave.

  Você não precisa especificar uma constante se estiver passando um MAP ou uma ARRAY estruturada para a função GET.

  Você também não precisa especificar uma constante se estiver passando um MAP para a função GET_IGNORE_CASE.

• Ao passar um OBJECT estruturado, ARRAY estruturada ou MAP para a função GET_PATH, você deve especificar uma constante para o nome do caminho.

• Para um objeto estruturado, se você usar uma chave OBJECT ou um caminho que não existe, ocorrerá um erro em tempo de compilação.

  Por outro lado, quando você usa um índice, chave ou caminho que não existe com um objeto semiestruturado, a função retorna NULL.

Como determinar o tamanho de uma ARRAY estruturada¶

Para determinar o tamanho de uma ARRAY estruturada, passe a ARRAY para a função ARRAY_SIZE:

SELECT ARRAY_SIZE([1,2,3]::ARRAY(NUMBER));

Como determinar o tamanho de um MAP¶

Para determinar o tamanho de um MAP, passe o MAP para a função MAP_SIZE:

SELECT MAP_SIZE({'my_key':'my_value'}::MAP(VARCHAR,VARCHAR));

Como procurar elementos em uma ARRAY estruturada¶

Para determinar se um elemento está presente em uma ARRAY estruturada, chame a função ARRAY_CONTAINS. Por exemplo:

SELECT ARRAY_CONTAINS(10, [1, 10, 100]::ARRAY(NUMBER));

Para determinar a posição de um elemento em uma ARRAY estruturada, chame a função ARRAY_POSITION. Por exemplo:

SELECT ARRAY_POSITION(10, [1, 10, 100]::ARRAY(NUMBER));

Como determinar se um MAP contém uma chave¶

Para determinar se MAP contém uma chave, chame a função MAP_CONTAINS_KEY:

Por exemplo:

SELECT MAP_CONTAINS_KEY('key_to_find', my_map);

SELECT MAP_CONTAINS_KEY(10, my_map);

Comparação de objetos estruturados com objetos semiestruturados¶

Você não pode comparar uma ARRAY estruturada, OBJECT estruturado ou MAP com uma ARRAY, OBJECT ou VARIANT semiestruturado.

Comparação de objetos estruturados com outros objetos estruturados¶

Você pode comparar dois objetos do mesmo tipo (por exemplo, duas ARRAYs estruturadas, dois OBJECTs estruturados ou dois MAPs).

Atualmente, os seguintes operadores de comparação são suportados para comparar tipos estruturados:

• =

• !=

• <

• <=

• >=

• >

Ao comparar dois objetos estruturados quanto à igualdade, observe o seguinte:

• Se um tipo não puder ser forçado para outro tipo, a comparação falhará.

• Ao comparar MAPs que possuem chaves numéricas, as chaves são comparadas como números (não como valores VARCHAR).

Ao comparar dois objetos estruturados usando <, <=, >= ou >, os campos do objeto estruturado são comparados em ordem alfabética. Por exemplo, o seguinte objeto:

{'a':2,'b':1}::OBJECT(b INTEGER,a INTEGER)

é maior que:

{'a':1,'b':2}::OBJECT(b INTEGER,a INTEGER)

Como determinar se duas ARRAYs se sobrepõem¶

Se você precisar determinar se os elementos de duas ARRAYs estruturadas se sobrepõem, chame a função ARRAYS_OVERLAP. Por exemplo:

SELECT ARRAYS_OVERLAP(numeric_array, other_numeric_array);

As ARRAYs devem ser de tipos comparáveis.

Observe que você não pode passar uma ARRAY semiestruturada e uma ARRAY estruturada para esta função. Ambas ARRAYs devem ser estruturadas ou semiestruturadas.

Transformação de ARRAYs estruturadas¶

Ao passar uma ARRAY estruturada para essas funções, as funções retornam uma ARRAY estruturada do mesmo tipo:

• ARRAY_APPEND

• ARRAY_CAT

• ARRAY_COMPACT

• ARRAY_EXCEPT

• ARRAY_INSERT

• ARRAY_INTERSECTION

• ARRAY_PREPEND

• ARRAY_SLICE

• ARRAY_UNION_AGG

As próximas seções explicam como essas funções funcionam com ARRAYs estruturadas.

• Funções que adicionam elementos a matrizes

• Funções que aceitam várias ARRAYs como entrada

SELECT ARRAY_APPEND( [1,2]::ARRAY(DOUBLE), 3::NUMBER );

SELECT ARRAY_APPEND( [1,2]::ARRAY(DOUBLE), '3' );

SELECT ARRAY_APPEND( [1,2]::ARRAY(NUMBER), '2022-02-02'::DATE );

SELECT ARRAY_CAT( [1,2]::ARRAY(NUMBER), ['3','4'] );

SELECT ARRAY_CAT( [1,2], ['3','4']::ARRAY(VARCHAR) );

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

SELECT ARRAY_EXCEPT( [1,2]::ARRAY(NUMBER), [2,3]::ARRAY(DOUBLE) );

SELECT ARRAY_EXCEPT( [1,2]::ARRAY(NUMBER), ['2','3']::ARRAY(VARCHAR) );

Transformação de OBJECTs estruturados¶

As seções a seguir explicam como retornar um OBJECT estruturado que foi transformado de outro OBJECT:

• Remoção dos pares chave-valor

• Inserção de pares chave-valor e atualização de valores

• Seleção de pares chave-valor de um OBJECT existente

Se você precisar alterar a ordem dos pares chave-valor, renomear chaves ou adicionar chaves sem especificar valores, use a função CAST ou operador ::. Para obter mais detalhes, consulte Conversão de um tipo estruturado em outro.

Transformação de MAPs¶

Para transformar MAPs, use as seguintes funções:

• MAP_CAT

• MAP_DELETE

• MAP_INSERT

• MAP_PICK

Como usar a função FLATTEN com tipos estruturados¶

Você pode passar ARRAYs estruturadas, OBJECTs estruturados e MAPs para a função FLATTEN. Como é o caso dos tipos de dados semiestruturados, você pode usar o argumento PATH para especificar o valor que está sendo nivelado.

• Se o valor que estiver sendo nivelado for uma ARRAY estruturada e o argumento RECURSIVE for FALSE, a coluna value conterá um valor do mesmo tipo que ARRAY.

  Por exemplo:

  SELECT value, SYSTEM$TYPEOF(value)
    FROM TABLE(FLATTEN(INPUT => [1.08, 2.13, 3.14]::ARRAY(DOUBLE)));
  

  Copy

  +-------+----------------------+
  | VALUE | SYSTEM$TYPEOF(VALUE) |
  |-------+----------------------|
  |  1.08 | FLOAT[DOUBLE]        |
  |  2.13 | FLOAT[DOUBLE]        |
  |  3.14 | FLOAT[DOUBLE]        |
  +-------+----------------------+
  

• Se o valor que estiver sendo nivelado for MAP e o argumento RECURSIVE for FALSE, a coluna key conterá uma chave do mesmo tipo que a chave MAP e a coluna value conterá um valor do mesmo tipo que o valor MAP.

  Por exemplo:

  SELECT key, SYSTEM$TYPEOF(key), value, SYSTEM$TYPEOF(value)
    FROM TABLE(FLATTEN(INPUT => {'my_key': 'my_value'}::MAP(VARCHAR, VARCHAR)));
  

  Copy

  +--------+------------------------+----------+------------------------+
  | KEY    | SYSTEM$TYPEOF(KEY)     | VALUE    | SYSTEM$TYPEOF(VALUE)   |
  |--------+------------------------+----------+------------------------|
  | my_key | VARCHAR(16777216)[LOB] | my_value | VARCHAR(16777216)[LOB] |
  +--------+------------------------+----------+------------------------+
  

• Caso contrário, as colunas key e value terão o tipo VARIANT.

Para MAPs, a ordem das chaves e dos valores retornados é indeterminada.

Uso da função PARSE_JSON¶

Observe que a função PARSE_JSON não retorna tipos estruturados.

Uso de tipos estruturados com operadores de conjunto e expressões CASE¶

Você pode usar ARRAYs estruturadas, OBJECTs estruturados e MAPs em:

• Expressões de consulta combinadas por operadores de conjunto (por exemplo, UNION ALL).

• Expressões CASE.

Para operadores de conjunto, se diferentes tipos forem usados nas diferentes expressões (por exemplo, se um tipo for ARRAY(NUMBER) e o outro for ARRAY(DOUBLE)), um tipo será forçado para o outro.

Como trabalhar com outras funções semiestruturadas¶

As funções a seguir não aceitam uma ARRAY estruturada, OBJECT estruturado ou MAP como argumento de entrada:

• AS_ARRAY

• AS_OBJECT

• IS_ARRAY

• IS_OBJECT

• TYPEOF

Passar um tipo estruturado como entrada resulta em um erro.

Uso do comando SHOW COLUMNS para visualizar informações de tipo estruturado¶

Na saída do comando SHOW COLUMNS, a coluna data_type inclui informações sobre os tipos de elementos, chaves e valores.

Uso dos comandos DESCRIBE e outros SHOW para visualizar informações de tipo estruturado¶

A saída dos comandos a seguir inclui informações sobre tipos estruturados:

• DESCRIBE TABLE

• DESCRIBE RESULT

• DESCRIBE FUNCTION

• DESCRIBE PROCEDURE

• SHOW FUNCTIONS

• SHOW PROCEDURES

Por exemplo, na saída DESCRIBE RESULT, a linha de uma coluna MAP(VARCHAR, VARCHAR) contém o seguinte valor na coluna type:

map(VARCHAR(16777216), VARCHAR(16777216))

A linha de uma coluna ARRAY(NUMBER) contém o seguinte valor na coluna type:

ARRAY(NUMBER(38,0))

Exibição de informações sobre os tipos estruturados usados em um banco de dados¶

Para colunas de tipos estruturados, INFORMATION_SCHEMA Exibição COLUMNS fornece apenas informações sobre o tipo de dados básico da coluna (ARRAY, OBJECT ou MAP).

Por exemplo, a coluna data_type contém apenas “ARRAY”, “OBJECT” ou “MAP”. A coluna não inclui os tipos de elementos, chaves ou valores.

Para visualizar informações sobre os tipos de elementos, chaves e valores, use as seguintes exibições:

• Para obter informações sobre os tipos de elementos em ARRAYs estruturadas, consulte a exibição ELEMENT_TYPES em INFORMATION_SCHEMA ou a exibição ELEMENT_TYPES em ACCOUNT_USAGE.

• Para obter informações sobre os tipos de chaves e valores em OBJECTs estruturados e MAPs, consulte a exibição FIELDS em INFORMATION_SCHEMA ou a exibição FIELDS em ACCOUNT_USAGE.