Strukturierte Datentypen

Ein strukturierter Typ ist ein ARRAY-, OBJECT- oder MAP-Typ, der Elemente oder Schlüssel-Wert-Paare bestimmter Snowflake-Datentypen enthält. Strukturierte Typen sind zum Beispiel:

  • Ein ARRAY-Typ mit INTEGER-Elementen

  • Ein OBJECT-Typ mit VARCHAR und NUMBER Schlüssel-Werte-Paaren

  • Ein MAP-Typ, der einen VARCHAR-Schlüssel mit einem DOUBLE-Wert verknüpft

Sie können strukturierte Typen auf folgende Weise verwenden:

  • Sie können eine Spalte für einen strukturierten Typ in einer Iceberg-Tabelle definieren.

    Die Iceberg-Datentypen list, struct und map entsprechen dem strukturierten ARRAY-Typ, dem strukturierten OBJECT-Typ und dem MAP-Typ in Snowflake.

    Eine Spalte vom strukturierten Typ unterstützt maximal 1.000 Unterspalten.

  • Sie verwenden strukturierte Typen, wenn Sie in einer Iceberg-Tabelle auf Daten in einer Spalte mit strukturiertem Typ zugreifen.

  • Sie können einen semistrukturierten ARRAY-, OBJECT- oder VARIANT-Typ in einen entsprechenden strukturierten Typ umwandeln (z. B. ein ARRAY in ein ARRAY von INTEGER-Elementen). Sie können auch einen strukturierten Typ in einen semistrukturierten Typ umwandeln.

Bemerkung

Derzeit werden strukturierte Typen nur von Iceberg-Tabellen unterstützt. Sie können einer regulären Tabelle keine Spalten eines strukturierten Typs hinzufügen.

Unter diesem Thema wird erklärt, wie strukturierte Typen in Snowflake verwendet werden.

Festlegen eines strukturierten Typs

Um eine Spalte eines strukturierten Typs zu definieren oder einen Wert in einen strukturierten Typ umzuwandeln, verwenden Sie die in den folgenden Abschnitten beschriebene Syntax:

Festlegen eines strukturierten ARRAY-Typs

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

ARRAY( <element_type> [ NOT NULL ] )
Copy

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

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

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

Verwenden von strukturierten Typen in semistrukturierten Typen

Sie können einen MAP-Typ, einen strukturierten OBJECT-Typ oder einen strukturierten ARRAY-Typ nicht in einem VARIANT-Typ, einem semistrukturierten OBJECT-Typ oder einem semistrukturierten ARRAY-Typ verwenden. In den folgenden Fällen tritt ein Fehler auf:

  • Sie verwenden einen MAP-Typ, einen strukturierten OBJECT-Typ oder einen strukturierten ARRAY-Typ in einer OBJECT-Konstante oder einer ARRAY-Konstante.

  • Sie übergeben einen MAP-Typ, einen strukturierten OBJECT-Typ oder einen strukturierten ARRAY-Typ an eine OBJECT- oder ARRAY-Konstruktorfunktion.

Konvertieren strukturierter und semistrukturierter Typen

In der folgenden Tabelle sind die Regeln für das Konvertieren von strukturierten OBJECT-Typen, strukturierten ARRAY-Typen und MAP-Typen in semistrukturierte OBJECT-Typen, ARRAY-Typen und VARIANT-Typen (und umgekehrt) zusammengefasst.

Quell-Datentyp

Ziel-Datentyp

Umwandlung

Koersion

Semistrukturierter ARRAY-Typ

Strukturierter ARRAY-Typ

Semistrukturierter OBJECT-Typ

  • Strukturierter OBJECT-Typ

  • MAP

Semistrukturierter VARIANT-Typ

  • Strukturierter ARRAY-Typ

  • Strukturierter OBJECT-Typ

  • MAP

Strukturierter ARRAY-Typ

Semistrukturierter ARRAY-Typ

  • Strukturierter OBJECT-Typ

  • MAP

Semistrukturierter OBJECT-Typ

  • Strukturierter ARRAY-Typ

  • Strukturierter OBJECT-Typ

  • MAP

Semistrukturierter VARIANT-Typ

In den folgenden Abschnitten werden diese Einschränkungen ausführlicher erläutert:

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.

Bemerkung

TRY_CAST wird für strukturierte Typen nicht unterstützt.

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

Semistrukturierte Typen

Strukturierter Typ, der umgewandelt werden kann in

ARRAY

