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

  Bei der Definition einer Iceberg-Tabellenspalte können Sie keinen VARIANT-Typ, keinen semistrukturierten ARRAYTyp oder 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-Wert in einen strukturierten ARRAY-Wert um (ein ARRAY von NUMBER-Elementen).

• Der zweite Spaltenausdruck gibt einen semistrukturierten ARRAY-Wert 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.

  • Jede key in einer OBJECT-Definition muss eindeutig sein.

  • Die Reihenfolge der Schlüssel ist Teil der OBJECT-Definition. Der Vergleich von zwei OBJECT-Werten, die dieselben Schlüssel in unterschiedlicher Reihenfolge haben, ist nicht erlaubt. (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-Wert in einen strukturierten OBJECT-Wert 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-Wert 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) NOT NULL, num NUMBER(38,0))[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.

  Zuordnungsschlüssel dürfen nicht NULL sein.

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

Das folgende Beispiel wandelt einen semistrukturierten OBJECT-Wert in einen MAP-Wert um und verwendet die Funktion SYSTEM$TYPEOF, um den resultierenden Typ des Wertes 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 einen Wert eines semistrukturierten Typs explizit in einen Wert eines strukturierten Typs umzuwandeln, können Sie die Funktion CAST aufrufen oder den :: Operator verwenden.

Sie können nur Werte der folgenden semistrukturierten Typen in Werte des entsprechenden strukturierten Typs 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 ARRAY- und VARIANT-Werten in strukturierte ARRAY-Werte

• Umwandeln von semistrukturierten OBJECT- und VARIANT-Werten in strukturierte OBJECT-Werte

• Umwandeln von semistrukturierten OBJECT- und VARIANT-Werten in MAP-Werte

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 Wert eines strukturierten Typs explizit in einen Wert eines semistrukturierten Typs umzuwandeln, können Sie die Funktion CAST aufrufen, den :: Operator verwenden oder eine der Konvertierungsfunktionen aufrufen (zum Beispiel TO_ARRAY, TO_OBJECT oder TO_VARIANT).

Beispiel:

• Wenn col_structured_array vom Typ ARRAY (VARCHAR) ist:

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

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

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

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

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

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

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

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

Beachten Sie Folgendes:

• Wenn Sie in einen semistrukturierten OBJECT-Wert umwandeln, wird die Reihenfolge der Schlüssel im strukturierten OBJECT-Wert nicht beibehalten.

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

  Wenn Sie einen strukturierten ARRAY-Wert in einen VARIANT-Wert 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-Wert umwandeln, der einen NUMBER-Typ für Schlüssel verwendet, werden die MAP-Schlüssel im zurückgegebenen OBJECT-Wert in Zeichenfolgen konvertiert.

Implizites Umwandeln eines Wertes (Koersion)¶

Die folgenden Regeln gelten für implizites Umwandeln (Koersion) von einem Wert eines strukturierten Typs in einem Wert eines anderen strukturierten Typs:

• Ein Wert eines strukturierten Typs kann in einen Wert eines anderen strukturierten Typs umgewandelt werden, wenn die beiden Basistypen identisch sind:

  • Ein ARRAY-Wert kann in einen anderen ARRAY-Wert 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)-Wert kann in einen ARRAY (DOUBLE)-Wert umgewandelt werden.

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

  • Ein OBJECT-Wert mit einer Typdefinition kann nur dann in einen OBJECT-Wert 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-Werten kann der Typ eines Werts 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)-Wert kann in einen Wert OBJECT(city VARCHAR, zipcode DOUBLE) umgewandelt werden.

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

  • Ein MAP-Wert mit einem Werttyp kann in einen MAP-Wert mit einem anderen Werttyp umgewandelt werden, wenn:

    • 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)-Wert in einen MAP(VARCHAR, DOUBLE)-Wert implizit umgewandelt werden.

  • Ein MAP-Wert mit einem Schlüsseltyp kann in einen MAP-Wert 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)-Wert nicht in einen MAP (NUMBER, NUMBER)-Wert implizit umgewandelt werden.

• Ein Wert eines strukturierten Typs kann nicht in einen Wert eines semstrukturierten Typs implizit umgewandelt werden (und umgekehrt).

• Ein VARCHAR-Wert kann nicht in einen Wert eines strukturierten Typs implizit umgewandelt werden.

Umwandeln von einem strukturierten Typ in einen anderen¶

Sie können die Funktion CAST aufrufen oder den :: Operator verwenden, um von einem Wert eines strukturierten Typs in einen Wert eines anderen strukturierten Typs umzuwandeln. Sie können Werte von und in die folgenden strukturierten Typen umwandeln:

• Bei strukturierten ARRAY-Typen:

  Sie können einen ARRAY-Wert eines Typs in einen ARRAY-Wert eines anderen Typs umwandeln.

• Bei strukturierten OBJECT-Typen:

  • Sie können eine Umwandlung verwenden, um die Reihenfolge der Schlüssel-Wert-Paare in einem OBJECT-Wert zu ändern.

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

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

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

• Bei MAP-Werten:

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

  • Sie können einen MAP-Wert in einen strukturierten OBJECT-Wert 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)-Wert in einen ARRAY (DATE)-Wert umzuwandeln.

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

+------------+
| CAST_ARRAY |
|------------|
| [          |
|   "1",     |
|   "2",     |
|   "3"      |
| ]          |
+------------+

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

+-----------------------+
| OBJECT_VALUE_ORDER    |
|-----------------------|
| {                     |
|   "state": "CA",      |
|   "city": "San Mateo" |
| }                     |
+-----------------------+

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

+-----------------------------+
| OBJECT_VALUE_KEY_NAMES      |
|-----------------------------|
| {                           |
|   "city_name": "San Mateo", |
|   "state_name": "CA"        |
| }                           |
+-----------------------------+

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

+------------------------+
| ADD_FIELDS             |
|------------------------|
| {                      |
|   "city": "San Mateo", |
|   "state": "CA",       |
|   "zipcode": null      |
| }                      |
+------------------------+

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

Mit den folgenden Funktionen werden semistrukturierte ARRAY-Werte erstellt:

• ARRAY_CONSTRUCT

• ARRAY_CONSTRUCT_COMPACT

• ARRAY_AGG

• TO_ARRAY

Mit den folgenden Funktionen werden semistrukturierte OBJECT-Werte erstellt:

• OBJECT_CONSTRUCT

• OBJECT_CONSTRUCT_KEEP_NULL

• OBJECT_AGG

• TO_OBJECT

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

SELECT ARRAY_CONSTRUCT(10, 20, 30)::ARRAY(NUMBER);

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

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-Werten¶

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

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

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

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

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

Erstellen eines MAP-Werts¶

Um einen MAP-Wert zu erstellen, erstellen Sie einen semistrukturierten OBJECT-Wert und wandeln den OBJECT-Wert in einen MAP-Wert um.

Die folgenden Anweisungen erstellen zum Beispiel beide den Wert 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
);

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

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

Weitere Informationen dazu finden Sie unter Umwandeln von semistrukturierten OBJECT- und VARIANT-Werten in MAP-Werte.

Abrufen der Liste der Schlüssel aus einem strukturierten OBJECT-Wert¶

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

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

Wenn die Eingabe ein strukturierter OBJECT-Wert ist, gibt die Funktion einen ARRAY (VARCHAR)-Wert zurück, der die Schlüssel enthält. Wenn die Eingabe ein semistrukturierter OBJECT-Wert ist, gibt die Funktion einen ARRAY-Wert zurück.

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

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

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

Zugriff auf Werte und Elemente aus Werten von strukturierten Typen¶

Sie können die folgenden Methoden verwenden, um auf Werte und Elemente von strukturierten ARRAY-, strukturierten OBJECT- und MAP-Werten zuzugreifen:

• Funktion GET

• Funktion GET_IGNORE_CASE

• Funktion GET_PATH

• Punktierte Schreibweise

• Klammernotation

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

Das folgende Beispiel übergibt das erste Element eines semistrukturierten ARRAY-Werts und einen ARRAY (VARCHAR)-Wert 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 einen strukturierten OBJECT-Wert 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 einen MAP- oder strukturierten ARRAY-Wert an die Funktion GET übergeben.

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

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

• Wenn Sie für einen strukturierten OBJECT-Wert 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 OBJECT-Wert nicht vorhanden ist, gibt die Funktion NULL zurück.

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

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

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

Bestimmen der Größe eines MAP-Werts¶

Um die Größe eines MAP-Werts zu bestimmen, übergeben Sie den MAP-Wert an die Funktion MAP_SIZE:

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

