Types de données structurés¶

Spécification d’un type ARRAY structuré¶

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

ARRAY( <element_type> [ NOT NULL ] )

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 une valeur ARRAY semi-structurée en une valeur ARRAY structurée (un ARRAY de NUMBER éléments).

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

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

Spécification d’un type OBJECT structuré¶

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

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

Où :

• key spécifie une clé pour le type OBJECT.

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

  • L’ordre des clés fait partie de la définition d’OBJECT. Il n’est pas autorisé de comparer deux valeurs OBJECT 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 une valeur OBJECT semi-structurée en une valeur OBJECT structurée 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 une valeur OBJECT semi-structurée.

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

Spécification d’un type MAP¶

Pour spécifier un type MAP, utilisez la syntaxe suivante :

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

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

  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.

L’exemple suivant transforme une valeur OBJECT semi-structurée en une valeur MAP et utilise la fonction SYSTEM$TYPEOF pour imprimer le type obtenu de la valeur. 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;

+------------------------------------------------+
| MAP_EXAMPLE                                    |
|------------------------------------------------|
| MAP(VARCHAR(16777216), VARCHAR(16777216))[LOB] |
+------------------------------------------------+

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

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

Vous pouvez uniquement convertir les valeurs des types semi-structurés suivants en valeurs de type structuré correspondant ; sinon, il se produit une erreur d’exécution.

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

• Conversion de valeurs ARRAY et VARIANT semi-structurées en valeurs ARRAY structurées

• Conversion de valeurs OBJECT et VARIANT semi-structurées en valeurs OBJECT structurées

• Conversion de valeurs OBJECT et VARIANT semi-structurées en valeurs 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] |
+------------------------------------------------+------------------------------------------------+

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

Pour convertir explicitement une valeur d’un type structuré en valeur d’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).

Par exemple :

• Si col_structured_array est du type ARRAY(VARCHAR) :

  • CAST(tableau_structuré_col AS ARRAY) renvoie une valeur ARRAY semi-structurée.

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

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

  • CAST(objet_structuré_col AS OBJECT) renvoie une valeur OBJECT semi-structurée.

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

• Si col_map est du type MAP(VARCHAR, VARCHAR) :

  • CAST(carte_col AS OBJECT) renvoie une valeur OBJECT semi-structurée.

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

Remarques :

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

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

  Si vous transformez une valeur ARRAY structurée en valeur 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 valeur MAP qui utilise un type NUMBER pour les clés, les clés de la MAP sont converties en chaînes dans la valeur OBJECT renvoyée.

Conversion implicite d’une valeur (coercition)¶

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

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

  • Une valeur ARRAY d’un type peut être contrainte à se transformer en une valeur 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 :

    • Une valeur ARRAY(NUMBER) peut être contrainte à se transformer en valeur ARRAY(DOUBLE).

    • Une valeur ARRAY(DATE) ne peut être contrainte à se transformer en valeur ARRAY(NUMBER).

  • Une valeur OBJECT avec une définition de type peut être contrainte à se transformer en valeur 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 valeurs ARRAY structurées, 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 :

    • Une valeur OBJECT(ville VARCHAR, code postal NUMBER) peut être contrainte à se transformer en une valeur OBJECT(ville VARCHAR, code postal DOUBLE).

    • Une valeur OBJECT(ville VARCHAR, code postal NUMBER) ne peut être contrainte à se transformer en une valeur OBJECT(ville VARCHAR, code postal DATE).

  • Une valeur MAP avec un type de valeur peut être contrainte à se transformer en valeur 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 valeur MAP(VARCHAR, NUMBER) peut être contrainte à se transformer en valeur MAP(VARCHAR, DOUBLE).

  • Une valeur MAP avec un type de clé peut être contrainte à se transformer en valeur 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 valeur MAP(VARCHAR, NUMBER) ne peut être contrainte à se transformer en valeur MAP(NUMBER, NUMBER).

• Une valeur de type structuré ne peut pas être contrainte à se transformer en valeur semi-structurée (et inversement).

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

Conversion d’un type structuré en un autre¶

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

