Types de données structurés

Un type structuré est un ARRAY, un OBJECT ou une MAP qui contient des éléments ou des paires clé-valeur avec des types de données Snowflake spécifiques. Voici quelques exemples de types structurés :

  • Un ARRAY d’éléments INTEGER.

  • Un OBJECT avec des paires clé-valeur VARCHAR et NUMBER.

  • Une MAP qui associe une clé VARCHAR à une valeur DOUBLE.

Vous pouvez utiliser les types structurés des manières suivantes :

  • Vous pouvez définir une colonne de type structuré dans une table Iceberg.

    Les types de données Iceberg list, struct et map correspondent aux types de ARRAY structuré, d’OBJECT structuré et de MAP dans Snowflake.

    Une colonne de type structuré prend en charge un maximum de 1 000 sous-colonnes.

  • Les types structurés sont utilisés pour accéder aux données d’une colonne de type structuré dans une table Iceberg.

  • Vous pouvez transformer un type semi-structuré ARRAY, OBJECT ou VARIANT en un type structuré correspondant (par exemple, un ARRAY en un ARRAY d’éléments INTEGER). Vous pouvez également obtenir un type structuré d’un type semi-structuré.

Note

Actuellement, les tables autres que les tables Iceberg ne prennent pas en charge les types structurés. Vous ne pouvez pas ajouter une colonne d’un type structuré à une table standard.

Cette rubrique explique comment utiliser les types structurés dans Snowflake.

Spécification d’un type structuré

Lors de la définition d’une colonne de type structuré ou de la conversion d’une valeur en un type structuré, utilisez la syntaxe décrite dans les sections suivantes :

Spécification d’un ARRAY structuré

Pour spécifier un ARRAY structuré, utilisez la syntaxe suivante :

ARRAY( <element_type> [ NOT NULL ] )
Copy

Où :

  • element_type est le type de données Snowflake des éléments de ce ARRAY.

    Vous pouvez également spécifier un ARRAY structuré, un OBJECT structuré ou un MAP comme type d’élément.

    Note

    Dans la définition d’une colonne de table Iceberg, vous ne pouvez pas spécifier un VARIANT, un ARRAY semi-structuré ou un OBJECT semi-structuré comme type de l’élément ARRAY.

  • NOT NULL spécifie que le ARRAY ne peut contenir aucun élément qui soit NULL.

Par exemple, comparez les types renvoyés par la fonction SYSTEM$TYPEOF dans l’instruction suivante :

  • La première expression de colonne transforme un ARRAY semi-structuré en un ARRAY structuré (un ARRAY de NUMBER éléments).

  • La deuxième expression de colonne spécifie un ARRAY semi-structuré.

SELECT
  SYSTEM$TYPEOF(
    [1, 2, 3]::ARRAY(NUMBER)
  ) AS structured_array,
  SYSTEM$TYPEOF(
    [1, 2, 3]
  ) AS semi_structured_array;
Copy
+-------------------------------+-----------------------+
| STRUCTURED_ARRAY              | SEMI_STRUCTURED_ARRAY |
|-------------------------------+-----------------------|
| ARRAY(NUMBER(38,0))[LOB]      | ARRAY[LOB]            |
+-------------------------------+-----------------------+

Spécification d’un OBJECT structuré

Pour spécifier un OBJECT structuré, utilisez la syntaxe suivante :

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

Où :

  • key spécifie une clé pour l’OBJECT.

    • Chaque key d’une définition d’objet doit être unique.

    • L’ordre des clés fait partie de la définition d’objet. Il n’est pas autorisé de comparer deux OBJECTs qui ont les mêmes clés dans un ordre différent. (Il se produit une erreur de temps de compilation.)

    • Si vous ne spécifiez aucune clé, mais que vous spécifiez les parenthèses (c’est-à-dire si vous utilisez OBJECT()), le type obtenu est un OBJECT structuré qui ne contient aucune clé. Un OBJECT structuré sans clé est différent d’un OBJECT semi-structuré.

  • value_type est le type de données Snowflake de la valeur correspondant à la clé.

    Vous pouvez également spécifier un ARRAY structuré, un OBJECT structuré ou un MAP comme type de valeur.

    Note

    Dans la définition d’une colonne de table Iceberg, vous ne pouvez pas spécifier un VARIANT, un ARRAY semi-structuré ou un OBJECT semi-structuré comme type de la valeur correspondant à la clé OBJECT.

  • NOT NULL spécifie que la valeur correspondant à la clé ne peut pas être NULL.

Par exemple, comparez les types renvoyés par la fonction SYSTEM$TYPEOF dans l’instruction suivante :

  • La première expression de colonne transforme un OBJECT semi-structuré en un OBJECT structuré qui contient les clés et les valeurs suivantes :

    • Une clé nommée str avec une valeur VARCHAR qui n’est pas NULL.

    • Une clé nommée num avec une valeur NUMBER.

  • La deuxième expression de colonne spécifie un OBJECT semi-structuré.

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;
Copy
+----------------------------------------------------------------+------------------------+
| STRUCTURED_OBJECT                                              | SEMI_STRUCTURED_OBJECT |
|----------------------------------------------------------------+------------------------|
| OBJECT(str VARCHAR(16777216), num NUMBER(38,0) NOT NULL )[LOB] | OBJECT[LOB]            |
+----------------------------------------------------------------+------------------------+

Spécification d’une MAP

Pour spécifier une MAP, utilisez la syntaxe suivante :

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

Où :

  • key_type est le type de données Snowflake de la clé de la carte. Vous devez utiliser un des types suivants pour les clés :

    • VARCHAR

    • NUMBER avec l’échelle 0

    Vous ne pouvez pas utiliser un type de données à virgule flottante comme type de clé.

    Notez que les clés de carte ne peuvent pas être NULL.

  • value_type est le type de données Snowflake des valeurs de map.

    Vous pouvez également spécifier un ARRAY structuré, un OBJECT structuré ou un MAP comme type de valeurs.

    Note

    Dans la définition d’une colonne de table Iceberg, vous ne pouvez pas spécifier un VARIANT, un ARRAY semi-structuré ou un OBJECT semi-structuré comme type de valeur dans le MAP.

  • NOT NULL spécifie que la valeur correspondant à la clé ne peut pas être NULL. NOT NULL spécifie que la valeur ne peut pas être NULL.