Suchen von Elementen in einem strukturierten ARRAY-Wert¶

Um festzustellen, ob ein Element in einem strukturierten ARRAY-Wert 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-Wert zu bestimmen, rufen Sie die Funktion ARRAY_POSITION auf. Beispiel:

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

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

Um festzustellen, ob ein MAP-Wert 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);

Vergleich von strukturierten Werten mit semistrukturierten Werten¶

Sie können einen strukturierten ARRAY-, strukturierten OBJECT- oder MAP-Wert nicht mit einem semistrukturierten ARRAY-, OBJECT-, oder VARIANT-Wert vergleichen.

Vergleich von strukturierten Werten mit anderen strukturierten Werten¶

Sie können zwei Werte desselben Typs vergleichen (z. B. zwei strukturierte ARRAY-Werte, zwei strukturierte OBJECT-Werte oder zwei MAP-Werte).

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

• =

• !=

• <

• <=

• >=

• >

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

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

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

Wenn Sie zwei strukturierte Werte mit <, <=, >= oder > vergleichen, werden die strukturierten Wertfelder in alphabetischer Reihenfolge verglichen. Zum Beispiel der folgende Wert:

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

größer als:

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

Feststellen, ob sich zwei ARRAY-Werte überlappen¶

Um festzustellen, ob sich die Elemente von zwei strukturierten ARRAY-Werten überlappen, rufen Sie die Funktion ARRAYS_OVERLAP auf. Beispiel:

SELECT ARRAYS_OVERLAP(numeric_array, other_numeric_array);

Die ARRAY-Werte müssen von vergleichbaren Typen sein.

Sie können dieser Funktion nicht einen semistrukturierten ARRAY-Wert und einen strukturierten ARRAY-Wert übergeben. Beide ARRAY-Werte müssen entweder strukturiert oder semistrukturiert sein.

Transformieren von strukturierten ARRAY-Werten¶

Wenn Sie einen strukturierten ARRAY-Wert an diese Funktionen übergeben, geben die Funktionen einen strukturierten ARRAY-Wert 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 ARRAY-Werten funktionieren.

• Funktionen, die Elemente zu ARRAY-Werten hinzufügen

• Funktionen, die mehrere ARRAY-Werte 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 OBJECT-Werten¶

In den folgenden Abschnitten wird erklärt, wie ein strukturierter OBJECT-Wert zurückgegeben wird, der aus einem anderen OBJECT-Wert 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

Um die Reihenfolge der Schlüssel-Wert-Paare zu ändern, Schlüssel umzubenennen oder Schlüssel ohne Angabe von Werten hinzuzufügen, verwenden Sie die Funktion CAST oder den Operator ::. Weitere Details dazu finden Sie unter Umwandeln von einem strukturierten Typ in einen anderen.

Transformieren von MAP-Werten¶

Um MAP-Werte zu transformieren, verwenden Sie die folgenden Funktionen:

• MAP_CAT

• MAP_DELETE

• MAP_INSERT

• MAP_PICK

Verwenden der Funktion FLATTEN mit Werten von strukturierten Typen¶

Sie können strukturierte ARRAY-, strukturierte OBJECT-, und MAP-Werte 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 strukturierter ARRAY-Wert ist und das Argument RECURSIVE den Wert FALSE hat, enthält die Spalte value einen Wert desselben Typs wie der ARRAY-Wert.

  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-Wert ist und das Argument RECURSIVE FALSE ist, 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 MAP-Werten ist die Reihenfolge der zurückgegebenen Schlüssel und Werte unbestimmt.

Verwenden der Funktion PARSE_JSON¶

Die Funktion PARSE_JSON gibt keine strukturierten Typen zurück.

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

Sie können strukturierte ARRAY-, strukturierte OBJECT- und MAP-Werte für Folgendes 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-Werte als Eingabeargument:

• AS_ARRAY

• AS_OBJECT

• IS_ARRAY

• IS_OBJECT

• TYPEOF

Die Übergabe eines Werts von einem strukturierten Typ als Eingabe 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:

• Informationen zu den Elementtypen in strukturierten ARRAY-Typen erhalten Sie durch Abfragen der Ansicht ELEMENT_TYPES in INFORMATION_SCHEMA oder der Ansicht ELEMENT_TYPES in ACCOUNT_USAGE.

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