Abfragen von semistrukturierten Daten

Snowflake unterstützt SQL-Abfragen, die mit speziellen Operatoren und Funktionen auf semistrukturierte Daten zugreifen. Beachten Sie, dass dieses Thema nur auf JSON-, Avro-, ORC- und Parquet-Daten angewendet werden kann, aber nicht auf XML-Daten.

Unter diesem Thema:

In Beispielen verwendete Beispieldaten

Sofern nicht anders angegeben, beziehen sich die hier verwendeten Beispiele auf eine Tabelle mit dem Namen car_sales, die nur aus einer VARIANT-Spalte mit dem Namen src besteht:

CREATE OR REPLACE TABLE car_sales
(
  src variant
)
AS
SELECT PARSE_JSON(column1) AS src
FROM VALUES
('{
    "date" : "2017-04-28",
    "dealership" : "Valley View Auto Sales",
    "salesperson" : {
      "id": "55",
      "name": "Frank Beasley"
    },
    "customer" : [
      {"name": "Joyce Ridgely", "phone": "16504378889", "address": "San Francisco, CA"}
    ],
    "vehicle" : [
      {"make": "Honda", "model": "Civic", "year": "2017", "price": "20275", "extras":["ext warranty", "paint protection"]}
    ]
}'),
('{
    "date" : "2017-04-28",
    "dealership" : "Tindel Toyota",
    "salesperson" : {
      "id": "274",
      "name": "Greg Northrup"
    },
    "customer" : [
      {"name": "Bradley Greenbloom", "phone": "12127593751", "address": "New York, NY"}
    ],
    "vehicle" : [
      {"make": "Toyota", "model": "Camry", "year": "2017", "price": "23500", "extras":["ext warranty", "rust proofing", "fabric protection"]}
    ]
}') v;

SELECT * FROM car_sales;

+-------------------------------------------+
| SRC                                       |
|-------------------------------------------|
| {                                         |
|   "customer": [                           |
|     {                                     |
|       "address": "San Francisco, CA",     |
|       "name": "Joyce Ridgely",            |
|       "phone": "16504378889"              |
|     }                                     |
|   ],                                      |
|   "date": "2017-04-28",                   |
|   "dealership": "Valley View Auto Sales", |
|   "salesperson": {                        |
|     "id": "55",                           |
|     "name": "Frank Beasley"               |
|   },                                      |
|   "vehicle": [                            |
|     {                                     |
|       "extras": [                         |
|         "ext warranty",                   |
|         "paint protection"                |
|       ],                                  |
|       "make": "Honda",                    |
|       "model": "Civic",                   |
|       "price": "20275",                   |
|       "year": "2017"                      |
|     }                                     |
|   ]                                       |
| }                                         |
| {                                         |
|   "customer": [                           |
|     {                                     |
|       "address": "New York, NY",          |
|       "name": "Bradley Greenbloom",       |
|       "phone": "12127593751"              |
|     }                                     |
|   ],                                      |
|   "date": "2017-04-28",                   |
|   "dealership": "Tindel Toyota",          |
|   "salesperson": {                        |
|     "id": "274",                          |
|     "name": "Greg Northrup"               |
|   },                                      |
|   "vehicle": [                            |
|     {                                     |
|       "extras": [                         |
|         "ext warranty",                   |
|         "rust proofing",                  |
|         "fabric protection"               |
|       ],                                  |
|       "make": "Toyota",                   |
|       "model": "Camry",                   |
|       "price": "23500",                   |
|       "year": "2017"                      |
|     }                                     |
|   ]                                       |
| }                                         |
+-------------------------------------------+

Durchsuchen semistrukturierter Daten

Fügen Sie einen Doppelpunkt : zwischen dem Namen der VARIANT-Spalte und einem Element erster Ebene ein: <Spalte>:<Ebene1-Element>.

Bemerkung