Strukturierter ARRAY-Typ

OBJECT

MAP-Typ oder strukturierter OBJECT-Typ

VARIANT

MAP-Typ oder strukturierter ARRAY- oder OBJECT-Typ

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

Umwandeln von semistrukturierten ARRAYs und VARIANTs in strukturierte ARRAYs

Die folgenden Schritte zeigen, wie ein semistrukturierter ARRAY-Typ oder ein VARIANT-Typ in einen ARRAY-Typ von NUMBER-Elementen umgewandelt wird:

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

Oder:

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

Wenn Sie einen semistrukturierten ARRAY-Typ oder einen VARIANT-Typ in einen strukturierten ARRAY-Typ umwandeln, beachten Sie Folgendes:

  • Jedes Element des ARRAY-Typs wird in den angegebenen Typ des ARRAY-Typs umgewandelt.

    Das Umwandeln der ARRAY-Spalte in ARRAY(VARCHAR) führt zur Konvertierung jedes Werts in einen VARCHAR-Typ:

    SELECT
      CAST ([1,2,3] AS ARRAY(VARCHAR)) AS varchar_array,
      SYSTEM$TYPEOF(varchar_array) AS array_cast_type;
    
    Copy
    +---------------+-------------------------------+
    | VARCHAR_ARRAY | ARRAY_CAST_TYPE               |
    |---------------+-------------------------------|
    | [             | ARRAY(VARCHAR(16777216))[LOB] |
    |   "1",        |                               |
    |   "2",        |                               |
    |   "3"         |                               |
    | ]             |                               |
    +---------------+-------------------------------+
    
  • Wenn das Element nicht in den angegebenen Typ umgewandelt werden kann (z. B. Umwandeln von ['a', 'b', 'c'] in ARRAY(NUMBER)), schlägt die Umwandlung fehl.

  • Wenn das ARRAY-Objekt NULL-Elemente enthält und für den ARRAY-Typ NOT NULL angegeben ist (z. B. Umwandeln von [1, NULL, 3] in ARRAY(NUMBER NOT NULL), schlägt die Umwandlung fehl.

  • Elemente, die JSON-Nullwerte sind, werden in NULL umgewandelt, wenn der Zielelementtyp keine JSON-Nullen unterstützt (d. h. der Zieltyp ist kein semistrukturierter ARRAY-, OBJECT- oder VARIANT-Typ).

    Wenn Sie beispielsweise eine Umwandlung in ARRAY(NUMBER) vornehmen, werden JSON-Nullwerte in NULL umgewandelt, da der NUMBER-Typ keine JSON-Nullwerte unterstützt.

    Wenn Sie hingegen eine Umwandlung in ARRAY(VARIANT) vornehmen, werden JSON-Nullwerte nicht in NULL umgewandelt, da der VARIANT-Typ JSON-Nullwerte unterstützt.

Umwandeln von semistrukturierten OBJECTs und VARIANTs in strukturierte OBJECTs

Die folgenden Schritte zeigen, wie ein semistrukturierter OBJECT- oder VARIANT-Typ in einen strukturierten OBJECT-Typ umgewandelt wird, der die Schlüssel-Wert-Paare city und state enthält (bei denen es sich um VARCHAR-Werte handelt):

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

Oder:

SELECT
  SYSTEM$TYPEOF(
     {'city':'San Mateo','state':'CA'}::OBJECT(city VARCHAR, state VARCHAR)
  ) AS object_cast_type,
  SYSTEM$TYPEOF(
     {'city':'San Mateo','state':'CA'}::VARIANT::OBJECT(city VARCHAR, state VARCHAR)
  ) AS variant_cast_type;
Copy
+--------------------------------------------------------------+--------------------------------------------------------------+
| OBJECT_CAST_TYPE                                             | VARIANT_CAST_TYPE                                            |
|--------------------------------------------------------------+--------------------------------------------------------------|
| OBJECT(city VARCHAR(16777216), state VARCHAR(16777216))[LOB] | OBJECT(city VARCHAR(16777216), state VARCHAR(16777216))[LOB] |
+--------------------------------------------------------------+--------------------------------------------------------------+

Wenn Sie einen semistrukturierten OBJECT-Typ oder einen VARIANT-Typ in einen strukturierten OBJECT-Typ umwandeln, beachten Sie Folgendes:

  • Das OBJECT-Objekt darf keine zusätzlichen Schlüssel enthalten, die nicht im OBJECT-Typ angegeben sind.

    Wenn es zusätzliche Schlüssel gibt, schlägt die Umwandlung fehl.

  • Fehlt im OBJECT-Objekt ein Schlüssel, der im OBJECT-Typ angegeben ist, schlägt die Umwandlung fehl.

  • Der Wert jedes Schlüssels im OBJECT-Objekt wird in den für diesen Schlüssel angegebenen Typ umgewandelt.

    Wenn ein Wert nicht in den angegebenen Typ umgewandelt werden kann, schlägt die Umwandlung fehl.

  • Wenn der Wert eines Schlüssels ein JSON-Nullwert ist und der Zielwerttyp keine JSON-Nullen unterstützt (d. h. der Zieltyp ist kein semistrukturierter ARRAY-, OBJECT- oder VARIANT-Typ), wird der Wert in einen NULL-Wert konvertiert.

    Wenn Sie beispielsweise eine Umwandlung in OBJECT(city VARCHAR) vornehmen, werden JSON-Nullwerte in NULL-Werte umgewandelt, da VARCHAR keine JSON-Nullwerte unterstützt.

    Wenn Sie hingegen eine Umwandlung in OBJECT(city VARIANT) vornehmen, werden JSON-Nullwerte nicht in NULL umgewandelt, da der VARIANT-Typ JSON-Nullwerte unterstützt.

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

Die folgenden Anweisungen demonstrieren, wie ein semistrukturierter OBJECT- oder VARIANT-Typ in einen MAP-Typ umgewandelt wird, der einen VARCHAR-Schlüssel mit einem VARCHAR-Wert verknüpft:

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

Oder:

SELECT
  SYSTEM$TYPEOF(
    {'my_key':'my_value'}::MAP(VARCHAR, VARCHAR)
  ) AS map_cast_type,
  SYSTEM$TYPEOF(
    {'my_key':'my_value'}::VARIANT::MAP(VARCHAR, VARCHAR)
  ) AS variant_cast_type;
Copy
+------------------------------------------------+------------------------------------------------+
| MAP_CAST_TYPE                                  | VARIANT_CAST_TYPE                              |
|------------------------------------------------+------------------------------------------------|
| map(VARCHAR(16777216), VARCHAR(16777216))[LOB] | map(VARCHAR(16777216), VARCHAR(16777216))[LOB] |
+------------------------------------------------+------------------------------------------------+

Wenn Sie einen semistrukturierten OBJECT- oder VARIANT-Typ in einen MAP-Typ umwandeln, beachten Sie Folgendes:

  • Wenn die Schlüssel und Werte nicht mit den angegebenen Typen übereinstimmen, werden die Schlüssel und Werte in die angegebenen Typen konvertiert.

  • Wenn die Schlüssel und Werte nicht in die angegebenen Typen umgewandelt werden können, schlägt die Umwandlung fehl.

  • Wenn der Wert eines Schlüssels ein JSON-Nullwert ist und der Zielwerttyp keine JSON-Nullen unterstützt (d. h. der Zieltyp ist kein semistrukturierter ARRAY-, OBJECT- oder VARIANT-Typ), wird der Wert in einen NULL-Wert konvertiert.

    Wenn Sie beispielsweise eine Umwandlung in MAP(VARCHAR, VARCHAR) vornehmen, werden JSON-Nullwerte in NULL umgewandelt, da der VARCHAR-Typ keine JSON-Nullwerte unterstützt.

    Wenn Sie hingegen eine Umwandlung in MAP(VARCHAR, VARIANT) vornehmen, werden JSON-Nullwerte nicht in NULL umgewandelt, da der VARIANT-Typ JSON-Nullwerte unterstützt.

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.

Bemerkung

TRY_CAST wird bei strukturierten Typen nicht unterstützt.

Strukturierter Typ

Semistrukturierter Typ, der umgewandelt werden kann in

Strukturierter ARRAY-Typ

ARRAY

MAP-Typ oder strukturierter OBJECT-Typ

OBJECT

MAP-, strukturierter ARRAY- oder strukturierter OBJECT-Typ

VARIANT

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:

Bemerkung

TRY_CAST wird bei strukturierten Typen nicht unterstützt.

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.

Beispiel: Umwandeln eines ARRAY-Typs in einen anderen

Im folgenden Beispiel wird ein ARRAY(NUMBER)-Typ in einen ARRAY(VARCHAR)-Typ umgewandelt:

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

Beispiel: Ändern der Reihenfolge von Schlüssel-Wert-Paaren in einem OBJECT-Typ

Im folgenden Beispiel wird die Reihenfolge der Schlüssel-Wert-Paare in einem strukturierten OBJECT-Typ geändert:

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

Beispiel: Ändern der Schlüsselnamen eines OBJECT-Typs

Um die Schlüsselnamen in einem strukturierten OBJECT-Typ zu ändern, geben Sie die Schlüsselwörter RENAME FIELDS am Ende von CAST an. Beispiel:

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

Beispiel: Hinzufügen von Schlüsseln zu einem OBJECT-Typ

Wenn der Typ, in den umgewandelt wird, zusätzliche Schlüssel-Wert-Paare hat, die im ursprünglichen strukturierten Objekt nicht vorhanden sind, geben Sie die Schlüsselwörter ADD FIELDS am Ende von CAST an. Beispiel:

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

Die Werte für die neu hinzugefügten Schlüssel werden auf NULL gesetzt. Wenn Sie diesen Schlüsseln einen Wert zuweisen möchten, rufen Sie stattdessen die Funktion OBJECT_INSERT auf.

Erstellen von strukturierten ARRAY-Typen, strukturierten OBJECT-Typen und MAP-Typen

In den folgenden Abschnitten wird erklärt, wie Sie strukturierte ARRAY-Typen, strukturierte OBJECT-Typen und MAP-Typen erstellt werden.

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

Mit den folgenden Funktionen werden semistrukturierte ARRAY-Typen erstellt:

Mit den folgenden Funktionen werden semistrukturierte OBJECT-Typen erstellt:

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

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

Bemerkung

Sie können diesen Funktionen keine strukturierten ARRAY-Typen, strukturierten OBJECT-Typen oder MAP-Typen übergeben. Dies würde dazu führen, dass ein strukturierter Typ implizit in einen semistrukturierten Typ umgewandelt wird, was nicht zulässig ist (wie unter Implizites Umwandeln eines Wertes (Koersion) erwähnt).

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

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
);
Copy
SELECT {
  'city': 'San Mateo',
  'state': 'CA'
}::MAP(
  VARCHAR,
  VARCHAR
);
Copy

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

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

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

