Extrahieren Sie JSON-Daten aus Zeichenfolgen - Amazon Athena
Beispiele: Extrahieren von Eigenschaften

Extrahieren Sie JSON-Daten aus Zeichenfolgen

Möglicherweise verfügen Sie über Quelldaten mit JSON-kodierten Zeichenfolgen, die Sie nicht in eine Tabelle in Athena deserialisieren möchten. In diesem Fall können Sie mit den JSON-Funktionen in Presto dennoch SQL-Operationen für diese Daten ausführen.

Betrachten Sie diese JSON-Zeichenfolge als Beispiel-Dataset.

{"name": "Susan Smith",
"org": "engineering",
"projects":
    [
     {"name":"project1", "completed":false},
     {"name":"project2", "completed":true}
    ]
}

Beispiele: Extrahieren von Eigenschaften

Um die Eigenschaften name und projects aus der JSON-Zeichenfolge zu extrahieren, verwenden Sie die Funktion json_extract wie im folgenden Beispiel. Die Funktion json_extract übernimmt die Spalte mit der JSON-Zeichenfolge und durchsucht sie mit einem JSONPath-artigen Ausdruck in der Punkt-.-Notation.

Anmerkung

JSONPath führt einen einfachen Strukturdurchlauf aus. Dabei wird das Stammverzeichnis des JSON-Dokuments mit $ bezeichnet, gefolgt von einem Punkt und einem direkt unterhalb des Stammverzeichnisses verschachtelten Elements wie $.name.

WITH dataset AS (
  SELECT '{"name": "Susan Smith",
           "org": "engineering",
           "projects": [{"name":"project1", "completed":false},
           {"name":"project2", "completed":true}]}'
    AS myblob
)
SELECT
  json_extract(myblob, '$.name') AS name,
  json_extract(myblob, '$.projects') AS projects
FROM dataset

Der zurückgegebene Wert ist eine JSON-kodierte Zeichenfolge und kein nativer Athena-Datentyp.

+-----------------------------------------------------------------------------------------------+
| name           | projects                                                                     |
+-----------------------------------------------------------------------------------------------+
| "Susan Smith"  | [{"name":"project1","completed":false},{"name":"project2","completed":true}] |
+-----------------------------------------------------------------------------------------------+

Verwenden Sie zum Extrahieren von skalaren Werten aus der JSON-Zeichenfolge die Funktion json_extract_scalar(json, json_path). Es ist ähnlich wie json_extract, gibt aber einen varchar-Zeichenfolgenwert anstelle einer JSON-kodierten Zeichenfolge zurück. Der Wert für den Parameter json_path muss ein Skalar sein (ein boolescher Wert, eine Zahl oder eine Zeichenfolge).

Anmerkung

Verwenden Sie die Funktion json_extract_scalar nicht für Arrays, Zuordnungen oder Strukturen.

WITH dataset AS (
  SELECT '{"name": "Susan Smith",
           "org": "engineering",
           "projects": [{"name":"project1", "completed":false},{"name":"project2", "completed":true}]}'
    AS myblob
)
SELECT
  json_extract_scalar(myblob, '$.name') AS name,
  json_extract_scalar(myblob, '$.projects') AS projects
FROM dataset

Diese Abfrage gibt Folgendes zurück:

+---------------------------+
| name           | projects |
+---------------------------+
| Susan Smith    |          |
+---------------------------+

Um das erste Element der Eigenschaft projects in dem Beispiel-Array abzurufen, verwenden Sie die Funktion json_array_get und geben Sie die Indexposition an.

WITH dataset AS (
  SELECT '{"name": "Bob Smith",
           "org": "engineering",
           "projects": [{"name":"project1", "completed":false},{"name":"project2", "completed":true}]}'
    AS myblob
)
SELECT json_array_get(json_extract(myblob, '$.projects'), 0) AS item
FROM dataset

Es wird der Wert an der angegebenen Indexposition in dem JSON-kodierten Array zurückgegeben.

+---------------------------------------+
| item                                  |
+---------------------------------------+
| {"name":"project1","completed":false} |
+---------------------------------------+

Um einen Athena-Zeichenfolgetyp zurückzugeben, verwenden Sie den []-Operator in einem JSONPath-Ausdruck und verwenden Sie anschließend die Funktion json_extract_scalar. Mehr über [] erfahren Sie unter Auf Array-Elemente zugreifen.

WITH dataset AS (
   SELECT '{"name": "Bob Smith",
             "org": "engineering",
             "projects": [{"name":"project1", "completed":false},{"name":"project2", "completed":true}]}'
     AS myblob
)
SELECT json_extract_scalar(myblob, '$.projects[0].name') AS project_name
FROM dataset

Sie erhalten das folgende Ergebnis:

+--------------+
| project_name |
+--------------+
| project1     |
+--------------+