Strukturierte Datentypen¶

Festlegen eines strukturierten ARRAY-Typs¶

Um einen strukturierten ARRAY-Typ anzugeben, verwenden Sie die folgende Syntax:

ARRAY( <element_type> [ NOT NULL ] )

Wobei:

• element_type ist der Snowflake-Datentyp der Elemente in diesem ARRAY.

  Sie können auch einen strukturierten ARRAY-Typ, einen strukturierten OBJECT-Typ oder einen strukturierten MAP-Typ angeben.

  Bemerkung

  In der Definition einer Iceberg-Tabellenspalte können Sie keinen VARIANT-Typ, keinen semistrukturierten ARRAY-Typ und keinen semistrukturierten OBJECT-Typ als Typ des ARRAY-Elements angeben.

• NOT NULL legt fest, dass der ARRAY-Typ keine Elemente enthalten darf, die NULL sind.

Vergleichen Sie zum Beispiel die von der Funktion SYSTEM$TYPEOF in der folgenden Anweisung zurückgegebenen Typen:

• Der erste Spaltenausdruck wandelt einen semistrukturierten ARRAY-Typ in einen strukturierten ARRAY-Typ um (ein ARRAY aus NUMBER-Elementen).

• Der zweite Spaltenausdruck gibt einen semistrukturierten ARRAY-Typ an.

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

Festlegen eines strukturierten OBJECT-Typs¶

Um einen strukturierten OBJECT-Typ anzugeben, verwenden Sie die folgende Syntax:

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

Wobei:

• key gibt einen Schlüssel für den OBJECT-Typ an.

  • Jeder key-Wert in einer Objektdefinition muss eindeutig sein.

  • Die Reihenfolge der Schlüssel ist Teil der Objektdefinition. Das Vergleichen von zwei OBJECT-Typen, die die gleichen Schlüssel in unterschiedlicher Reihenfolge haben, ist nicht zulässig. (Ein Kompilierzeitfehler tritt auf.)

  • Wenn Sie keinen Schlüssel, aber die Klammern angeben (d. h. wenn Sie OBJECT() verwenden), ist der resultierende Typ ein strukturierter OBJECT-Typ, der keine Schlüssel enthält. Ein strukturierter OBJECT-Typ ohne Schlüssel unterscheidet sich von einem semistrukturierten OBJECT-Typ.

• value_type ist der Snowflake-Datentyp des Wertes zum zugehörigen Schlüssel.

  Sie können auch einen strukturierten ARRAY-Typ, einen strukturierten OBJECT-Typ oder einen strukturierten MAP-Typ als Typ des Wertes angeben.

  Bemerkung

  In der Definition einer Iceberg-Tabellenspalte können Sie keinen VARIANT-Typ, keinen semistrukturierten ARRAY-Typ und keinen semistrukturierten OBJECT-Typ als Typ des Wertes angeben, der dem OBJECT-Schlüssel entspricht.

• NOT NULL gibt an, dass der dem Schlüssel entsprechende Wert nicht NULL sein darf.

Vergleichen Sie zum Beispiel die von der Funktion SYSTEM$TYPEOF in der folgenden Anweisung zurückgegebenen Typen:

• Der erste Spaltenausdruck wandelt einen semistrukturierten OBJECT-Typ in einen strukturierten OBJECT-Typ um, der die folgenden Schlüssel und Werte enthält:

  • Ein Schlüssel namens str mit einem VARCHAR-Wert, der nicht NULL ist.

  • Ein Schlüssel namens num mit einem NUMBER-Wert.

• Der zweite Spaltenausdruck gibt einen semistrukturierten OBJECT-Typ an.

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

Festlegen eines MAP-Typs¶

Um einen MAP-Typ anzugeben, verwenden Sie die folgende Syntax:

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

Wobei:

• key_type ist der Snowflake-Datentyp des Schlüssels für die Zuordnungsstruktur. Verwenden Sie für die Schlüssel einen der folgenden Typen:

  • VARCHAR

  • NUMBER mit der Dezimalstellenzahl 0

  Sie können keinen Gleitkomma-Datentyp als Typ für den Schlüssel verwenden.

  Beachten Sie, dass Zuordnungsschlüssel nicht den Wert NULL haben können.

