Tipos de dados estruturados¶

Como especificar um tipo ARRAY estruturado¶

Para especificar um tipo ARRAY estruturado, 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 um VARIANT, um ARRAY semiestruturado ou um OBJECT semiestruturado como o tipo do elemento ARRAY.

• NOT NULL especifica que o 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 um valor ARRAY semiestruturado em um valor ARRAY estruturado (um ARRAY de elementos NUMBER).

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

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 tipo OBJECT estruturado¶

Para especificar um tipo OBJECT estruturado, use a seguinte sintaxe:

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

Onde:

• key especifica uma chave para o tipo OBJECT.

  • Cada key em uma definição de OBJECT deve ser exclusivo.

  • A ordem das chaves faz parte da definição de OBJECT. Não é permitido comparar dois valores OBJECT que tenham 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 um VARIANT, ARRAY semiestruturado ou OBJECT semiestruturado como o tipo do 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 valor OBJECT semiestruturado em um valor 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 valor 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) NOT NULL, num NUMBER(38,0))[LOB] | OBJECT[LOB]            |
+---------------------------------------------------------------+------------------------+

Especificando um tipo de MAP¶

Para especificar um tipo de 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 o tipo da chave.

  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, você não pode especificar um VARIANT, um ARRAY semiestruturado ou um OBJECT semiestruturado como o tipo do valor no MAP.

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

O exemplo a seguir converte um valor OBJECT semiestruturado em um valor MAP e usa a função SYSTEM$TYPEOF para imprimir o tipo resultante do valor. 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 valor de um tipo semiestruturado em um valor de um tipo estruturado, é possível chamar a função CAST ou usar o operador ::.

Só é possível converter valores dos seguintes tipos semiestruturados em valores do 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:

• Converter valores semiestruturados de ARRAY e VARIANT em valores ARRAY estruturados

• Converter valores semiestruturados de OBJECT e VARIANT em valores OBJECT estruturados

• Como converter valores OBJECT e VARIANT semiestruturados em valores MAP

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 valor de um tipo estruturado em um valor de um tipo semiestruturado, é possível 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 do tipo ARRAY(VARCHAR):

  • CAST(col_structured_array AS ARRAY) retorna um valor ARRAY semiestruturado.

  • CAST(col_structured_array AS VARIANT) retorna um valor VARIANT que contém um valor ARRAY semiestruturado.

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

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

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

• Se col_map for do tipo MAP(VARCHAR, VARCHAR):

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

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

Observe o seguinte:

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

• Ao converter um valor estruturado OBJECT ou MAP em um valor semiestruturado OBJECT ou VARIANT, todos os valores NULL serão convertidos em valores nulos JSON.

  Se você estiver convertendo um valor ARRAY estruturado em um valor 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 valor MAP que usa um tipo NUMBER para chaves, as chaves MAP serão convertidas em cadeias de caracteres no valor OBJECT retornado.

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

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

• Um valor de tipo estruturado pode ser convertido a outro valor de tipo estruturado se os dois tipos básicos forem os mesmos:

  • Um valor ARRAY de um tipo pode ser convertido a um valor ARRAY de outro tipo, desde que o primeiro tipo de elemento possa ser convertido 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:

    • Um valor ARRAY(NUMBER) pode ser convertido para um valor ARRAY(DOUBLE).

    • Um valor ARRAY(DATE) não pode ser convertido para um valor ARRAY(NUMBER).

  • Um valor OBJECT com uma definição de tipo pode ser convertido para um valor OBJECT com outra definição de tipo apenas se todas as seguintes condições forem atendidas:

    • 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.

      Assim como ocorre com os tipos de elementos em valores ARRAY estruturados, você só pode converter o tipo de um valor para outro tipo 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 valor OBJECT(city VARCHAR, zipcode NUMBER) pode ser convertido para um valor OBJECT(city VARCHAR, zipcode DOUBLE).

    • Um valor OBJECT(city VARCHAR, zipcode NUMBER) não pode ser convertido para um valor OBJECT(city VARCHAR, zipcode DATE).

  • Um valor MAP com um tipo de valor pode ser convertido para um valor 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 valor MAP(VARCHAR, NUMBER) pode ser convertido para um valor MAP(VARCHAR, DOUBLE).

  • Um valor MAP com um tipo de chave pode ser convertido para um valor MAP com um tipo de chave diferente se ambos os tipos de chave usarem o mesmo tipo de inteiro NUMERIC que difere apenas na precisão.

    Por exemplo, um valor MAP(VARCHAR, NUMBER) não pode ser convertido para um valor MAP(NUMBER, NUMBER).

• Um valor de tipo estruturado não pode ser coagido a um valor semiestruturado (e vice-versa).

• Um valor VARCHAR não pode ser convertido para um valor de tipo estruturado.

Conversão de um tipo estruturado em outro¶

É possível chamar a função CAST ou usar o operador :: para converter um valor de um tipo estruturado em um valor de outro tipo estruturado. É possível converter valores de e para os seguintes tipos estruturados:

• Para ARRAYs estruturadas:

  É possível converter um valor ARRAY de um tipo em um valor ARRAY de outro tipo.

• Para OBJECTs estruturados:

  • É possível usar uma conversão para alterar a ordem dos pares chave-valor em um valor OBJECT.

  • É possível usar uma conversão para alterar os nomes das chaves em um valor OBJECT.

  • É possível usar uma conversão para adicionar chaves a um valor OBJECT.

  • É possível converter um valor estruturado OBJECT em um valor MAP.

• Para valores MAP:

  • É possível converter um valor MAP com chaves e valores de um tipo específico em um valor MAP com chaves e valores de um tipo diferente.

  • É possível converter um valor MAP em um valor OBJECT estruturado.

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

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

+------------+
| CAST_ARRAY |
|------------|
| [          |
|   "1",     |
|   "2",     |
|   "3"      |
| ]          |
+------------+

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

+-----------------------+
| OBJECT_VALUE_ORDER    |
|-----------------------|
| {                     |
|   "state": "CA",      |
|   "city": "San Mateo" |
| }                     |
+-----------------------+

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

+-----------------------------+
| OBJECT_VALUE_KEY_NAMES      |
|-----------------------------|
| {                           |
|   "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) AS add_fields;

+------------------------+
| ADD_FIELDS             |
|------------------------|
| {                      |
|   "city": "San Mateo", |
|   "state": "CA",       |
|   "zipcode": null      |
| }                      |
+------------------------+

Uso das funções SQL para construir valores estruturados ARRAY e OBJECT¶

As seguintes funções constroem valores semiestruturados ARRAY:

• ARRAY_CONSTRUCT

• ARRAY_CONSTRUCT_COMPACT

• ARRAY_AGG

• TO_ARRAY

As seguintes funções constroem valores semiestruturados OBJECT:

• OBJECT_CONSTRUCT

• OBJECT_CONSTRUCT_KEEP_NULL

• OBJECT_AGG

• TO_OBJECT

Para construir um valor estruturado ARRAY ou OBJECT, 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(
  'oname', 'abc',
  'created_date', '2020-01-18'::DATE
)::OBJECT(
  oname VARCHAR,
  created_date DATE
);

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

Como usar constantes ARRAY e OBJECT para construir valores estruturados ARRAY e OBJECT¶

Ao especificar uma constante ARRAY ou uma constante OBJECT, está especificando um valor semiestruturado ARRAY ou OBJECT.

Para construir um valor estruturado ARRAY ou OBJECT, é necessário converter explicitamente a expressão. Por exemplo:

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

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

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

Construção de um valor MAP¶

Para criar um valor MAP, construa um valor semiestruturado OBJECT e converta o valor OBJECT em um valor MAP.

Por exemplo, as instruções a seguir produzem o valor 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 seguinte instrução produz o valor MAP {-10->'CA',-20->'OR'}:

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

Para obter mais detalhes, consulte Como converter valores OBJECT e VARIANT semiestruturados em valores MAP.

Como obter a lista de chaves de um valor estruturado OBJECT¶

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

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

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

Como obter a lista de chaves de um valor MAP¶

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

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

Acesso a valores e elementos a partir de valores de tipos estruturados¶

É possível usar os seguintes métodos para acessar valores e elementos dos valores estruturados ARRAY, OBJECT e MAP:

• 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 valor estruturado, em vez de VARIANT.

O exemplo a seguir passa o primeiro elemento de um valor ARRAY semiestruturado e um valor 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 valor estruturado OBJECT para a função GET ou GET_IGNORE_CASE, é necessário especificar uma constante para a chave.

  Não é necessário especificar uma constante se você estiver passando um valor MAP ou ARRAY estruturado para a função GET.

  Também não é necessário especificar uma constante se você estiver passando um valor MAP para a função GET_IGNORE_CASE.

• Ao passar um valor OBJECT estruturado, ARRAY estruturado ou MAP para a função GET_PATH, é necessário especificar uma constante para o nome do caminho.

• Para um valor OBJECT estruturado, se você usar uma chave OBJECT ou um caminho inexistente, ocorrerá um erro de tempo de compilação.

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

Determinação do tamanho de um valor ARRAY estruturado¶

Para determinar o tamanho de um valor ARRAY estruturado, passe o valor ARRAY para a função ARRAY_SIZE:

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

Determinação do tamanho de um valor MAP¶

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

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

Consulta de elementos em um valor ARRAY estruturado¶

Para determinar se um elemento está presente em um valor ARRAY estruturado, 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 um valor ARRAY estruturado, chame a função ARRAY_POSITION. Por exemplo:

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

Como determinar se um valor MAP contém uma chave¶

Para determinar se um valor 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 valores estruturados com valores semiestruturados¶

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

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

É possível comparar dois valores do mesmo tipo (por exemplo, dois valores ARRAY estruturados, dois valores OBJECT estruturados ou dois valores MAP).

Atualmente, os seguintes operadores de comparação são compatíveis com a comparação de valores de tipos estruturados:

• =

• !=

• <

• <=

• >=

• >

Ao comparar dois valores estruturados para igualdade, observe o seguinte:

• Se um tipo não puder ser convertido para outro tipo, a comparação falha.

• Ao comparar os valores MAP que têm chaves numéricas, as chaves são comparadas como números (e não como valores VARCHAR).

Ao comparar dois valores estruturados usando <, <=, >= ou >, os campos de valores estruturados são comparados em ordem alfabética. Por exemplo, o valor a seguir:

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

é maior que:

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

Como determinar se dois valores ARRAY se sobrepõem¶

Para determinar se os elementos de dois valores ARRAY estruturados se sobrepõem, chame a função ARRAYS_OVERLAP. Por exemplo:

SELECT ARRAYS_OVERLAP(numeric_array, other_numeric_array);

Os valores ARRAY devem ser de tipos comparáveis.

Você não pode passar um valor ARRAY semiestruturado e um valor ARRAY estruturado para essa função. Ambos os valores ARRAY devem ser estruturados ou semiestruturados.

Transformação de valores ARRAY estruturados¶

Ao passar um valor ARRAY estruturado para essas funções, elas retornam um valor ARRAY estruturado 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 os valores ARRAY estruturados.

• Funções que adicionam elementos aos valores ARRAY

• Funções que aceitam múltiplos valores ARRAY 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 valores OBJECT estruturados¶

As seções a seguir explicam como retornar um valor OBJECT estruturado que foi transformado a partir de outro valor 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

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

Transformação dos valores MAP¶

Para transformar os valores MAP, use as seguintes funções:

• MAP_CAT

• MAP_DELETE

• MAP_INSERT

• MAP_PICK

Uso da função FLATTEN com valores de tipos estruturados¶

É possível passar valores ARRAY, OBJECT e MAP estruturados 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 um valor estruturado ARRAY e o argumento RECURSIVE for FALSE, a coluna value conterá um valor do mesmo tipo que o valor 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 um valor 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 os valores de MAP, a ordem das chaves e dos valores retornados é indeterminada.

Uso da função PARSE_JSON¶

A função PARSE_JSON não retorna tipos estruturados.

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

É possível usar os valores estruturados ARRAY, OBJECT e MAP em:

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

• Expressões CASE.

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

Como trabalhar com outras funções semiestruturadas¶

As seguintes funções não aceitam valores ARRAY estruturados, OBJECT estruturados ou MAP como argumento de entrada:

• AS_ARRAY

• AS_OBJECT

• IS_ARRAY

• IS_OBJECT

• TYPEOF

A passagem de um valor de tipo estruturado como entrada resulta em um erro.

Como usar o 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.

Como usar os 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 tipos estruturados ARRAY, 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 nos tipos estruturados OBJECT e MAP, consulte a exibição FIELDS em INFORMATION_SCHEMA ou a exibição FIELDS em ACCOUNT_USAGE.