• Pour les ARRAYs structurés :

  Vous pouvez transformer une valeur ARRAY d’un type en valeur ARRAY d’un autre type.

• Pour les OBJECTs structurés :

  • Vous pouvez utiliser une conversion pour modifier l’ordre des paires clé-valeur d’une valeur OBJECT.

  • Vous pouvez utiliser une conversion pour modifier les noms des clés d’une valeur OBJECT.

  • Vous pouvez utiliser une conversion pour ajouter des clés à une valeur OBJECT.

  • Vous pouvez transformer une valeur OBJECT structurée en valeur MAP.

• Pour les valeurs MAP :

  • Vous pouvez convertir une valeur MAP avec des clés et des valeurs d’un type spécifique en valeur MAP avec des clés et des valeurs d’un type différent.

  • Vous pouvez transformer une valeur MAP en valeur OBJECT structurée.

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’une valeur ARRAY(BOOLEAN) en une valeur ARRAY(DATE) échoue.

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

Utilisation de fonctions SQL pour construire des valeurs ARRAY et OBJECT structurées¶

Les fonctions suivantes permettent de construire des valeurs ARRAY semi-structurées :

• ARRAY_CONSTRUCT

• ARRAY_CONSTRUCT_COMPACT

• ARRAY_AGG

• TO_ARRAY

Les fonctions suivantes permettent de construire des valeurs OBJECT semi-structurées :

• OBJECT_CONSTRUCT

• OBJECT_CONSTRUCT_KEEP_NULL

• OBJECT_AGG

• TO_OBJECT

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

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

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

Utilisation des constantes ARRAY et OBJECT pour construire des valeurs ARRAY et OBJECT structurées¶

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

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

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

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

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

Construire une valeur MAP¶

Pour construire une valeur MAP, construisez une valeur OBJECT semi-structurée et transformez la valeur OBJECT en valeur MAP.

Par exemple, les deux instructions suivantes la valeur 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
);

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

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

Pour plus de détails, reportez-vous à Conversion de valeurs OBJECT et VARIANT semi-structurées en valeurs MAP.

Obtention de la liste de clés à partir d’une valeur OBJECT structurée¶

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

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

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

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

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

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

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

Vous pouvez utiliser les méthodes suivantes pour accéder aux valeurs et aux éléments de valeurs ARRAY structurées, de valeurs OBJECT structurées et de valeurs MAP :

• La fonction GET

• La fonction GET_IGNORE_CASE

• La fonction GET_PATH

• Notation par points

• Notation par crochets

Les valeurs et les éléments renvoyés ont le type spécifié pour la valeur structurée, au lieu de VARIANT.

L’exemple suivant transmet le premier élément d’une valeur ARRAY semi-structurée et d’une valeur 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;

+-------------------------------+-----------------------------+
| SEMI_STRUCTURED_ARRAY_ELEMENT | STRUCTURED_ARRAY_ELEMENT    |
|-------------------------------+-----------------------------|
| VARIANT[LOB]                  | VARCHAR(16777216)[LOB]      |
+-------------------------------+-----------------------------+

Remarques :

• Lorsque vous transmettez une valeur OBJECT structurée à 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 valeur MAP ou une valeur ARRAY structurée à la fonction GET.

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

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

• Pour un valeur OBJECT structurée, 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 une valeur OBJECT semi-structurée, la fonction renvoie NULL.

Détermination de la taille d’une valeur ARRAY structurée¶

Pour déterminer la taille d’une valeur ARRAY structurée, transmettez la valeur ARRAY à la fonction ARRAY_SIZE :

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

Détermination de la taille d’une valeur MAP¶

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

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

Recherche d’éléments dans une valeur ARRAY structurée¶

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

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

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

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

Détermination si une valeur MAP contient une clé¶

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

Par exemple :

SELECT MAP_CONTAINS_KEY('key_to_find', my_map);

SELECT MAP_CONTAINS_KEY(10, my_map);

Comparaison des valeurs structurées avec des valeurs semi-structurées¶

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

Comparaison des valeurs structurées avec d’autres valeurs structurées¶