• value_type ist der Snowflake-Datentyp der Werte in der Zuordnung.

  Sie können auch einen strukturierten ARRAY-Typ, einen strukturierten OBJECT-Typ oder einen strukturierten MAP-Typ als Typ der Werte angeben.

  Bemerkung

  In der Definition einer Iceberg-Tabellenspalte können Sie keinen VARIANT-Typ, keinen semistrukturierten ARRAY-Typ und keinen semistrukturierten OBJECT-Typ als Typ des Wertes im MAP-Typ angeben.

• NOT NULL gibt an, dass der dem Schlüssel entsprechende Wert nicht NULL sein darf. NOT NULL gibt an, dass der Wert nicht NULL sein darf.

Das folgende Beispiel wandelt einen semistrukturierten OBJECT-Typ in einen MAP-Typ um und verwendet die Funktion SYSTEM$TYPEOF, um den resultierenden Typ des Objekts zu drucken. Der MAP-Typ verknüpft VARCHAR-Schlüssel mit VARCHAR-Werten.

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

Explizites Umwandeln eines semistrukturierten Typs in einen strukturierten Typ¶

Um ein semistrukturiertes Objekt explizit in einen strukturierten Typ umzuwandeln, können Sie die Funktion CAST aufrufen oder den Operator :: verwenden.

Sie können nur die folgenden semistrukturierten Objekte in den entsprechenden strukturierten Typ umwandeln, in allen anderen Fällen kommt es zu einem Laufzeitfehler.

In den nächsten Abschnitten erfahren Sie mehr darüber, wie die Typen umgewandelt werden:

• Umwandeln von semistrukturierten ARRAYs und VARIANTs in strukturierte ARRAYs

• Umwandeln von semistrukturierten OBJECTs und VARIANTs in strukturierte OBJECTs

• Umwandeln semistrukturierter OBJECT- und VARIANT-Typen in MAP-Typen

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

Explizites Umwandeln eines strukturierten Typs in einen semistrukturierten Typ¶

Um einen strukturierten Typ explizit in einen semistrukturierten Typ umzuwandeln, können Sie die Funktion CAST aufrufen, den Operator :: verwenden oder eine der Konvertierungsfunktionen (z. B. TO_ARRAY, TO_OBJECT oder TO_VARIANT) aufrufen.

Beispiel:

• Wenn col_structured_array vom Typ ARRAY(VARCHAR) ist:

  • CAST(col_structured_array AS ARRAY) gibt einen semistrukturierten ARRAY-Typ zurück.

  • CAST(col_structured_array AS VARIANT) gibt einen VARIANT-Typ zurück, der einen semistrukturierten ARRAY-Typ enthält.

• Wenn col_structured_object vom Typ OBJECT(name VARCHAR, state VARCHAR):

  • CAST(col_structured_object AS OBJECT) gibt einen semistrukturierten OBJECT-Typ zurück.

  • CAST(col_structured_object AS VARIANT) gibt einen VARIANT-Typ zurück, der einen semistrukturierten OBJECT-Typ enthält.

• Wenn col_map vom Typ MAP(VARCHAR, VARCHAR) ist:

  • CAST(col_map AS OBJECT) gibt einen semistrukturierten OBJECT-Typ zurück.

  • CAST(col_map AS VARIANT) gibt einen VARIANT-Typ zurück, der einen semistrukturierten OBJECT-Typ enthält.

Beachten Sie Folgendes:

• Beim Umwandeln in einen semistrukturierten OBJECT-Typ wird die Reihenfolge der Schlüssel im strukturierten OBJECT-Typ nicht beibehalten.

• Wenn Sie einen strukturierten OBJECT-Typ oder einen MAP-Typ in einen semistrukturierten OBJECT- oder VARIANT-Typ umwandeln, werden alle NULL-Werte in JSON-Nullwerte umgewandelt.

  Wenn Sie einen strukturierten ARRAY-Typ in einen VARIANT-Typ umwandeln, bleiben die NULL-Werte unverändert erhalten.

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

  Copy

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

• Wenn Sie einen MAP-Typ umwandeln, der einen NUMBER-Typ für Schlüssel verwendet, werden die MAP-Schlüssel im zurückgegebenen OBJECT-Typ in Zeichenfolgen konvertiert.

Implizites Umwandeln eines Wertes (Koersion)¶

Die folgenden Regeln gelten für implizites Umwandeln (Koersion) von einem strukturierten Typ in einen anderen strukturierten Typ:

• Ein strukturierter Typ kann in einen anderen strukturierten Typ umgewandelt werden, wenn die beiden grundlegenden Objekttypen gleich sind:

  • Ein ARRAY-Typ kann in einen anderen ARRAY-Typ implizit umgewandelt werden (Koersion), sofern Koersion vom ersten Elementtyp in den zweiten Elementtyp möglich ist.

    Ein Elementtyp kann in einem der folgenden Fälle implizit in einen anderen Elementtyp umgewandelt werden (Koersion):

    • Beide Typen sind numerisch. Die folgenden Fälle werden unterstützt:

      • Beide verwenden denselben numerischen Typ, unterscheiden sich aber möglicherweise in der Genauigkeit und/oder der Dezimalstellenzahl.

      • Koersion von NUMBER in FLOAT (und umgekehrt).

    • Beide Typen sind Zeitstempel. Die folgenden Fälle werden unterstützt:

      • Beide verwenden denselben Typ, unterscheiden sich aber möglicherweise in der Genauigkeit.

      • Koersion von TIMESTAMP_LTZ in TIMESTAMP_TZ (und umgekehrt).

    Beispiel:

    • Ein ARRAY(NUMBER)-Typ kann in einen ARRAY(DOUBLE)-Typ implizit umgewandelt werden.

    • Ein ARRAY(DATE)-Typ kann nicht in einen ARRAY(NUMBER)-Typ implizit umgewandelt werden.

  • Ein OBJECT-Typ mit einer Typdefinition kann nur dann in einen OBJECT-Typ mit einer anderen Typdefinition implizit umgewandelt werden, wenn alle der folgenden Punkte zutreffen:

    • Beide OBJECT-Typen haben die gleiche Anzahl von Schlüsseln.

    • Beide OBJECT-Typen verwenden die gleichen Namen für die Schlüssel.

    • Die Schlüssel in beiden OBJECT-Typen sind in der gleichen Reihenfolge.

    • Der Typ jedes Wertes in einem OBJECT-Typ kann in den Typ des entsprechenden Wertes in dem anderen OBJECT-Typ implizit umgewandelt werden.

      Wie bei den Elementtypen in strukturierten ARRAY-Typen kann der Typ eines Wertes nur unter folgenden Voraussetzungen in einen anderen Typ implizit umgewandelt werden:

      • Beide Typen sind numerisch. Die folgenden Fälle werden unterstützt:

        • Beide verwenden denselben numerischen Typ, unterscheiden sich aber möglicherweise in der Genauigkeit und/oder der Dezimalstellenzahl.

        • Koersion von NUMBER in FLOAT (und umgekehrt).

      • Beide Typen sind Zeitstempel. Die folgenden Fälle werden unterstützt:

        • Beide verwenden denselben Typ, unterscheiden sich aber möglicherweise in der Genauigkeit.

        • Koersion von TIMESTAMP_LTZ in TIMESTAMP_TZ (und umgekehrt).

    Beispiel:

    • Ein OBJECT(city VARCHAR, zipcode NUMBER)-Typ kann in einen OBJECT(city VARCHAR, zipcode DOUBLE)-Typ implizit umgewandelt werden.

    • Ein OBJECT(city VARCHAR, zipcode NUMBER)-Typ kann nicht in einen OBJECT(city VARCHAR, zipcode DATE)-Typ implizit umgewandelt werden.

  • Ein MAP-Typ mit einem Werttyp kann unter folgenden Bedingungen in einen MAP-Typ eines anderen Werttyps implizit umgewandelt werden:

    • Beide Wertetypen sind numerisch. Die folgenden Fälle werden unterstützt:

      • Beide verwenden denselben numerischen Typ, unterscheiden sich aber möglicherweise in der Genauigkeit und/oder der Dezimalstellenzahl.

      • Koersion von NUMBER in FLOAT (und umgekehrt).

    • Beide Werttypen sind Zeitstempel. Die folgenden Fälle werden unterstützt:

      • Beide verwenden denselben Typ, unterscheiden sich aber möglicherweise in der Genauigkeit.

      • Koersion von TIMESTAMP_LTZ in TIMESTAMP_TZ (und umgekehrt).

    Beispielsweise kann ein MAP(VARCHAR, NUMBER)-Typ in einen MAP(VARCHAR, DOUBLE)-Typ implizit umgewandelt werden.

  • Ein MAP-Typ mit einem Schlüsseltyp kann in einen MAP-Typ mit einem anderen Schlüsseltyp umgewandelt werden, wenn beide Schlüsseltypen denselben ganzzahligen NUMERIC-Typ verwenden, der sich nur in der Genauigkeit unterscheidet.

    Beispielsweise kann ein MAP(VARCHAR, NUMBER)-Typ nicht in einen MAP(NUMBER, NUMBER)-Typ implizit umgewandelt werden.

• Ein strukturierter Typ kann nicht in ein semistrukturiertes Objekt umgewandelt werden (und umgekehrt).

• Ein VARCHAR-Wert kann nicht in einen strukturierten Typ implizit umgewandelt werden (Koersion).

Umwandeln von einem strukturierten Typ in einen anderen¶

Sie können die Funktion CAST aufrufen oder den Operator :: verwenden, um von einem strukturierten Typ in einen anderen umzuwandeln. Sie können eine Umwandlung von und in die folgenden strukturierten Typen vornehmen:

• Bei strukturierten ARRAY-Typen:

  Sie können einen ARRAY-Typ in einen anderen ARRAY-Typ umwandeln.

• Bei strukturierten OBJECT-Typen:

  • Sie können eine Umwandlung verwenden, um eine Änderung der Reihenfolge der Schlüssel-Wert-Paare in einen OBJECT-Typ vorzunehmen.

  • Sie können eine Umwandlung verwenden, um eine Änderung des Namens der Schlüssel in einen OBJECT-Typ vorzunehmen.

  • Sie können eine Umwandlung verwenden, um Schlüssel zu einem OBJECT-Typ hinzuzufügen.

  • Sie können einen strukturierten OBJECT-Typ in einen MAP-Typ umwandeln.

• MAPs:

  • Sie können einen MAP-Typ mit Schlüsseln und Werten eines bestimmten Typs in einen MAP-Typ mit Schlüsseln und Werten eines anderen Typs umwandeln.

  • Sie können einen MAP-Typ in einen strukturierten OBJECT-Typ umwandeln.

Wenn es nicht möglich ist, die Werte von einem Typ in den anderen umzuwandeln, schlägt die Umwandlung fehl. Beispielsweise schlägt der Versuch fehl, einen ARRAY(BOOLEAN)-Typ in einen ARRAY(DATE)-Typ umzuwandeln.

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

Verwenden von SQL-Funktionen zum Erstellen von strukturierten ARRAY- und OBJECT-Typen¶

Mit den folgenden Funktionen werden semistrukturierte ARRAY-Typen erstellt:

• ARRAY_CONSTRUCT

• ARRAY_CONSTRUCT_COMPACT

• ARRAY_AGG

• TO_ARRAY

Mit den folgenden Funktionen werden semistrukturierte OBJECT-Typen erstellt:

• OBJECT_CONSTRUCT

• OBJECT_CONSTRUCT_KEEP_NULL

• OBJECT_AGG

• TO_OBJECT

Um einen strukturierten ARRAY- oder OBJECT-Typ zu konstruieren, verwenden Sie diese Funktionen und wandeln den Rückgabewert der Funktion explizit um. Beispiel:

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

Weitere Informationen dazu finden Sie unter Explizites Umwandeln eines semistrukturierten Typs in einen strukturierten Typ.

Verwenden von ARRAY- und OBJECT-Konstanten zum Erstellen von strukturierten ARRAY- und OBJECT-Typen¶

Wenn Sie eine ARRAY-Konstante oder eine OBJECT-Konstante angeben, geben Sie einen semistrukturierten ARRAY- oder OBJECT-Typ an.

Um einen strukturierten ARRAY- oder OBJECT-Typ zu konstruieren, müssen Sie den Ausdruck explizit umwandeln. Beispiel:

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

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

Weitere Informationen dazu finden Sie unter Explizites Umwandeln eines semistrukturierten Typs in einen strukturierten Typ.

Erstellen eines MAP-Typs¶

Um einen MAP-Typ zu konstruieren, konstruieren Sie ein semistrukturiertes Objekt und wandeln den OBJECT-Typ in einen MAP-Typ um.

Die folgenden Anweisungen erstellen beispielsweise beide den MAP-Typ {'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
);

Die folgende Anweisung erstellt den MAP-Typ {-10->'CA',-20->'OR'}:

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

Weitere Informationen dazu finden Sie unter Umwandeln semistrukturierter OBJECT- und VARIANT-Typen in MAP-Typen.

Abrufen der Liste von Schlüsseln aus einem strukturierten OBJECT¶

Um die Liste der Schlüssel in einem strukturierten OBJECT zu erhalten, rufen Sie die Funktion OBJECT_KEYS auf:

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

Wenn der Eingabewert ein strukturiertes OBJECT ist, gibt die Funktion ein ARRAY(VARCHAR) zurück, das die Schlüssel enthält. Wenn der Eingabewert ein semistrukturiertes OBJECT ist, gibt die Funktion ein ARRAY zurück.

Abrufen der Liste von Schlüsseln aus einem MAP-Objekt¶

Um die Liste der Schlüssel aus einem MAP-Objekt zu erhalten, rufen Sie die Funktion MAP_KEYS auf:

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

Zugriff auf Werte und Elemente von strukturierten Typen¶

Sie können die folgenden Methoden verwenden, um auf Werte und Elemente aus strukturierten ARRAYs, strukturierten OBJECTs und MAPs zuzugreifen:

• Funktion GET

• Funktion GET_IGNORE_CASE

• Funktion GET_PATH

• Punktierte Schreibweise

• Klammernotation

Die zurückgegebenen Werte und Elemente haben den für das Objekt angegebenen Typ und nicht VARIANT.

Das folgende Beispiel übergibt das erste Element eines semistrukturierten ARRAY und ein ARRAY(VARCHAR) an die Funktion SYSTEM$TYPEOF, um den Datentyp dieses Elements zurückzugeben:

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

Beachten Sie Folgendes:

• Wenn Sie ein strukturiertes OBJECT an die Funktion GET oder GET_IGNORE_CASE übergeben, müssen Sie eine Konstante für den Schlüssel angeben.

  Sie müssen keine Konstante angeben, wenn Sie der Funktion GET ein MAP-Objekt oder ein strukturiertes ARRAY übergeben.

  Sie müssen auch keine Konstante angeben, wenn Sie ein MAP-Objekt an die Funktion GET_IGNORE_CASE übergeben.

• Wenn Sie einen strukturiertes OBJECT, ein strukturiertes ARRAY oder ein MAP-Objekt an die Funktion GET_PATH übergeben, müssen Sie eine Konstante für den Pfadnamen angeben.

• Wenn Sie für ein strukturiertes Objekt einen OBJECT-Schlüssel oder einen Pfad verwenden, der nicht existiert, tritt ein Kompilierungsfehler auf.

  Wenn Sie dagegen einen Index, Schlüssel oder Pfad verwenden, der bei einem semistrukturierten Objekt nicht vorhanden ist, gibt die Funktion NULL zurück.

Bestimmen der Größe eines strukturierten ARRAY-Objekts¶

Um die Größe eines strukturierten ARRAY-Objekts zu bestimmen, übergeben Sie das ARRAY an die Funktion ARRAY_SIZE:

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

Bestimmen der Größe eines MAP-Objekts¶

Um die Größe eines MAP-Objekts zu bestimmen, übergeben Sie das MAP-Objekt an die Funktion MAP_SIZE:

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

Suchen von Elementen in einem strukturierten ARRAY¶

Um festzustellen, ob ein Element in einem strukturierten ARRAY vorhanden ist, rufen Sie die Funktion ARRAY_CONTAINS auf. Beispiel:

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

Um die Position eines Elements in einem strukturierten ARRAY zu bestimmen, rufen Sie die Funktion ARRAY_POSITION auf. Beispiel:

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

Feststellen, ob ein MAP-Objekt einen Schlüssel enthält¶

Um festzustellen, ob ein MAP-Objekt einen Schlüssel enthält, rufen Sie die Funktion MAP_CONTAINS_KEY auf:

Beispiel:

SELECT MAP_CONTAINS_KEY('key_to_find', my_map);

SELECT MAP_CONTAINS_KEY(10, my_map);

Vergleichen von strukturierten Objekten mit semistrukturierten Objekten¶

Sie können ein strukturiertes ARRAY-, strukturiertes OBJECT- oder MAP-Objekt nicht mit einem semistrukturierten ARRAY-, OBJECT- oder VARIANT-Objekt vergleichen.

Vergleichen von strukturierten Objekten mit anderen strukturierten Objekten¶

Sie können zwei Objekte desselben Typs vergleichen (z. B. zwei strukturierte ARRAYs, zwei strukturierte OBJECTs oder zwei MAPs).

Derzeit werden die folgenden Vergleichsoperatoren für den Vergleich strukturierter Typen unterstützt:

• =

• !=

• <

• <=

• >=

• >

Wenn Sie zwei strukturierte Objekte auf Gleichheit vergleichen, sollten Sie Folgendes beachten:

• Wenn ein Typ nicht implizit umgewandelt (Koersion) werden kann in den anderen Typ, schlägt der Vergleich fehl.

• Wenn Sie MAPs vergleichen, die numerische Schlüssel haben, werden die Schlüssel als Zahlen verglichen (nicht als VARCHAR-Werte).

Wenn Sie zwei strukturierte Objekte mit <, <=, >= oder > vergleichen, werden die Felder des strukturierten Objekts in alphabetischer Reihenfolge verglichen. Beispielsweise ist das folgende Objekt:

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

größer als:

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

Bestimmen, ob sich zwei ARRAYs überlappen¶

Wenn Sie feststellen müssen, ob sich die Elemente von zwei strukturierten ARRAYs überschneiden, rufen Sie die Funktion ARRAYS_OVERLAP auf. Beispiel:

SELECT ARRAYS_OVERLAP(numeric_array, other_numeric_array);

Die ARRAYs müssen vergleichbare Typen sein.

Beachten Sie, dass Sie dieser Funktion kein semistrukturiertes ARRAY und ein strukturiertes ARRAY übergeben können. Beide ARRAYs müssen entweder strukturiert oder semistrukturiert sein.

Transformieren von strukturierten ARRAYs¶

Wenn diesen Funktionen ein strukturiertes ARRAY übergeben wird, geben die Funktionen einen strukturiertes ARRAY desselben Typs zurück:

• ARRAY_APPEND

• ARRAY_CAT

• ARRAY_COMPACT

• ARRAY_EXCEPT

• ARRAY_INSERT

• ARRAY_INTERSECTION

• ARRAY_PREPEND

• ARRAY_SLICE

• ARRAY_UNION_AGG

In den nächsten Abschnitten wird erklärt, wie diese Funktionen mit strukturierten ARRAYs funktionieren.

• Funktionen, die Elemente zu Arrays hinzufügen

• Funktionen, die mehrere ARRAYs als Eingabe akzeptieren

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

Transformieren von strukturierten OBJECTs¶

In den folgenden Abschnitten wird erklärt, wie ein strukturiertes OBJECT zurückgegeben wird, das aus einem anderen OBJECT transformiert wurde:

• Entfernen von Schlüssel-Wert-Paaren

• Einfügen von Schlüssel-Wert-Paaren und Aktualisieren von Werten

• Auswählen von Schlüssel-Wert-Paaren aus einem bestehenden OBJECT

Wenn Sie die Reihenfolge von Schlüssel-Wert-Paaren ändern, Schlüssel umbenennen oder Schlüssel hinzufügen möchten, ohne Werte anzugeben, verwenden Sie die Funktion CAST oder den Operator ::. Weitere Details dazu finden Sie unter Umwandeln von einem strukturierten Typ in einen anderen.

Transformieren von MAPs¶

Um MAPs zu transformieren, verwenden Sie die folgenden Funktionen:

• MAP_CAT

• MAP_DELETE

• MAP_INSERT

• MAP_PICK

Verwenden der FLATTEN-Funktion mit strukturierten Typen¶

Sie können strukturierte ARRAYs, strukturierte OBJECTs und MAPs an die Funktion FLATTEN übergeben. Wie bei semistrukturierten Datentypen können Sie das Argument PATH verwenden, um den zu vereinfachenden Wert anzugeben.

• Wenn der zu vereinfachende Wert ein strukturiertes ARRAY ist und das Argument RECURSIVE den Wert FALSE hat, enthält die Spalte value einen Wert desselben Typs wie ARRAY.

  Beispiel:

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

• Wenn der zu vereinfachende Wert ein MAP ist und das RECURSIVE-Argument den Wert FALSE hat, enthält die key-Spalte einen Schlüssel desselben Typs wie der MAP-Schlüssel, und die value-Spalte enthält einen Wert desselben Typs wie der MAP-Wert.

  Beispiel:

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

• Ansonsten haben die Spalten key und value den Typ VARIANT.

Bei MAPs ist die Reihenfolge der zurückgegebenen Schlüssel und Werte unbestimmt.

Verwenden der Funktion PARSE_JSON¶

Beachten Sie, dass die Funktion PARSE_JSON keine strukturierten Typen zurückgibt.

Verwenden strukturierter Typen mit Mengenoperatoren und CASE-Ausdrücken¶

Sie können strukturierte ARRAYs, strukturierte OBJECTs und MAPs in verwenden:

• Durch Mengenoperatoren kombinierte Abfrageausdrücke (z. B. UNION ALL).

• CASE-Ausdrücke.

Wenn bei Mengenoperatoren unterschiedliche Typen in den verschiedenen Ausdrücken verwendet werden (z. B. wenn ein Typ ARRAY(NUMBER) und der andere ARRAY(DOUBLE) ist), wird ein Typ implizit umgewandelt in den anderen Typ.

Arbeiten mit anderen semistrukturierten Funktionen¶

Die folgenden Funktionen akzeptieren keine strukturierten ARRAY-, strukturierten OBJECT- oder MAP-Objekte als Eingabeargument:

• AS_ARRAY

• AS_OBJECT

• IS_ARRAY

• IS_OBJECT

• TYPEOF

Das Übergeben eines strukturierten Typs als Eingabeargument führt zu einem Fehler.

Verwenden des Befehls SHOW COLUMNS zum Anzeigen strukturierter Typinformationen¶

In der Ausgabe des Befehls SHOW COLUMNS enthält die Spalte data_type Informationen zu den Typen der Elemente, Schlüssel und Werte.

Verwenden der DESCRIBE- und anderer SHOW-Befehle zum Anzeigen von Informationen zu strukturierten Typen¶

Die Ausgabe der folgenden Befehle enthält Informationen zu strukturierten Typen:

• DESCRIBE TABLE

• DESCRIBE RESULT

• DESCRIBE FUNCTION

• DESCRIBE PROCEDURE

• SHOW FUNCTIONS

• SHOW PROCEDURES

In der DESCRIBE RESULT-Ausgabe enthält die Zeile für eine Spalte vom Typ MAP(VARCHAR, VARCHAR) beispielsweise den folgenden Wert in der Spalte type:

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

Die Zeile für eine ARRAY(NUMBER)-Spalte enthält den folgenden Wert in der type-Spalte:

ARRAY(NUMBER(38,0))

Anzeigen von Informationen zu den in einer Datenbank verwendeten strukturierten Typen¶

Bei Spalten mit strukturierten Typen liefert die Ansicht COLUMNS von INFORMATION_SCHEMA nur Informationen zu den Basisdatentyp der Spalte (ARRAY, OBJECT oder MAP).

Die Spalte data_type enthält zum Beispiel nur „ARRAY“, „OBJECT“ oder „MAP“. Die Spalte enthält nicht die Typen der Elemente, Schlüssel oder Werte.

Um Informationen zu den Typen der Elemente, Schlüssel und Werte anzuzeigen, verwenden Sie die folgenden Ansichten:

• Weitere Informationen zu den Typen von Elementen in strukturierten ARRAYs erhalten Sie durch Abfragen der Ansicht ELEMENT_TYPES in INFORMATION_SCHEMA oder die Ansicht ELEMENT_TYPES in ACCOUNT_USAGE.

• Weitere Informationen zu den Typen von Schlüsseln und Werten in strukturierten OBJECTs und MAPs erhalten Sie durch Abfragen der FIELDS-Ansicht in INFORMATION_SCHEMA oder der FIELDS-Ansicht in ACCOUNT_USAGE.