In den folgenden Beispielen wird die Abfrageausgabe in doppelte Anführungszeichen eingeschlossen, da die Anfrageausgabe vom Typ VARIANT und nicht vom Typ VARCHAR ist. (VARIANT-Werte sind keine Zeichenfolgen. Die VARIANT-Werte enthalten Zeichenfolgen.) Operator : und die nachfolgenden Operatoren . und [] geben standardmäßig immer VARIANT-Werte zurück, die Zeichenfolgen enthalten.

Rufen Sie beispielsweise eine Liste aller Händlernamen ab:

SELECT src:dealership FROM car_sales;

+--------------------------+
| SRC:DEALERSHIP           |
|--------------------------|
| "Valley View Auto Sales" |
| "Tindel Toyota"          |
+--------------------------+

Bemerkung

Snowflake-Zeichenfolgen und damit VARIANT-Werte, die Zeichenfolgen als Schlüssel oder Werte enthalten, akzeptieren das Zeichen '\x00'. In einem Pfad wird das nachstehende '\x00'-Zeichen jedoch ignoriert. Der folgende Code enthält beispielsweise einen Schlüssel, der mit '\x00' endet, der aber auch ohne '\x00' gefunden werden kann:

CREATE OR REPLACE TABLE double0 (variant1 VARIANT);
INSERT INTO double0 (variant1) SELECT PARSE_JSON(' { "key1\x00":"value1" } ');
SELECT GET_PATH(variant1, 'key1')  FROM double0;
+----------------------------+
| GET_PATH(VARIANT1, 'KEY1') |
|----------------------------|
| "value1"                   |
+----------------------------+

Snowflake empfiehlt, in semistrukturierten Daten eingebettete '\x00'-Zeichen in Schlüsseln zu vermeiden.

Es gibt zwei Möglichkeiten, auf Elemente in einem JSON-Objekt zuzugreifen:

Wichtig

Unabhängig davon, welche Notation Sie verwenden, ist die Groß-/Kleinschreibung bei Spaltennamen nicht relevant, bei Elementnamen dagegen schon. In der folgenden Liste sind beispielsweise die ersten beiden Pfade gleich, der dritte jedoch nicht:

  • src:salesperson.name

  • SRC:salesperson.name

  • SRC:salesperson.name

Punktnotation

Verwenden Sie die Punktnotation, um einen Pfad in einem JSON-Objekt zu durchlaufen: <Spalte>:<Ebene1-Element>.<Ebene2-Element>.<Ebene3-Element>. Schließen Sie optional Elementnamen in doppelte Anführungszeichen ein: <Spalte>:"<Ebene1-Element>"."<Ebene2-Element>"."<Ebene3-Element>".

Bemerkung

Die Regeln für JSON-Schlüssel (Elementnamen) unterscheiden sich von den Regeln für Snowflake-SQL-Bezeichner.

Weitere Informationen zu den Regeln für Snowflake-SQL-Bezeichner finden Sie unter Anforderungen an Bezeichner.

Weitere Informationen zu JSON-Schlüsseln finden Sie unter http://json.org, insbesondere in der Beschreibung zu „string“ (Zeichenfolge).

Wenn ein Elementname nicht den Regeln für Snowflake-SQL-Bezeichner entspricht, z. B. wenn er Leerzeichen enthält, dann müssen Sie den Namen in doppelte Anführungszeichen setzen. Nachfolgend einige Beispiele (nicht alle stammen aus dem obigen „car_sales“-Beispiel) für gültige JSON-Elementnamen, die keine gültigen Snowflake-Bezeichnernamen sind, sofern sie nicht in doppelte Anführungszeichen eingeschlossen sind:

-- This contains a blank.
SELECT src:"company name" FROM partners;

-- This does not start with a letter or underscore.
SELECT zipcode_info:"94987" FROM addresses;

