Types de données structurés¶

Spécification d’un ARRAY structuré¶

Pour spécifier un 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 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;

+-------------------------------+-----------------------+
| 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 ] ]
    [ , ... ]
  ]
)

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;

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

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;

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

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

Vous pouvez uniquement convertir les objets semi-structurés suivants dans le 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 ARRAYs et de VARIANTs semi-structurés en ARRAYs structurés

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

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

SELECT
  SYSTEM$TYPEOF(
    CAST ([1,2,3] AS ARRAY(NUMBER))
  ) AS array_cast_type,
  SYSTEM$TYPEOF(
    CAST ([1,2,3]::VARIANT AS ARRAY(NUMBER))
  ) AS variant_cast_type;

SELECT
  SYSTEM$TYPEOF(
    [1,2,3]::ARRAY(NUMBER)
  ) AS array_cast_type,
  SYSTEM$TYPEOF(
    [1,2,3]::VARIANT::ARRAY(NUMBER)
  ) AS variant_cast_type;

+--------------------------+--------------------------+
| ARRAY_CAST_TYPE          | VARIANT_CAST_TYPE        |
|--------------------------+--------------------------|
| ARRAY(NUMBER(38,0))[LOB] | ARRAY(NUMBER(38,0))[LOB] |
+--------------------------+--------------------------+

SELECT
  SYSTEM$TYPEOF(
    CAST ({'city':'San Mateo','state':'CA'} AS OBJECT(city VARCHAR, state VARCHAR))
  ) AS object_cast_type,
  SYSTEM$TYPEOF(
    CAST ({'city':'San Mateo','state':'CA'}::VARIANT AS OBJECT(city VARCHAR, state VARCHAR))
  ) AS variant_cast_type;

SELECT
  SYSTEM$TYPEOF(
     {'city':'San Mateo','state':'CA'}::OBJECT(city VARCHAR, state VARCHAR)
  ) AS object_cast_type,
  SYSTEM$TYPEOF(
     {'city':'San Mateo','state':'CA'}::VARIANT::OBJECT(city VARCHAR, state VARCHAR)
  ) AS variant_cast_type;

+--------------------------------------------------------------+--------------------------------------------------------------+
| OBJECT_CAST_TYPE                                             | VARIANT_CAST_TYPE                                            |
|--------------------------------------------------------------+--------------------------------------------------------------|
| OBJECT(city VARCHAR(16777216), state VARCHAR(16777216))[LOB] | OBJECT(city VARCHAR(16777216), state VARCHAR(16777216))[LOB] |
+--------------------------------------------------------------+--------------------------------------------------------------+

SELECT
  SYSTEM$TYPEOF(
    CAST ({'my_key':'my_value'} AS MAP(VARCHAR, VARCHAR))
  ) AS map_cast_type,
  SYSTEM$TYPEOF(
    CAST ({'my_key':'my_value'} AS MAP(VARCHAR, VARCHAR))
  ) AS variant_cast_type;

SELECT
  SYSTEM$TYPEOF(
    {'my_key':'my_value'}::MAP(VARCHAR, VARCHAR)
  ) AS map_cast_type,
  SYSTEM$TYPEOF(
    {'my_key':'my_value'}::VARIANT::MAP(VARCHAR, VARCHAR)
  ) AS variant_cast_type;

+------------------------------------------------+------------------------------------------------+
| MAP_CAST_TYPE                                  | VARIANT_CAST_TYPE                              |
|------------------------------------------------+------------------------------------------------|
| map(VARCHAR(16777216), VARCHAR(16777216))[LOB] | map(VARCHAR(16777216), VARCHAR(16777216))[LOB] |
+------------------------------------------------+------------------------------------------------+

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

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 :

• Pour les ARRAYs structurés :

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

• Pour les OBJECTs structurés :

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

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

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

  • Vous pouvez transformer un OBJECT structuré en MAP.

• Pour MAPs :

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

  • Vous pouvez transformer une MAP en un OBJECT structuré.

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.

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

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

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

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

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

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

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

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

• ARRAY_CONSTRUCT

• ARRAY_CONSTRUCT_COMPACT

• ARRAY_AGG

• TO_ARRAY

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

• OBJECT_CONSTRUCT

• OBJECT_CONSTRUCT_KEEP_NULL

• OBJECT_AGG

• TO_OBJECT

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

SELECT OBJECT_CONSTRUCT(
  'name', 'abc',
  'created_date', '2020-01-18'::DATE
)::OBJECT(
  name 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 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);

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

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

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

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

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

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

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

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

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 :

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

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

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

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

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

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

SELECT MAP_CONTAINS_KEY(10, my_map);

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)

est supérieur à :

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

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

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 ARRAYs structurés¶

Lorsque l’on transmet un ARRAY structuré à ces fonctions, celles-ci renvoient un ARRAY structuré 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 ARRAYs structurés.

• Fonctions qui ajoutent des éléments à des tableaux

• Fonctions acceptant plusieurs ARRAYs 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 d’OBJECTs structurés¶

Les sections suivantes expliquent comment renvoyer un OBJECT structuré qui a été transformé à partir d’un autre 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

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.

Transformation de MAPs¶

Pour transformer des MAPs, utilisez les fonctions suivantes :

• MAP_CAT

• MAP_DELETE

• MAP_INSERT

• MAP_PICK

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 :

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

• AS_ARRAY

• AS_OBJECT

• IS_ARRAY

• IS_OBJECT

• TYPEOF

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

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 :

• 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 ARRAYs 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 OBJECTs structurés et des MAPs, interrogez la vue FIELDS dans INFORMATION_SCHEMA ou la vue FIELDS dans ACCOUNT_USAGE.