Kategorien:: Funktionen für semistrukturierte und strukturierte Daten (Extraktion)

GET_PATH, `:`¶

Extrahiert einen Wert aus semistrukturierten Daten unter Verwendung eines Pfadnamens.

GET_PATH ist eine Variante von GET, die den Namen einer VARIANT-, OBJECT- oder ARRAY-Spalte als erstes Argument verwendet und den Wert des Felds oder Elements anhand des Pfadnamens extrahiert, der als zweites Argument angegeben wurde.

Syntax¶

GET_PATH( <column_identifier> , '<path_name>' )

<column_identifier>:<path_name>

:( <column_identifier> , '<path_name>' )

Argumente¶

column_identifier

Ein Ausdruck, der eine VARIANT-, OBJECT- oder ARRAY-Spalte ergibt.

path_name

Ein Ausdruck, der einen VARCHAR-Wert ergibt. Dieser Wert gibt den Pfad zu dem Feld oder Element an, das Sie extrahieren möchten.

Bei strukturierte Typen müssen Sie eine Zeichenfolgenkonstante angeben.

Rückgabewerte¶

Der zurückgegebene Wert ist das angegebene Element im ARRAY oder der Wert, der dem angegebenen Schlüssel eines Schlüssel-Wert-Paares in dem OBJECT entspricht.
Wenn das Eingabeobjekt ein semistrukturierter OBJECT-, ARRAY- oder VARIANT-Wert ist, gibt die Funktion einen VARIANT-Wert zurück. Der Datentyp des Wertes ist VARIANT, weil:
- In einem ARRAY-Wert jedes Element vom Typ VARIANT ist.
- In einem OBJECT-Wert der Wert in jedem Schlüssel-Wert-Paar vom Typ VARIANT ist.
Wenn das Eingabeobjekt ein strukturiertes OBJECT-Objekt, ein strukturiertes ARRAY-Objekt oder ein MAP-Objekt ist, gibt die Funktion einen Wert des Typs zurück, der für das Objekt angegeben ist.

Wenn der Typ des Eingabeobjekts zum Beispiel ARRAY(NUMBER) ist, gibt die Funktion den Wert NUMBER zurück.

Nutzungshinweise¶

GET_PATH entspricht einer Kette von GET-Funktionen. Es wird NULL zurückgegeben, wenn der Pfadname keinem Element entspricht.
Die Syntax für Pfadnamen besteht aus standardmäßiger JavaScript-Notation. Sie beinhaltet eine Verkettung von Feldnamen (Bezeichnern), denen Punkte (z. B. .) und Indexoperatoren (z. B. [<Index>]) vorangestellt sind:
- Beim ersten Feldnamen muss der führende Punkt nicht angegeben werden.
- Die Indexwerte in den Indexoperatoren können nicht negative Dezimalzahlen (bei Arrays) bzw. Zeichenfolgenliterale in einfachen oder doppelten Anführungszeichen sein (bei Objektfeldern).
Weitere Details dazu finden Sie unter Abfragen von semistrukturierten Daten.
Außerdem unterstützt GET_PATH eine syntaktische Verknüpfung mit dem Zeichen : als Extraktionsoperator, der den Spaltennamen (der Punkte enthalten kann) vom Pfadbezeichner trennt.

Zur Wahrung der syntaktischen Konsistenz unterstützt die Pfadnotation auch Bezeichner in SQL-artigen doppelten Anführungszeichen sowie die Verwendung von : als Pfadtrennzeichen.

Wenn der Operator : verwendet wird, können beliebige Ganzzahl- oder Zeichenfolge-Unterausdrücke in [] aufgenommen werden.

Beispiele¶

Erstellen Sie eine Tabelle mit einer VARIANT-Spalte und fügen Sie Daten ein. Verwenden Sie die Funktion PARSE_JSON, um die VARIANT-Daten einzufügen. Die VARIANT-Werte enthalten verschachtelte ARRAY-Werte und OBJECT-Werte.

CREATE OR REPLACE TABLE get_path_demo(
  id INTEGER,
  v  VARIANT);

INSERT INTO get_path_demo (id, v)
  SELECT 1,
         PARSE_JSON('{
           "array1" : [
             {"id1": "value_a1", "id2": "value_a2", "id3": "value_a3"}
           ],
           "array2" : [
             {"id1": "value_b1", "id2": "value_b2", "id3": "value_b3"}
           ],
           "object_outer_key1" : {
             "object_inner_key1a": "object_x1",
             "object_inner_key1b": "object_x2"
           }
         }');

INSERT INTO get_path_demo (id, v)
  SELECT 2,
         PARSE_JSON('{
           "array1" : [
             {"id1": "value_c1", "id2": "value_c2", "id3": "value_c3"}
           ],
           "array2" : [
             {"id1": "value_d1", "id2": "value_d2", "id3": "value_d3"}
           ],
           "object_outer_key1" : {
             "object_inner_key1a": "object_y1",
             "object_inner_key1b": "object_y2"
           }
         }');

SELECT * FROM get_path_demo;

+----+----------------------------------------+
| ID | V                                      |
|----+----------------------------------------|
|  1 | {                                      |
|    |   "array1": [                          |
|    |     {                                  |
|    |       "id1": "value_a1",               |
|    |       "id2": "value_a2",               |
|    |       "id3": "value_a3"                |
|    |     }                                  |
|    |   ],                                   |
|    |   "array2": [                          |
|    |     {                                  |
|    |       "id1": "value_b1",               |
|    |       "id2": "value_b2",               |
|    |       "id3": "value_b3"                |
|    |     }                                  |
|    |   ],                                   |
|    |   "object_outer_key1": {               |
|    |     "object_inner_key1a": "object_x1", |
|    |     "object_inner_key1b": "object_x2"  |
|    |   }                                    |
|    | }                                      |
|  2 | {                                      |
|    |   "array1": [                          |
|    |     {                                  |
|    |       "id1": "value_c1",               |
|    |       "id2": "value_c2",               |
|    |       "id3": "value_c3"                |
|    |     }                                  |
|    |   ],                                   |
|    |   "array2": [                          |
|    |     {                                  |
|    |       "id1": "value_d1",               |
|    |       "id2": "value_d2",               |
|    |       "id3": "value_d3"                |
|    |     }                                  |
|    |   ],                                   |
|    |   "object_outer_key1": {               |
|    |     "object_inner_key1a": "object_y1", |
|    |     "object_inner_key1b": "object_y2"  |
|    |   }                                    |
|    | }                                      |
+----+----------------------------------------+

Extrahieren Sie den Wert id3 aus array2 in jeder Zeile:

SELECT id,
       GET_PATH(
         v,
         'array2[0].id3') AS id3_in_array2
  FROM get_path_demo;

+----+---------------+
| ID | ID3_IN_ARRAY2 |
|----+---------------|
|  1 | "value_b3"    |
|  2 | "value_d3"    |
+----+---------------+

Verwenden Sie den Operator :, um in jeder Zeile denselben id3-Wert aus array2 zu extrahieren:

SELECT id,
       v:array2[0].id3 AS id3_in_array2
  FROM get_path_demo;

+----+---------------+
| ID | ID3_IN_ARRAY2 |
|----+---------------|
|  1 | "value_b3"    |
|  2 | "value_d3"    |
+----+---------------+

Dieses Beispiel ist dasselbe wie das vorherige, verwendet aber SQL-artige Bezeichner in Anführungszeichen:

SELECT id,
       v:"array2"[0]."id3" AS id3_in_array2
  FROM get_path_demo;

+----+---------------+
| ID | ID3_IN_ARRAY2 |
|----+---------------|
|  1 | "value_b3"    |
|  2 | "value_d3"    |
+----+---------------+

Extrahieren Sie den Wert object_inner_key1a aus dem verschachtelten OBJECT-Wert in jeder Zeile:

SELECT id,
       GET_PATH(
         v,
         'object_outer_key1:object_inner_key1a') AS object_inner_key1A_values
  FROM get_path_demo;

+----+---------------------------+
| ID | OBJECT_INNER_KEY1A_VALUES |
|----+---------------------------|
|  1 | "object_x1"               |
|  2 | "object_y1"               |
+----+---------------------------+

Verwenden Sie den :-Operator, um die gleichen object_inner_key1a-Werte zu extrahieren:

SELECT id,
       v:object_outer_key1.object_inner_key1a AS object_inner_key1a_values
  FROM get_path_demo;

+----+---------------------------+
| ID | OBJECT_INNER_KEY1A_VALUES |
|----+---------------------------|
|  1 | "object_x1"               |
|  2 | "object_y1"               |
+----+---------------------------+