-- This contains characters that are not letters, digits, or underscores, and
-- it does not start with a letter or underscore.
SELECT measurements:"#sPerSquareInch" FROM english_metrics;

Abrufen der Namen aller Verkäufer, die Autos verkauft haben:

SELECT src:salesperson.name FROM car_sales;

+----------------------+
| SRC:SALESPERSON.NAME |
|----------------------|
| "Frank Beasley"      |
| "Greg Northrup"      |
+----------------------+

Klammernotation

Alternativ können Sie die Klammernotation verwenden, um den Pfad in einem Objekt zu durchlaufen: <Spalte>['<Ebene1-Element>']['<Ebene2-Element>']. Schließen Sie Elementnamen in einfache Anführungszeichen ein. Werte werden als Zeichenfolgen abgerufen.

Abrufen der Namen aller Verkäufer, die Autos verkauft haben:

SELECT src['salesperson']['name'] FROM car_sales;

+----------------------------+
| SRC['SALESPERSON']['NAME'] |
|----------------------------|
| "Frank Beasley"            |
| "Greg Northrup"            |
+----------------------------+

Abrufen einer einzelnen Instanz eines sich wiederholenden Elements

Abrufen einer bestimmten nummerierten Instanz eines untergeordneten Elements in einem sich wiederholenden Array durch Hinzufügen eines nummerierten Prädikats (beginnend bei 0) zur Array-Referenz.

Beachten Sie, dass es zum Abrufen aller Instanzen eines untergeordneten Elements aus einem sich wiederholenden Array notwendig ist, das Array zu vereinfach (Flatten). Ein Beispiel dazu finden Sie unter Verwenden der FLATTEN-Funktion zum Parsen von Arrays unter diesem Thema.

Abrufen der Fahrzeugdetails für jeden Verkauf:

SELECT src:vehicle[0] FROM car_sales;

+-------------------------+
| SRC:VEHICLE[0]          |
|-------------------------|
| {                       |
|   "extras": [           |
|     "ext warranty",     |
|     "paint protection"  |
|   ],                    |
|   "make": "Honda",      |
|   "model": "Civic",     |
|   "price": "20275",     |
|   "year": "2017"        |
| }                       |
| {                       |
|   "extras": [           |
|     "ext warranty",     |
|     "rust proofing",    |
|     "fabric protection" |
|   ],                    |
|   "make": "Toyota",     |
|   "model": "Camry",     |
|   "price": "23500",     |
|   "year": "2017"        |
| }                       |
+-------------------------+

Abrufen des Preises von jedem verkauften Auto:

SELECT src:vehicle[0].price FROM car_sales;

+----------------------+
| SRC:VEHICLE[0].PRICE |
|----------------------|
| "20275"              |
| "23500"              |
+----------------------+

Explizites Umwandeln von Werten

Standardmäßig werden Werte, die aus einer VARIANT-Spalte abgerufen werden, als Zeichenfolgenliterale zurückgegeben. Um unerwartete Ergebnisse zu verhindern, wandeln Sie die Werte explizit in den gewünschten Datentyp um (mit ::-Notation).

Abrufen einer Liste der Verkäufer-IDs als Zeichenfolgenwerte:

SELECT src:salesperson.id::string FROM car_sales;

+----------------------------+
| SRC:SALESPERSON.ID::STRING |
|----------------------------|
| 55                         |
| 274                        |
+----------------------------+

Verwenden der FLATTEN-Funktion zum Analysieren von Arrays

Analysieren Sie ein Array mit der FLATTEN-Funktion. FLATTEN ist eine Tabellenfunktion, mit der die laterale Ansicht einer VARIANT-, OBJECT- oder ARRAY-Spalte erstellt werden kann. Die Funktion gibt eine Zeile für jedes Objekt zurück, und der LATERAL-Modifikator verknüpft die Daten mit allen Informationen außerhalb des Objekts.