Verwenden von Schlüsseln, Werten und Elementen in strukturierten Typen

In den folgenden Abschnitten wird erklärt, wie Werte und Elemente in strukturierten Typen verwendet werden.

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

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

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:

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

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

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

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

Bemerkung

Verwenden Sie für beide Funktionen ein Element von einem Typ, der vergleichbar ist mit dem Typ des ARRAY.

Der Ausdruck für das Element darf nicht in ein VARIANT umgewandelt werden.

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);
Copy
SELECT MAP_CONTAINS_KEY(10, my_map);
Copy

Vergleichen von Objekten

In den folgenden Abschnitten wird erläutert, wie Sie dies tun können:

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

größer als:

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

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

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 Typen

In den folgenden Abschnitten wird erklärt, wie strukturierte ARRAYs, strukturierte OBJECTs und MAPs transformiert werden:

Transformieren von strukturierten ARRAYs

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

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

Funktionen, die Elemente zu Arrays hinzufügen

Die folgenden Funktionen fügen Elemente zu einem ARRAY hinzu:

Bei diesen Funktionen muss der Typ des Elements implizit umwandelbar (Koersion) in den ARRAY-Typ sein.

Der folgende Aufruf ist zum Beispiel erfolgreich, weil NUMBER implizit in DOUBLE (dem Typ des ARRAY) umgewandelt werden kann:

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