L’exemple suivant transforme un OBJECT semi-structuré en une MAP et utilise la fonction SYSTEM$TYPEOF pour imprimer le type obtenu de l’objet. La MAP associe les clés VARCHAR à des valeurs VARCHAR.

SELECT
  SYSTEM$TYPEOF(
    {
      'a_key': 'a_val',
      'b_key': 'b_val'
    }::MAP(VARCHAR, VARCHAR)
  ) AS map_example;
Copy
+------------------------------------------------+
| MAP_EXAMPLE                                    |
|------------------------------------------------|
| map(VARCHAR(16777216), VARCHAR(16777216))[LOB] |
+------------------------------------------------+

Utilisation de types structurés dans des types semi-structurés

Vous ne pouvez pas utiliser un MAP, un OBJECT structuré ou un ARRAY structuré dans une VARIANT, un OBJECT semi-structuré ou un ARRAY semi-structuré. Une erreur se produit dans les situations suivantes :

Conversion de types structurés et semi-structurés

Le tableau suivant résume les règles de conversion d’OBJECTs structurés, de ARRAYs structurés et de MAPs en OBJECTs, ARRAYs et VARIANTs semi-structurés (et inversement).

Type de données source

Type de données cible

Convertible

Coercible (Peut être contraint à se transformer)

ARRAY semi-structuré

ARRAY structuré

OBJECT semi-structuré

  • OBJECT structuré

  • MAP

VARIANT semi-structurée

  • ARRAY structuré

  • OBJECT structuré

  • MAP

ARRAY structuré

ARRAY semi-structuré

  • OBJECT structuré

  • MAP

OBJECT semi-structuré

  • ARRAY structuré

  • OBJECT structuré

  • MAP

VARIANT semi-structurée

Les sections suivantes expliquent ces règles plus en détail.

Conversion explicite d’un type semi-structuré en un type structuré

Pour convertir explicitement un objet semi-structuré en un type structuré, vous pouvez appeler la fonction CAST ou utiliser l’opérateur ::.

Note

TRY_CAST n’est pas pris en charge pour les types structurés.

Vous pouvez uniquement convertir les objets semi-structurés suivants dans le type structuré correspondant ; sinon, il se produit une erreur d’exécution.

Type semi-structuré

Type structuré que vous pouvez convertir en

ARRAY

ARRAY structuré

OBJECT

MAP ou OBJECT structuré

VARIANT

MAP ou ARRAY ou OBJECT structuré

Les sections suivantes fournissent plus de détails sur la façon dont les types sont convertis :

Conversion de ARRAYs et de VARIANTs semi-structurés en ARRAYs structurés

Les étapes suivantes montrent comment transformer un ARRAY ou une VARIANT semi-structuré(e) en un ARRAY de NUMBER éléments :

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;
Copy

Ou :

SELECT
  SYSTEM$TYPEOF(
    [1,2,3]::ARRAY(NUMBER)
  ) AS array_cast_type,
  SYSTEM$TYPEOF(
    [1,2,3]::VARIANT::ARRAY(NUMBER)
  ) AS variant_cast_type;
Copy
+--------------------------+--------------------------+
| ARRAY_CAST_TYPE          | VARIANT_CAST_TYPE        |
|--------------------------+--------------------------|
| ARRAY(NUMBER(38,0))[LOB] | ARRAY(NUMBER(38,0))[LOB] |
+--------------------------+--------------------------+