Abrufen der Namen und Adressen aller Kunden. Umwandeln der VARIANT-Ausgabe in Zeichenfolgenwerte:

SELECT
  value:name::string as "Customer Name",
  value:address::string as "Address"
  FROM
    car_sales
  , LATERAL FLATTEN(INPUT => SRC:customer);

+--------------------+-------------------+
| Customer Name      | Address           |
|--------------------+-------------------|
| Joyce Ridgely      | San Francisco, CA |
| Bradley Greenbloom | New York, NY      |
+--------------------+-------------------+

Verwenden der FLATTEN-Funktion zum Analysieren geschachtelter Arrays

In den Beispieldaten ist das extras-Array innerhalb des vehicle-Arrays geschachtelt:

"vehicle" : [
     {"make": "Honda", "model": "Civic", "year": "2017", "price": "20275", "extras":["ext warranty", "paint protection"]}
   ]

Fügen Sie eine zweite FLATTEN-Klausel hinzu, um das extras-Array innerhalb des bereits vereinfachten vehicle-Arrays zu vereinfachen und die für jedes verkaufte Auto erworbenen „Extras“ abzurufen:

SELECT
  vm.value:make::string as make,
  vm.value:model::string as model,
  ve.value::string as "Extras Purchased"
  FROM
    car_sales
  , LATERAL FLATTEN(INPUT => SRC:vehicle) vm
  , LATERAL FLATTEN(INPUT => vm.value:extras) ve;

  +--------+-------+-------------------+
  | MAKE   | MODEL | Extras Purchased  |
  |--------+-------+-------------------|
  | Honda  | Civic | ext warranty      |
  | Honda  | Civic | paint protection  |
  | Toyota | Camry | ext warranty      |
  | Toyota | Camry | rust proofing     |
  | Toyota | Camry | fabric protection |
  +--------+-------+-------------------+

Analysieren von Text als VARIANT-Werte mit der PARSE_JSON-Funktion

Analysieren Sie mit der Funktion PARSE_JSON Text als JSON-Dokument.

Wenn der Eingabewert NULL ist, ist der Ausgabewert auch NULL. Wenn jedoch die Eingabezeichenfolge null ist, wird sie als VARIANT null-Wert interpretiert, d. h. das Ergebnis ist kein SQL NULL, sondern ein reeller Wert, der verwendet wird, um einen Nullwert in semistrukturierten Formaten darzustellen.

Ein Beispiel dazu finden Sie unter In Beispielen verwendete Beispieldaten unter diesem Thema.

Extrahieren von Werten mit der GET-Funktion

GET akzeptiert einen VARIANT-, OBJECT- oder ARRAY-Wert als erstes Argument und extrahiert den VARIANT-Wert des Elements in dem Pfad, der als zweites Argument angegeben ist.

Berechnen und extrahieren Sie das letzte Element jedes Arrays in einer VARIANT-Spalte mit den Funktionen GET und ARRAY_SIZE. ARRAY_SIZE gibt die Größe des Eingabearrays zurück:

Bemerkung

Dieses Beispiel weicht von der car_sales-Tabelle ab, die an anderer Stelle unter diesem Thema verwendet wird.

CREATE OR replace TABLE colors (v variant);

INSERT INTO
   colors
   SELECT
      parse_json(column1) AS v
   FROM
   VALUES
     ('[{r:255,g:12,b:0},{r:0,g:255,b:0},{r:0,g:0,b:255}]'),
     ('[{c:0,m:1,y:1,k:0},{c:1,m:0,y:1,k:0},{c:1,m:1,y:0,k:0}]')
    v;

SELECT *, GET(v, ARRAY_SIZE(v)-1) FROM colors;