Der folgende Aufruf ist erfolgreich, weil VARCHAR implizit in DOUBLE umgewandelt werden kann:

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

Der folgende Aufruf schlägt fehl, weil DATE nicht implizit in NUMBER umgewandelt werden kann:

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

Funktionen, die mehrere ARRAYs als Eingabe akzeptieren

Die folgenden Funktionen akzeptieren mehrere ARRAYs als Eingabeargumente:

Wenn Sie diese Funktionen aufrufen, müssen beide Argumente entweder strukturierte ARRAYs oder semistrukturierte ARRAYs sein. Die folgenden Aufrufe schlagen beispielsweise fehl, weil ein Argument ein strukturiertes ARRAY und das andere Argument ein semistrukturiertes ARRAY ist:

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

Die Funktion ARRAY_EXCEPT gibt ein ARRAY zurück, das vom gleichen Typ ist wie das ARRAY des ersten Arguments.

Die Funktionen ARRAY_CAT und ARRAY_INTERSECTION geben ein ARRAY von einem Typ zurück, der beide Eingabetypen aufnehmen kann.

Der folgende Aufruf von ARRAY_CAT übergibt zum Beispiel zwei strukturierte ARRAYs:

  • Das erste strukturierte ARRAY lässt NULL-Werte nicht zu und enthält NUMBER-Werte mit der Dezimalstellenzahl von 0. (NUMBER ist NUMBER(38, 0).)

  • Das zweite strukturierte ARRAY enthält einen NULL-Wert und eine Zahl, die eine Dezimalstellenzahl von 1 hat.