Lorsque vous transformez un ARRAY ou une VARIANT semi-structuré(e) en ARRAY structuré, notez les points suivants :

  • Chaque élément du ARRAY est converti dans le type spécifié du ARRAY.

    La transformation de la colonne de ARRAY en ARRAY(VARCHAR) convertit chaque valeur en VARCHAR :

    SELECT
      CAST ([1,2,3] AS ARRAY(VARCHAR)) AS varchar_array,
      SYSTEM$TYPEOF(varchar_array) AS array_cast_type;
    
    Copy
    +---------------+-------------------------------+
    | VARCHAR_ARRAY | ARRAY_CAST_TYPE               |
    |---------------+-------------------------------|
    | [             | ARRAY(VARCHAR(16777216))[LOB] |
    |   "1",        |                               |
    |   "2",        |                               |
    |   "3"         |                               |
    | ]             |                               |
    +---------------+-------------------------------+
    
  • Si l’élément ne peut pas être converti dans le type spécifié (par exemple, conversion de ['a', 'b', 'c'] en ARRAY(NUMBER)), la conversion échoue.

  • Si le ARRAY contient des éléments NULL et que le type de ARRAY spécifie NOT NULL (par exemple, conversion de [1, NULL, 3] en ARRAY(NUMBER NOT NULL), la conversion échoue.

  • Les éléments qui sont des valeurs JSON nulles sont convertis en NULL si le type d’élément cible ne prend pas en charge les valeurs JSON nulles (c’est-à-dire si le type cible n’est pas un ARRAY, un OBJECT ou une VARIANT semi-structuré(e)).

    Par exemple, si vous effectuez une conversion vers ARRAY(NUMBER), les valeurs JSON nulles sont converties en NULL, car NUMBER ne prend pas en charge les valeurs JSON nulles.

    En revanche, si vous effectuez une conversion vers ARRAY(VARIANT), les valeurs JSON nulles ne sont pas converties en NULL, car VARIANT prend en charge les valeurs JSON nulles.

Conversion d’OBJECTs et de VARIANTs semi-structurés en OBJECTs structurés

Les étapes suivantes montrent comment transformer un OBJECT ou une VARIANT semi-structuré(e) en un OBJECT structuré contenant les paires clé-valeur city et state (qui sont des valeurs VARCHAR) :

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;
Copy

Ou :

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;
Copy
+--------------------------------------------------------------+--------------------------------------------------------------+
| OBJECT_CAST_TYPE                                             | VARIANT_CAST_TYPE                                            |
|--------------------------------------------------------------+--------------------------------------------------------------|
| OBJECT(city VARCHAR(16777216), state VARCHAR(16777216))[LOB] | OBJECT(city VARCHAR(16777216), state VARCHAR(16777216))[LOB] |
+--------------------------------------------------------------+--------------------------------------------------------------+

Lorsque vous transformez un OBJECT ou une VARIANT semi-structuré(e) en OBJECT structuré, notez les points suivants :

  • L’OBJECT ne doit pas contenir de clés supplémentaires non spécifiées dans le type OBJECT.

    En cas de clés supplémentaires, la conversion échoue.

  • S’il manque à l’OBJECT une clé spécifiée dans le type OBJECT, la conversion échoue.

  • La valeur de chaque clé de l’OBJECT est convertie dans le type spécifié pour cette clé.

    Si une valeur ne peut être convertie dans le type spécifié, la conversion échoue.

  • Si la valeur d’une clé est une valeur JSON nulle, la valeur est convertie en NULL, si le type de valeur cible ne prend pas en charge les valeurs JSON nulles (c’est-à-dire si le type cible n’est pas un ARRAY, un OBJECT ou une VARIANT semi-structuré(e)).

    Par exemple, si vous effectuez une conversion vers OBJECT(ville VARCHAR), les valeurs JSON nulles sont converties en NULL, car VARCHAR ne prend pas en charge les valeurs JSON nulles.

    En revanche, si vous effectuez une conversion vers OBJECT(ville VARIANT), les valeurs JSON nulles ne sont pas converties en NULL, car VARIANT prend en charge les valeurs JSON nulles.

Conversion d’OBJECTs et de VARIANTs semi-structurés en MAPs

Les instructions suivantes montrent comment transformer un OBJECT ou une VARIANT semi-structuré(e) en une MAP qui associe une clé VARCHAR à une valeur VARCHAR :

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;
Copy

Ou :

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;
Copy
+------------------------------------------------+------------------------------------------------+
| MAP_CAST_TYPE                                  | VARIANT_CAST_TYPE                              |
|------------------------------------------------+------------------------------------------------|
| map(VARCHAR(16777216), VARCHAR(16777216))[LOB] | map(VARCHAR(16777216), VARCHAR(16777216))[LOB] |
+------------------------------------------------+------------------------------------------------+

Lorsque vous transformez un OBJECT ou une VARIANT semi-structuré(e) en MAP, notez les points suivants :

  • Si les clés et les valeurs ne correspondent pas aux types spécifiés, elles sont converties dans les types spécifiés.

  • Si les clés et les valeurs ne peuvent pas être converties dans les types spécifiés, la conversion échoue.

  • Si la valeur d’une clé est une valeur JSON nulle, la valeur est convertie en NULL, si le type de valeur cible ne prend pas en charge les valeurs JSON nulles (c’est-à-dire si le type cible n’est pas un ARRAY, un OBJECT ou une VARIANT semi-structuré(e)).

    Par exemple, si vous effectuez une conversion vers MAP(VARCHAR, VARCHAR), les valeurs JSON nulles sont converties en NULL, car VARCHAR ne prend pas en charge les valeurs JSON nulles.

    En revanche, si vous effectuez une conversion vers MAP(VARCHAR, VARIANT), les valeurs JSON nulles ne sont pas converties en NULL, car VARIANT prend en charge les valeurs JSON nulles.

Conversion explicite d’un type structuré en un type semi-structuré

Pour convertir explicitement un type structuré en un type semi-structuré, vous pouvez appeler la fonction CAST, utiliser l’opérateur :: ou appeler l’une des fonctions de conversion (par exemple, TO_ARRAY, TO_OBJECT ou TO_VARIANT).

Note

TRY_CAST n’est pas pris en charge avec les types structurés.

Type structuré

Type semi-structuré que vous pouvez convertir en

ARRAY structuré

ARRAY

MAP ou OBJECT structuré

OBJECT

MAP, ARRAY structuré ou OBJECT structuré

VARIANT

Par exemple :

  • Si col_structured_array est ARRAY(VARCHAR) :

    • CAST(tableau_structuré_col AS ARRAY) renvoie un ARRAY semi-structuré.

    • CAST(tableau_structuré_col AS VARIANT) renvoie une VARIANT contenant un ARRAY semi-structuré.

  • Si col_structured_object est OBJECT(nom VARCHAR, état VARCHAR) :

    • CAST(objet_structuré_col AS OBJECT) renvoie un OBJECT semi-structuré.

    • CAST(objet_structuré_col AS VARIANT) renvoie une VARIANT contenant un OBJECT semi-structuré.

  • Si col_map est MAP(VARCHAR, VARCHAR) :

    • CAST(carte_col AS OBJECT) renvoie un OBJECT semi-structuré.

    • CAST(carte_col AS VARIANT) renvoie une VARIANT contenant un OBJECT semi-structuré.

Remarques :

  • Lorsque vous effectuez une conversion vers un OBJECT semi-structuré, l’ordre des clés de l’OBJECT structuré n’est pas préservé.

  • Lorsque vous transformez un OBJECT structuré ou une MAP en OBJECT ou en VARIANT semi-structuré(e), toutes les valeurs NULL sont converties en valeurs JSON nulles.

    Si vous transformez un ARRAY structuré en VARIANT, les valeurs NULL sont conservées telles quelles.

    SELECT [1,2,NULL,3]::ARRAY(INTEGER)::VARIANT;
    
    Copy
    +---------------------------------------+
    | [1,2,NULL,3]::ARRAY(INTEGER)::VARIANT |
    |---------------------------------------|
    | [                                     |
    |   1,                                  |
    |   2,                                  |
    |   undefined,                          |
    |   3                                   |
    | ]                                     |
    +---------------------------------------+
    
  • Si vous transformez une MAP qui utilise un type NUMBER pour les clés, les clés de la MAP sont converties en chaînes dans l’OBJECT renvoyé.

Casting (Conversion) implicite d’une valeur (coercition ou conversion contrainte)

Les règles suivantes s’appliquent au casting (à la conversion) implicite (coercition ou conversion contrainte) d’un type structuré en un autre type structuré :

  • Un type structuré peut être contraint à se transformer en un autre type structuré si les deux types d’objet de base sont identiques :

    • Un ARRAY d’un type peut être contraint à se transformer en un ARRAY d’un autre type, à condition que le premier type d’élément puisse être contraint à se convertir dans le deuxième type d’élément.

      Un type d’élément peut être contraint à se transformer en un autre type d’élément dans l’un ou l’autre des cas suivants :

      • Les deux types sont numériques. Les cas suivants sont pris en charge :

        • Les deux utilisent le même type de numérique, mais peuvent varier en termes de précision et/ou d’échelle.

        • Contrainte de transformation d’un NUMBER en FLOAT (et inversement).

      • Les deux types sont des horodatages. Les cas suivants sont pris en charge :

        • Les deux utilisent le même type, mais peuvent varier en termes de précision.

        • Contrainte de transformation d’un TIMESTAMP_LTZ en TIMESTAMP_TZ (et inversement).

      Par exemple :

      • Un ARRAY(NUMBER) peut être contraint à se transformer en ARRAY(DOUBLE).

      • Un ARRAY(DATE) ne peut pas être contraint à se transformer en ARRAY(NUMBER).

    • Un OBJECT avec une définition de type peut être contraint à se transformer en un OBJECT avec une autre définition de type uniquement si toutes les conditions suivantes sont remplies :

      • Les deux types d’OBJECT ont le même nombre de clés.

      • Les deux types d’OBJECT utilisent les mêmes noms pour les clés.

      • Les clés des deux types d’OBJECT sont dans le même ordre.

      • Le type de chaque valeur d’un type d’OBJECT peut être contraint à se transformer dans le type de la valeur correspondante dans l’autre type d’OBJECT.

        Comme c’est le cas pour les types d’élément des ARRAYs structurés, vous pouvez contraindre le type d’une valeur à se transformer en un autre type uniquement si :

        • Les deux types sont numériques. Les cas suivants sont pris en charge :

          • Les deux utilisent le même type de numérique, mais peuvent varier en termes de précision et/ou d’échelle.

          • Contrainte de transformation d’un NUMBER en FLOAT (et inversement).

        • Les deux types sont des horodatages. Les cas suivants sont pris en charge :

          • Les deux utilisent le même type, mais peuvent varier en termes de précision.

          • Contrainte de transformation d’un TIMESTAMP_LTZ en TIMESTAMP_TZ (et inversement).

      Par exemple :

      • Un OBJECT(ville VARCHAR, code postal NUMBER) peut être contraint à se transformer en un OBJECT(ville VARCHAR, code postal DOUBLE).

      • Un OBJECT(ville VARCHAR, code postal NUMBER) ne peut pas être contraint à se transformer en OBJECT(ville VARCHAR, code postal DATE).

    • Une MAP avec un type de valeur peut être contraint à se transformer en MAP avec un type de valeur différent si :

      • Les deux types de valeur sont numériques. Les cas suivants sont pris en charge :

        • Les deux utilisent le même type de numérique, mais peuvent varier en termes de précision et/ou d’échelle.

        • Contrainte de transformation d’un NUMBER en FLOAT (et inversement).

      • Les deux types de valeur sont des horodatages. Les cas suivants sont pris en charge :

        • Les deux utilisent le même type, mais peuvent varier en termes de précision.

        • Contrainte de transformation d’un TIMESTAMP_LTZ en TIMESTAMP_TZ (et inversement).

      Par exemple, une MAP(VARCHAR, NUMBER) peut être contrainte à se transformer en MAP(VARCHAR, DOUBLE).

    • Une MAP avec un type de clé peut être contrainte à se transformer en MAP avec un type de clé différent si les deux types de clé utilisent le même type d’entier NUMERIC qui ne varie qu’en termes de précision.

      Par exemple, une MAP(VARCHAR, NUMBER) ne peut pas être contrainte à se transformer en MAP(NUMBER, NUMBER).

  • Un type structuré ne peut pas être contraint à se transformer en objet semi-structuré (et inversement).

  • Une valeur VARCHAR ne peut pas être contrainte à se transformer en un type structuré.

Conversion d’un type structuré en un autre

Vous pouvez appeler la fonction CAST ou utiliser l’opérateur :: pour convertir un type structuré en un autre. Vous pouvez effectuer des conversions depuis et vers les types structurés suivants :

Note

TRY_CAST n’est pas pris en charge avec les types structurés.

S’il n’est pas possible de convertir les valeurs d’un type à l’autre, la conversion échoue. Par exemple, la tentative de transformation d’un ARRAY(BOOLEAN) en un ARRAY(DATE) échoue.

Exemple : Conversion d’un type de ARRAY en un autre

L’exemple suivant transforme un ARRAY(NUMBER) en ARRAY(VARCHAR) :

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

Exemple : Modification de l’ordre des paires clé-valeur d’un OBJECT

L’exemple suivant modifie l’ordre des paires clé-valeur d’un OBJECT structuré :

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

Exemple : Modification des noms de clé d’un OBJECT

Pour modifier les noms de clé d’un OBJECT structuré, spécifiez les mots-clés RENAME FIELDS à la fin de CAST. Par exemple :

SELECT CAST({'city':'San Mateo','state': 'CA'}::OBJECT(city VARCHAR, state VARCHAR)
  AS OBJECT(city_name VARCHAR, state_name VARCHAR) RENAME FIELDS);
Copy
+------------------------------------------------------------------------------+
| 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"                                                         |
| }                                                                            |
+------------------------------------------------------------------------------+