Vous pouvez comparer deux valeurs du même type (par exemple, deux valeurs ARRAY structurées, deux valeurs OBJECT structurées ou deux valeurs MAP).

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

• =

• !=

• <

• <=

• >=

• >

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

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

• Lorsque vous comparez des valeurs MAP 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 valeurs structurées via <, <=, >= ou >, les champs de valeur structurée sont comparés dans l’ordre alphabétique. Par exemple, la valeur suivante :

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

est supérieur à :

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

Détermination si deux valeurs ARRAY se chevauchent¶

Pour déterminer si les éléments de deux valeurs ARRAY structurées se chevauchent, appelez la fonction ARRAYS_OVERLAP. Par exemple :

SELECT ARRAYS_OVERLAP(numeric_array, other_numeric_array);

Les valeurs ARRAY doivent être de types comparables à.

Vous ne pouvez pas transmettre une valeur ARRAY semi-structurée et une valeur ARRAY structurée à cette fonction. Les deux valeurs ARRAY doivent être soit structurées soit semi-structurées.

Transformation de valeurs ARRAY structurées¶

Lorsque vous transmettez une valeur ARRAY structurée à ces fonctions, celles-ci renvoient une valeur ARRAY structurée du même type :

• ARRAY_APPEND

• ARRAY_CAT

• ARRAY_COMPACT

• ARRAY_EXCEPT

• ARRAY_INSERT

• ARRAY_INTERSECTION

• ARRAY_PREPEND

• ARRAY_SLICE

• ARRAY_UNION_AGG

Les sections suivantes expliquent comment ces fonctions fonctionnent avec les valeurs ARRAY structurées.

• Fonctions qui ajoutent des éléments à des valeurs ARRAY

• Fonctions acceptant plusieurs valeurs ARRAY en entrée

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

Transformation de valeurs OBJECT structurées¶

Les sections suivantes expliquent comment retourner une valeur OBJECT structurée qui a été transformée à partir d’une autre valeur OBJECT :

• Suppression de paires clé-valeur

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

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

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

Transformation de valeurs MAP¶

Pour transformer des valeurs MAP, utilisez les fonctions suivantes :

• MAP_CAT

• MAP_DELETE

• MAP_INSERT

• MAP_PICK

Utilisation de la fonction FLATTEN avec des valeurs de types structurés¶

Vous pouvez transmettre des valeurs ARRAY structurées, des valeurs OBJECT structurées et des valeurs MAP à 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 une valeur ARRAY structurée et si l’argument RECURSIVE est défini sur FALSE, la colonne value contient une valeur du même type que la valeur 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 valeur 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 valeur 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 valeurs MAP, l’ordre des clés et des valeurs renvoyées est indéterminé.

Utilisation de la fonction PARSE_JSON¶

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 valeurs ARRAY structurées, des valeurs OBJECT structurées et des valeurs MAP dans les éléments suivants :

• Expressions de requête combinées par des opérateurs d’ensemble (par exemple UNION ALL).

• Expressions CASE.

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 est contraint de se transformer dans l’autre.

Utilisation d’autres fonctions semi-structurées¶

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

• AS_ARRAY

• AS_OBJECT

• IS_ARRAY

• IS_OBJECT

• TYPEOF

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

Utilisation de la commande SHOW COLUMNS pour voir 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 voir des informations sur les types structurés¶

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

• DESCRIBE TABLE

• DESCRIBE RESULT

• DESCRIBE FUNCTION

• DESCRIBE PROCEDURE

• SHOW FUNCTIONS

• SHOW PROCEDURES

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))

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

ARRAY(NUMBER(38,0))

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 :

• Pour obtenir des informations sur les types des éléments des types ARRAY structurés, interrogez la vue ELEMENT_TYPES dans INFORMATION_SCHEMA ou la vue ELEMENT_TYPES dans ACCOUNT_USAGE.

• Pour obtenir des informations sur les types des clés et des valeurs des types OBJECT structurés et des types MAP, interrogez la vue FIELDS dans INFORMATION_SCHEMA ou la vue FIELDS dans ACCOUNT_USAGE.