+---------------+-------------------------+
| V             | GET(V, ARRAY_SIZE(V)-1) |
|---------------+-------------------------|
| [             | {                       |
|   {           |   "b": 255,             |
|     "b": 0,   |   "g": 0,               |
|     "g": 12,  |   "r": 0                |
|     "r": 255  | }                       |
|   },          |                         |
|   {           |                         |
|     "b": 0,   |                         |
|     "g": 255, |                         |
|     "r": 0    |                         |
|   },          |                         |
|   {           |                         |
|     "b": 255, |                         |
|     "g": 0,   |                         |
|     "r": 0    |                         |
|   }           |                         |
| ]             |                         |
| [             | {                       |
|   {           |   "c": 1,               |
|     "c": 0,   |   "k": 0,               |
|     "k": 0,   |   "m": 1,               |
|     "m": 1,   |   "y": 0                |
|     "y": 1    | }                       |
|   },          |                         |
|   {           |                         |
|     "c": 1,   |                         |
|     "k": 0,   |                         |
|     "m": 0,   |                         |
|     "y": 1    |                         |
|   },          |                         |
|   {           |                         |
|     "c": 1,   |                         |
|     "k": 0,   |                         |
|     "m": 1,   |                         |
|     "y": 0    |                         |
|   }           |                         |
| ]             |                         |
+---------------+-------------------------+

Extrahieren von Werten nach Pfad mit der GET_PATH-Funktion

Extrahieren Sie einen Wert aus einer VARIANT-Spalte mit der Funktion GET_PATH , :. Die Funktion ist eine Variation von GET, mit der ein Wert unter Verwendung eines Pfadnamens extrahiert wird. GET_PATH entspricht einer Kette von GET-Funktionen.

Abrufen der Fahrzeugmarken der von den Kunden gekauften Autos:

SELECT GET_PATH(src, 'vehicle[0]:make') FROM car_sales;

+----------------------------------+
| GET_PATH(SRC, 'VEHICLE[0]:MAKE') |
|----------------------------------|
| "Honda"                          |
| "Toyota"                         |
+----------------------------------+

Unter Durchsuchen semistrukturierter Daten wird die Pfadsyntax beschrieben, mit der Elemente in einer VARIANT-Spalte abgerufen werden. Die Syntax ist für die Funktion GET oder GET_PATH , : kompakt. Im Gegensatz zur Pfadsyntax können diese Funktionen mit unregelmäßigen Pfaden oder Pfadelementen umgehen.

Die folgenden Abfragen führen zu den gleichen Ergebnissen:

SELECT GET_PATH(src, 'vehicle[0].make') FROM car_sales;

SELECT src:vehicle[0].make FROM car_sales;

Analysieren von Arrays direkt aus einer Staging-Datendateien

Angenommen, eine bereitgestellte Datei namens contacts.json.gz enthält die folgenden Daten:

{
    "root": [
        {
            "employees": [
                {
                    "firstName": "Anna",
                    "lastName": "Smith"
                },
                {
                    "firstName": "Peter",
                    "lastName": "Jones"
                }
            ]
        }
    ]
}

Weiterhin angenommen, ein Dateiformat namens my_json_format enthält TYPE=JSON in seiner Definition.

Fragen Sie den Namen des ersten Mitarbeiters in der Stagingdatei ab. In diesem Beispiel befindet sich die Datei im Tabellen-Stagingbereich customers, aber sie könnte sich auch in jedem anderen internen (z. B. Snowflake) oder externe Stagingbereich befinden:

SELECT 'The First Employee Record is '||
    S.$1:root[0].employees[0].firstName||
    ' '||S.$1:root[0].employees[0].lastName
FROM @%customers/contacts.json.gz (file_format => 'my_json_format') as S;

+----------------------------------------------+
| 'THE FIRST EMPLOYEE RECORD IS '||            |
|      S.$1:ROOT[0].EMPLOYEES[0].FIRSTNAME||   |
|      ' '||S.$1:ROOT[0].EMPLOYEES[0].LASTNAME |
|----------------------------------------------|
| The First Employee Record is Anna Smith      |
+----------------------------------------------+