Das von ARRAY_CAT zurückgegebene ARRAY erlaubt NULL-Werte und enthält NUMBER-Werte mit der Dezimalstellenzahl von 1.

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

Bei der Funktion ARRAY_CAT muss das ARRAY im zweiten Argument implizit umwandelbar in den Typ des ersten Arguments sein.

Bei den Funktionen ARRAY_EXCEPT und ARRAY_INTERSECTION muss das ARRAY des zweiten Arguments vergleichbar sein mit dem ARRAY des ersten Arguments sein.

Der folgende Aufruf ist zum Beispiel erfolgreich, weil ARRAY(NUMBER) mit ARRAY(DOUBLE) vergleichbar ist:

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

Der folgende Aufruf schlägt fehl, weil ARRAY(NUMBER) nicht mit ARRAY(VARCHAR) vergleichbar ist:

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

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:

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.

Entfernen von Schlüssel-Wert-Paaren

Um ein neues Objekt zurückzugeben, das die Schlüssel-Wert-Paare eines bestehenden Objekts enthält, wobei bestimmte Schlüssel-Wert-Paare entfernt werden, rufen Sie die Funktion OBJECT_DELETE auf.

Beim Aufrufen dieser Funktion ist Folgendes zu beachten:

  • Für die Argumente, die Schlüssel sind, müssen Sie Konstanten angeben.

  • Wenn der angegebene Schlüssel nicht Teil der OBJECT-Typdefinition ist, schlägt der Aufruf fehl. Der folgende Aufruf schlägt zum Beispiel fehl, weil OBJECT nicht den angegebenen Schlüssel zip_code enthält:

    SELECT OBJECT_DELETE( {'city':'San Mateo','state':'CA'}::OBJECT(city VARCHAR,state VARCHAR), 'zip_code' );
    
    Copy
    093201 (23001): Function OBJECT_DELETE: expected structured object to contain field zip_code but it did not.
    
  • Die Funktion gibt ein strukturiertes OBJECT zurück. Der Typ des OBJECT schließt den gelöschten Schlüssel aus. Angenommen, Sie entfernen den Schlüssel city:

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

    Die Funktion gibt ein OBJECT vom Typ OBJECT(state VARCHAR) zurück, das den Schlüssel city nicht enthält.

    +-----------------+--------------------------------------+
    | NEW_OBJECT      | SYSTEM$TYPEOF(NEW_OBJECT)            |
    |-----------------+--------------------------------------|
    | {               | OBJECT(state VARCHAR(16777216))[LOB] |
    |   "state": "CA" |                                      |
    | }               |                                      |
    +-----------------+--------------------------------------+
    
  • Wenn die Funktion alle Schlüssel aus dem Objekt entfernt, gibt die Funktion ein leeres strukturiertes OBJECT vom Typ OBJECT() zurück.

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

    Wenn der Typ eines strukturierten OBJECT Schlüssel-Wert-Paare enthält, werden die Namen und Typen dieser Paare in Klammern in den Typ aufgenommen (zum Beispiel OBJECT(Stadt VARCHAR)). Da ein leeres strukturiertes OBJECT keine Schlüssel-Wert-Paare enthält, sind die Klammern leer.

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

Um ein neues Objekt zurückzugeben, das die Schlüssel-Wert-Paare eines bestehenden Objekts mit zusätzlichen Schlüssel-Wert-Paaren oder neuen Werten für Schlüssel enthält, rufen Sie die Funktion OBJECT_INSERT auf.