Exemple : Ajout de clés à un OBJECT

Si le type vers lequel vous effectuez la conversion comporte des paires clé-valeur supplémentaires qui ne sont pas présentes dans l’objet structuré d’origine, spécifiez les mots-clés ADD FIELDS à la fin de CAST. Par exemple :

SELECT CAST({'city':'San Mateo','state': 'CA'}::OBJECT(city VARCHAR, state VARCHAR)
  AS OBJECT(city VARCHAR, state VARCHAR, zipcode NUMBER) ADD FIELDS);
Copy
+------------------------------------------------------------------------------+
| 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                                                            |
| }                                                                            |
+------------------------------------------------------------------------------+

Les valeurs des nouvelles clés ajoutées seront définies sur NULL. Si vous souhaitez attribuer une valeur à ces clés, appelez plutôt la fonction OBJECT_INSERT.

Construction de ARRAYs structurés, d’OBJECTs structurés et de MAPs

Les sections suivantes expliquent comment construire des ARRAYs structurés, des OBJECTs structurés et des MAPs.

Utilisation de fonctions SQL pour construire des ARRAYs et des OBJECTs structurés

Les fonctions suivantes permettent de construire des ARRAYs semi-structurés :

Les fonctions suivantes permettent de construire des OBJECTs semi-structurés :

Pour construire un ARRAY ou un OBJECT structuré, utilisez ces fonctions et convertissez explicitement la valeur renvoyée de la fonction. Par exemple :

SELECT ARRAY_CONSTRUCT(10, 20, 30)::ARRAY(NUMBER);
Copy
SELECT OBJECT_CONSTRUCT(
  'name', 'abc',
  'created_date', '2020-01-18'::DATE
)::OBJECT(
  name VARCHAR,
  created_date DATE
);
Copy

Pour plus de détails, reportez-vous à Conversion explicite d’un type semi-structuré en un type structuré.

Note

Il n’est pas possible de transmettre des ARRAYs structurés, des OBJECTs structurés ou des MAPs à ces fonctions. Si vous le faisiez, cela entraînerait le casting (la conversion) implicite d’un type structuré en type semi-structuré, ce qui n’est pas autorisé (comme indiqué à la section Casting (Conversion) implicite d’une valeur (coercition ou conversion contrainte)).

Utilisation des constantes ARRAY et OBJECT pour construire des ARRAYs et des OBJECTs structurés

Lorsque vous spécifiez une constante ARRAY ou une constante OBJECT, vous spécifiez un ARRAY ou un OBJECT semi-structuré.

Pour construire un ARRAY ou un OBJECT structuré, vous devez explicitement convertir l’expression. Par exemple :

SELECT [10, 20, 30]::ARRAY(NUMBER);
Copy
SELECT {
  'name': 'abc',
  'created_date': '2020-01-18'::DATE
}::OBJECT(
  name VARCHAR,
  created_date DATE
);
Copy

Pour plus de détails, reportez-vous à Conversion explicite d’un type semi-structuré en un type structuré.

Construire un MAP

Pour construire une MAP, construisez un objet semi-structuré et transformez l’OBJECT en MAP.

Par exemple, les instructions suivantes produisent toutes les deux la MAP {'city'->'San Mateo','state'->'CA'} :

SELECT OBJECT_CONSTRUCT(
  'city', 'San Mateo',
  'state', 'CA'
)::MAP(
  VARCHAR,
  VARCHAR
);
Copy
SELECT {
  'city': 'San Mateo',
  'state': 'CA'
}::MAP(
  VARCHAR,
  VARCHAR
);
Copy

L’instruction suivante produit la MAP {-10->'CA',-20->'OR'} :

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

Pour plus de détails, reportez-vous à Conversion d’OBJECTs et de VARIANTs semi-structurés en MAPs.

Utilisation de clés, de valeurs et d’éléments dans des types structurés

Les sections suivantes expliquent comment utiliser des valeurs et des éléments dans des types structurés.

Obtention de la liste de clés à partir d’un OBJECT structuré

Pour obtenir la liste de clés d’un OBJECT structuré, appelez la fonction OBJECT_KEYS :

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

Si la valeur d’entrée est un OBJECT structuré, la fonction renvoie un ARRAY(VARCHAR) contenant les clés. Si la valeur d’entrée est un OBJECT semi-structuré, la fonction renvoie un ARRAY.

Obtention de la liste de clés d’une MAP

Pour obtenir la liste de clés d’une MAP, appelez la fonction MAP_KEYS :

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

Accès aux valeurs et aux éléments des types structurés

Vous pouvez utiliser les méthodes suivantes pour accéder aux valeurs et aux éléments de ARRAYs structurés, d’OBJECTs structurés et de MAPs :

Les valeurs et les éléments renvoyés ont le type spécifié pour l’objet, au lieu de VARIANT.

L’exemple suivant transmet le premier élément d’un ARRAY semi-structuré et d’un ARRAY(VARCHAR) à la fonction SYSTEM$TYPEOF pour qu’elle renvoie le type de données de cet élément :

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;
Copy
+-------------------------------+-----------------------------+
| SEMI_STRUCTURED_ARRAY_ELEMENT | STRUCTURED_ARRAY_ELEMENT    |
|-------------------------------+-----------------------------|
| VARIANT[LOB]                  | VARCHAR(16777216)[LOB]      |
+-------------------------------+-----------------------------+

Remarques :

  • Lorsque vous transmettez un OBJECT structuré à la fonction GET ou GET_IGNORE_CASE, vous devez spécifier une constante pour la clé.

    Il n’est pas nécessaire de spécifier une constante si vous transmettez une MAP ou un ARRAY structuré à la fonction GET.

    Il n’est pas non plus nécessaire de spécifier une constante si vous transmettez une MAP à la fonction GET_IGNORE_CASE.

  • Lorsque vous transmettez un OBJECT structuré, un ARRAY structuré ou une MAP à la fonction GET_PATH, vous devez spécifier une constante pour le nom de chemin d’accès.

  • Pour un objet structuré, si vous utilisez une clé d’OBJECT ou un chemin d’accès qui n’existe pas, il se produit une erreur de temps de compilation.

    En revanche, lorsque vous utilisez un index, une clé ou un chemin d’accès qui n’existe pas avec un objet semi-structuré, la fonction renvoie NULL.

Détermination de la taille d’un ARRAY structuré

Pour déterminer la taille d’un ARRAY structuré, transmettez le ARRAY à la fonction ARRAY_SIZE :

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

Détermination de la taille d’une MAP

Pour déterminer la taille d’une MAP, transmettez la MAP à la fonction MAP_SIZE :

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

Recherche d’éléments dans un ARRAY structuré

Pour déterminer si un élément est présent dans un ARRAY structuré, appelez la fonction ARRAY_CONTAINS. Par exemple :

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

Pour déterminer la position d’un élément dans un ARRAY structuré, appelez la fonction ARRAY_POSITION. Par exemple :

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

Note

Pour les deux fonctions, utilisez un élément d”un type comparable au type du ARRAY.

Ne convertissez pas l’expression de l’élément en VARIANT.

Détermination si une MAP contient une clé

Pour déterminer si une MAP contient une clé, appelez la fonction MAP_CONTAINS_KEY :

Par exemple :

SELECT MAP_CONTAINS_KEY('key_to_find', my_map);
Copy
SELECT MAP_CONTAINS_KEY(10, my_map);
Copy

Comparaison d’objets

Les sections suivantes expliquent comment comparer des objets :

Comparaison d’objets structurés à des objets semi-structurés

Vous ne pouvez pas comparer un ARRAY structuré, un OBJECT structuré ou une MAP à un ARRAY, un OBJECT ou une VARIANT semi-structuré(e).

Comparaison d’objets structurés à d’autres objets structurés

Vous pouvez comparer deux objets du même type (par exemple, deux ARRAYs structurés, deux OBJECTs structurés ou deux MAPs).

Actuellement, les opérateurs de comparaison suivants sont pris en charge pour comparer les types structurés :

  • =

  • !=

  • <

  • <=

  • >=

  • >

Lorsque vous comparez deux objets structurés pour voir dans quelle mesure ils sont égaux, notez les points suivants :

  • Si un type ne peut pas être contraint à se transformer dans l’autre type, la comparaison échoue.

  • Lorsque vous comparez des MAPs qui ont des clés numériques, les clés sont comparées en tant que nombres (et non en tant que valeurs VARCHAR).

Lorsque vous comparez deux objets structurés via <, <=, >= ou >, les champs d’objet structuré sont comparés dans l’ordre alphabétique. Par exemple, l’objet suivant :

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

est supérieur à :

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

Détermination si deux ARRAYs se chevauchent

Si vous devez déterminer si les éléments de deux ARRAYs structurés se chevauchent, appelez la fonction ARRAYS_OVERLAP. Par exemple :

SELECT ARRAYS_OVERLAP(numeric_array, other_numeric_array);
Copy

Les ARRAYs doivent être de types comparables.

Notez que vous ne pouvez pas transmettre un ARRAY semi-structuré et un ARRAY structuré à cette fonction. Les deux ARRAYs doivent être structurés ou semi-structurés.

Transformation de types structurés

Les sections suivantes expliquent comment transformer des ARRAYs structurés, des OBJECTs structurés et des MAPs :

Transformation de ARRAYs structurés

Lorsque l’on transmet un ARRAY structuré à ces fonctions, celles-ci renvoient un ARRAY structuré du même type :

Les sections suivantes expliquent comment ces fonctions fonctionnent avec les ARRAYs structurés.

Fonctions qui ajoutent des éléments à des tableaux

Les fonctions suivantes ajoutent des éléments à un ARRAY :

Pour ces fonctions, le type de l’élément doit pouvoir être contraint à se transformer dans le type du ARRAY.

Par exemple, l’appel suivant fonctionne, parce que NUMBER peut être contraint à se transformer en DOUBLE (le type du ARRAY) :

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

L’appel suivant fonctionne, parce que VARCHAR peut être contraint à se transformer en DOUBLE :

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

L’appel suivant échoue, car DATE ne peut pas être contraint à se transformer en NUMBER :

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

Fonctions acceptant plusieurs ARRAYs en entrée

Les fonctions suivantes acceptent plusieurs ARRAYs comme arguments d’entrée :

Lorsque vous appelez ces fonctions, les deux arguments doivent être des ARRAYs structurés ou des ARRAYs semi-structurés. Par exemple, les appels suivants échouent, parce que l’un des arguments est un ARRAY structuré et l’autre un ARRAY semi-structuré :

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

La fonction ARRAY_EXCEPT renvoie un ARRAY du même type que le ARRAY du premier argument.

Les fonctions ARRAY_CAT et ARRAY_INTERSECTION renvoient un ARRAY d’un type qui peut prendre en charge les deux types d’entrée.

Par exemple, l’appel suivant à ARRAY_CAT transmet deux ARRAYs structurés :

  • Le premier ARRAY structuré n’autorise pas les valeurs NULLs et contient NUMBER valeurs avec l’échelle de 0. (NUMBER est NUMBER(38, 0).)

  • Le deuxième ARRAY structuré contient une valeur NULL et un nombre qui a l’échelle de 1.

Le ARRAY renvoyé par ARRAY_CAT accepte les valeurs NULLs et contient NUMBER valeurs avec l’échelle de 1.

SELECT
  ARRAY_CAT(
    [1, 2, 3]::ARRAY(NUMBER NOT NULL),
    [5.5, NULL]::ARRAY(NUMBER(2, 1))
  ) AS concatenated_array,
  SYSTEM$TYPEOF(concatenated_array);
Copy
+--------------------+-----------------------------------+
| CONCATENATED_ARRAY | SYSTEM$TYPEOF(CONCATENATED_ARRAY) |
|--------------------+-----------------------------------|
| [                  | ARRAY(NUMBER(38,1))[LOB]          |
|   1,               |                                   |
|   2,               |                                   |
|   3,               |                                   |
|   5.5,             |                                   |
|   undefined        |                                   |
| ]                  |                                   |
+--------------------+-----------------------------------+

Pour la fonction ARRAY_CAT, le ARRAY du deuxième argument doit pouvoir être contraint de se transformer dans le type du premier argument.

Pour les fonctions ARRAY_EXCEPT et ARRAY_INTERSECTION, le ARRAY du deuxième argument doit être comparable au ARRAY du premier argument.

Par exemple, l’appel suivant fonctionne, parce que ARRAY(NUMBER) est comparable à ARRAY(DOUBLE) :

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

L’appel suivant échoue, parce que ARRAY(NUMBER) n’est pas comparable à ARRAY(VARCHAR) :

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

Transformation d’OBJECTs structurés

Les sections suivantes expliquent comment renvoyer un OBJECT structuré qui a été transformé à partir d’un autre OBJECT :

Si vous devez modifier l’ordre des paires clé-valeur, renommer des clés ou ajouter des clés sans spécifier de valeurs, utilisez la fonction CAST ou l’opérateur ::. Pour plus de détails, voir Conversion d’un type structuré en un autre.

Suppression de paires clé-valeur

Pour renvoyer un nouvel objet contenant les paires clé-valeur d’un objet existant dont certaines paires clé-valeur ont été supprimées, appelez la fonction OBJECT_DELETE.

Lors de l’appel de cette fonction, notez les points suivants :

  • Pour les arguments qui sont des clés, vous devez spécifier des constantes.

  • Si la clé spécifiée ne fait pas partie de la définition de type OBJECT, l’appel échoue. Par exemple, l’appel suivant échoue, car l’OBJECT ne contient pas la clé spécifiée zip_code :

    SELECT OBJECT_DELETE( {'city':'San Mateo','state':'CA'}::OBJECT(city VARCHAR,state VARCHAR), 'zip_code' );
    
    Copy
    093201 (23001): Function OBJECT_DELETE: expected structured object to contain field zip_code but it did not.
    
  • La fonction renvoie un OBJECT structuré. Le type de l’OBJECT exclut la clé supprimée. Supposons, par exemple, que vous supprimiez la clé city :

    SELECT
      OBJECT_DELETE(
        {'city':'San Mateo','state':'CA'}::OBJECT(city VARCHAR,state VARCHAR),
        'city'
      ) AS new_object,
      SYSTEM$TYPE_OF(new_object);
    
    Copy

    La fonction renvoie un OBJECT du type OBJECT(state VARCHAR), qui ne comprend pas la clé city.

    +-----------------+--------------------------------------+
    | NEW_OBJECT      | SYSTEM$TYPEOF(NEW_OBJECT)            |
    |-----------------+--------------------------------------|
    | {               | OBJECT(state VARCHAR(16777216))[LOB] |
    |   "state": "CA" |                                      |
    | }               |                                      |
    +-----------------+--------------------------------------+
    
  • Si la fonction supprime toutes les clés de l’objet, elle renvoie un OBJECT structuré vide du type OBJECT().

    SELECT
      OBJECT_DELETE(
        {'state':'CA'}::OBJECT(state VARCHAR),
        'state'
      ) AS new_object,
      SYSTEM$TYPEOF(new_object);
    
    Copy
    +------------+---------------------------+
    | NEW_OBJECT | SYSTEM$TYPEOF(NEW_OBJECT) |
    |------------+---------------------------|
    | {}         | OBJECT()[LOB]             |
    +------------+---------------------------+
    

    Lorsque le type d’un OBJECT structuré comprend des paires clé-valeur, les noms et les types de ces paires sont inclus entre parenthèses dans le type (par exemple, OBJECT(ville VARCHAR)). Étant donné qu’un OBJECT structuré vide ne contient aucune paire clé-valeur, les parenthèses sont vides.

Insertion de paires clé-valeur et mise à jour des valeurs

Pour renvoyer un nouvel objet contenant les paires clé-valeur d’un objet existant avec des paires clé-valeur supplémentaires ou de nouvelles valeurs pour les clés, appelez la fonction OBJECT_INSERT.

Lors de l’appel de cette fonction, notez les points suivants :

  • Pour les arguments qui sont des clés, vous devez spécifier des constantes.

  • Lorsque l’argument updateFlag est défini sur FALSE (lorsque vous insérez une nouvelle paire clé-valeur) :

    • Si vous spécifiez une clé qui existe déjà dans l’OBJECT, il se produit une erreur.

      SELECT OBJECT_INSERT(
        {'city':'San Mateo','state':'CA'}::OBJECT(city VARCHAR,state VARCHAR),
        'city',
        'San Jose',
        false
      );
      
      Copy
      093202 (23001): Function OBJECT_INSERT:
        expected structured object to not contain field city but it did.
      
    • La fonction renvoie un OBJECT structuré. Le type de l’OBJECT comprend la nouvelle clé insérée. Par exemple, supposons que vous ajoutiez la clé zipcode avec la valeur DOUBLE 94402 :

      SELECT
        OBJECT_INSERT(
          {'city':'San Mateo','state':'CA'}::OBJECT(city VARCHAR,state VARCHAR),
          'zip_code',
          94402::DOUBLE,
          false
        ) AS new_object,
        SYSTEM$TYPEOF(new_object);
      
      Copy

      La fonction renvoie un OBJECT du type OBJECT(ville VARCHAR, état VARCHAR, code postal DOUBLE) :

      +-------------------------------------+---------------------------------------------------------------------------------------+
      | NEW_OBJECT                          | SYSTEM$TYPEOF(NEW_OBJECT)                                                             |
      |-------------------------------------+---------------------------------------------------------------------------------------|
      | {                                   | OBJECT(city VARCHAR(16777216), state VARCHAR(16777216), zip_code FLOAT NOT NULL)[LOB] |
      |   "city": "San Mateo",              |                                                                                       |
      |   "state": "CA",                    |                                                                                       |
      |   "zip_code": 9.440200000000000e+04 |                                                                                       |
      | }                                   |                                                                                       |
      +-------------------------------------+---------------------------------------------------------------------------------------+
      

      Le type de la valeur insérée détermine le type ajouté à la définition de type OBJECT. Dans ce cas, la valeur de zipcode est une valeur transformée en DOUBLE, si bien que le type de zipcode est DOUBLE.

  • Lorsque l’argument updateFlag est défini sur TRUE (lorsque vous remplacez une paire clé-valeur existante) :

    • Si vous spécifiez une clé qui n’existe pas dans l’OBJECT, il se produit une erreur.

    • La fonction renvoie un OBJECT structuré du même type.

    • Le type de la valeur insérée est contraint à se transformer dans le type de la clé existante.

Sélection de paires clé-valeur à partir d’un OBJECT existant

Pour renvoyer un nouvel objet contenant des paires clé-valeur sélectionnées à partir d’un objet existant, appelez la fonction OBJECT_PICK.

Lors de l’appel de cette fonction, notez les points suivants :

  • Pour les arguments qui sont des clés, vous devez spécifier des constantes.

  • Vous ne pouvez pas transmettre un ARRAY de clés comme deuxième argument. Vous devez spécifier chaque clé sous la forme d’un argument distinct.

  • La fonction renvoie un OBJECT structuré. Le type de l’OBJECT comprend les clés dans l’ordre dans lequel elles sont spécifiées.

    Supposons, par exemple, que vous sélectionniez les clés state et city dans cet ordre :

    SELECT
      OBJECT_PICK(
        {'city':'San Mateo','state':'CA','zip_code':94402}::OBJECT(city VARCHAR,state VARCHAR,zip_code DOUBLE),
        'state',
        'city') AS new_object,
      SYSTEM$TYPEOF(new_object);
    
    Copy

    La fonction renvoie un OBJECT du type OBJECT(state VARCHAR, city VARCHAR).

    +-----------------------+--------------------------------------------------------------+
    | NEW_OBJECT            | SYSTEM$TYPEOF(NEW_OBJECT)                                    |
    |-----------------------+--------------------------------------------------------------|
    | {                     | OBJECT(state VARCHAR(16777216), city VARCHAR(16777216))[LOB] |
    |   "state": "CA",      |                                                              |
    |   "city": "San Mateo" |                                                              |
    | }                     |                                                              |
    +-----------------------+--------------------------------------------------------------+
    

Transformation de MAPs

Pour transformer des MAPs, utilisez les fonctions suivantes :

Utilisation de types structurés

Les sections suivantes expliquent comment utiliser différentes fonctions SQL et opérateurs d’ensemble avec des objets de types structurés :

Utilisation de la fonction FLATTEN avec des types structurés

Vous pouvez transmettre des ARRAYs structurés, des OBJECTs structurés et des MAPs à la fonction FLATTEN. Comme pour les types de données semi-structurées, vous pouvez utiliser l’argument PATH pour spécifier la valeur à aplatir.

  • Si la valeur à aplatir est un ARRAY structuré et si l’argument RECURSIVE est défini sur FALSE, la colonne value contient une valeur du même type que le ARRAY.

    Par exemple :

    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]        |
    +-------+----------------------+
    
  • Si la valeur aplatie est une MAP et si l’argument RECURSIVE est défini sur FALSE, la colonne key contient une clé du même type que la clé de la MAP, et la colonne value contient une valeur du même type que la valeur de la MAP.

    Par exemple :

    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] |
    +--------+------------------------+----------+------------------------+
    
  • Sinon, les colonnes key et value sont de type VARIANT.

Pour les MAPs, l’ordre des clés et des valeurs renvoyées est indéterminé.

Utilisation de la fonction PARSE_JSON

Notez que la fonction PARSE_JSON ne renvoie pas de types structurés.

Utilisation de types structurés avec des opérateurs d’ensemble et des expressions CASE

Vous pouvez utiliser des ARRAYs structurés, des OBJECTs structurés et des MAPs dans les éléments suivants :

Pour les opérateurs d’ensemble, si différents types sont utilisés dans les différentes expressions (par exemple, si un type est ARRAY(NUMBER) et l’autre est ARRAY(DOUBLE)), un type sera contraint de se transformer dans l’autre.

Utilisation d’autres fonctions semi-structurées

Les fonctions suivantes n’acceptent pas de ARRAY structuré, d’OBJECT structuré ni de MAP comme argument d’entrée :

La transmission d’un type structuré en entrée entraîne une erreur.

Accès aux types structurés dans des applications utilisant des pilotes

Dans les applications qui utilisent des pilotes (par exemple le pilote ODBC ou JDBC, le connecteur Snowflake pour Python, etc.), les valeurs des types structurés sont renvoyées sous forme de types semi-structurés. Par exemple :

  • Une colonne de ARRAY structuré est renvoyée sous forme de ARRAY semi-structuré à l’application cliente.

  • Une colonne d’OBJECT structuré ou de MAP est renvoyée sous forme d’OBJECT semi-structuré à l’application cliente.

Utilisation de types structurés avec des fonctions définies par l’utilisateur (UDFs) et des procédures stockées

Lorsque vous créez une fonction définie par l’utilisateur (UDF), une fonction de table définie par l’utilisateur (UDTF) ou une procédure stockée dans SQL ou Snowflake Scripting, vous pouvez utiliser des types structurés dans les arguments et les valeurs de retour. Par exemple :

CREATE OR REPLACE FUNCTION my_udf(
    location OBJECT(city VARCHAR, zipcode NUMBER, val ARRAY(BOOLEAN)))
  RETURNS VARCHAR
  AS
  $$
    ...
  $$;
Copy
CREATE OR REPLACE FUNCTION my_udtf(check BOOLEAN)
  RETURNS TABLE(col1 ARRAY(VARCHAR))
  AS
  $$
  ...
  $$;
Copy
CREATE OR REPLACE PROCEDURE my_procedure(values ARRAY(INTEGER))
  RETURNS ARRAY(INTEGER)
  LANGUAGE SQL
  AS
  $$
    ...
  $$;
Copy

Note

Les types structurés ne sont pas encore pris en charge dans les UDFs, les UDTFs et les procédures stockées dans d’autres langages (Java, JavaScript, Python et Scala).

Affichage d’informations sur les types structurés

Les sections suivantes décrivent les vues et les commandes que vous pouvez utiliser pour afficher des informations sur les types structurés :

Utilisation de la commande SHOW COLUMNS pour afficher des informations sur les types structurés

Dans le résultat de la commande SHOW COLUMNS, la colonne data_type contient des informations sur les types des éléments, des clés et des valeurs.

Utilisation des commandes DESCRIBE et autres SHOW pour afficher des informations sur les types structurés

La sortie des commandes suivantes comprend des informations sur les types structurés :

Par exemple, dans la sortie DESCRIBE RESULT, la ligne d’une colonne MAP(VARCHAR, VARCHAR) contient la valeur suivante dans la colonne type :

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

La ligne d’une colonne ARRAY(NUMBER) contient la valeur suivante dans la colonne type :

ARRAY(NUMBER(38,0))
Copy

Affichage d’informations sur les types structurés utilisés dans une base de données

Pour les colonnes de types structurés, le INFORMATION_SCHEMA Vue COLUMNS ne fournit que des informations sur le type de données de base de la colonne (ARRAY, OBJECT ou MAP).

Par exemple, la colonne data_type contient simplement « ARRAY », « OBJECT » ou « MAP ». La colonne n’inclut pas les types des éléments, clés ou valeurs.

Pour obtenir des informations sur les types des éléments, clés et valeurs, utilisez les vues suivantes :