Beim Aufrufen dieser Funktion ist Folgendes zu beachten:

  • Für die Argumente, die Schlüssel sind, müssen Sie Konstanten angeben.

  • Wenn das Argument updateFlag den Wert FALSE hat (wenn Sie ein neues Schlüssel-Wert-Paar einfügen):

    • Wenn Sie einen Schlüssel angeben, der bereits in OBJECT vorhanden ist, tritt ein Fehler auf.

      SELECT OBJECT_INSERT(
        {'city':'San Mateo','state':'CA'}::OBJECT(city VARCHAR,state VARCHAR),
        'city',
        'San Jose',
        false
      );
      
      Copy
      093202 (23001): Function OBJECT_INSERT:
        expected structured object to not contain field city but it did.
      
    • Die Funktion gibt ein strukturiertes OBJECT zurück. Der Typ von OBJECT enthält den neu eingefügten Schlüssel. Angenommen, Sie fügen den Schlüssel zipcode mit dem DOUBLE-Wert 94402 hinzu:

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

      Die Funktion liefert ein OBJECT vom Typ OBJECT(city VARCHAR, state VARCHAR, zipcode DOUBLE):

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

      Der Typ des eingefügten Wertes bestimmt den Typ, der der OBJECT-Typdefinition hinzugefügt wird. In diesem Fall ist der Wert für zipcode ein in DOUBLE umgewandelter Wert, sodass zipcode den Typ DOUBLE hat.

  • Wenn das Argument updateFlag den Wert TRUE hat (wenn Sie ein vorhandenes Schlüssel-Wert-Paar ersetzen):

    • Wenn Sie einen Schlüssel angeben, der in OBJECT nicht vorhanden ist, tritt ein Fehler auf.

    • Die Funktion gibt ein strukturiertes OBJECT desselben Typs zurück.

    • Der Typ des eingefügten Wertes wird implizit umgewandelt (Koersion) in den Typ des vorhandenen Schlüssels.

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

Um ein neues Objekt zurückzugeben, das ausgewählte Schlüssel-Wert-Paare eines vorhandenen Objekts enthält, rufen Sie die Funktion OBJECT_PICK auf.

Beim Aufrufen dieser Funktion ist Folgendes zu beachten:

  • Für die Argumente, die Schlüssel sind, müssen Sie Konstanten angeben.

  • Sie können kein ARRAY von Schlüsseln als zweites Argument übergeben. Sie müssen jeden Schlüssel als separates Argument angeben.

  • Die Funktion gibt ein strukturiertes OBJECT zurück. Der Typ von OBJECT enthält die Schlüssel in der Reihenfolge, in der sie angegeben sind.

    Angenommen, Sie wählen die Schlüssel state und city in dieser Reihenfolge aus:

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

    Die Funktion gibt ein OBJECT vom Typ OBJECT(state VARCHAR, city VARCHAR) zurück.

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

Transformieren von MAPs

Um MAPs zu transformieren, verwenden Sie die folgenden Funktionen:

Verwenden von strukturierten Typen

In den folgenden Abschnitten wird erklärt, wie verschiedene SQL-Funktionen und Mengenoperatoren mit Objekten strukturierter Typen verwendet:

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:

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:

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

Zugriff auf strukturierte Typen in Anwendungen mit Treibern

In Anwendungen, die Treiber verwenden (z. B. den ODBC- oder JDBC-Treiber, den Snowflake-Konnektor für Python usw.), werden strukturierte Typwerte als semistrukturierte Typen zurückgegeben. Beispiel:

  • Eine strukturierte ARRAY-Spalte wird als semistrukturierte ARRAY-Spalte an die Clientanwendung zurückgegeben.

  • Eine strukturierte OBJECT- oder MAP-Spalte wird als semistrukturiertes OBJECT an die Clientanwendung zurückgegeben.

Verwenden strukturierter Typen mit benutzerdefinierten Funktionen (UDFs) und gespeicherten Prozeduren

Wenn Sie eine benutzerdefinierte Funktion (UDF), eine benutzerdefinierte Tabellenfunktion (UDTF) oder eine gespeicherte Prozedur in SQL oder Snowflake Scripting erstellen, können Sie strukturierte Typen in den Argumenten und Rückgabewerten verwenden. Beispiel:

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

Bemerkung

Strukturierte Typen werden in UDFs, UDTFs und gespeicherten Prozeduren anderer Sprachen (Java, JavaScript, Python und Scala) noch nicht unterstützt.

Anzeigen von Informationen zu strukturierten Typen

In den folgenden Abschnitten werden die Ansichten und Befehle beschrieben, die Sie verwenden können, um Informationen zu strukturierte Typen anzuzeigen:

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:

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

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

ARRAY(NUMBER(38,0))
Copy

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: