Abfragen von JSON-Zeichenfolgen
In diesem Artikel werden die Databricks SQL-Operatoren beschrieben, die Sie zum Abfragen und Transformieren von als JSON-Zeichenfolgen gespeicherten teilstrukturierten Daten verwenden können.
Hinweis
Mit diesem Feature können Sie semistrukturierte Daten lesen, ohne die Dateien zu vereinfachen. Für eine optimale Leseabfrageleistung empfiehlt Databricks jedoch, geschachtelte Spalten mit den richtigen Datentypen zu extrahieren.
Eine Spalte aus Feldern, die JSON-Zeichenfolgen enthalten, wird mit der Syntax <column-name>:<extraction-path> extrahiert, wobei <column-name> der Name der Zeichenfolgenspalte und <extraction-path> der Pfad zum zu extrahierenden Feld ist. Die zurückgegebenen Ergebnisse sind Zeichenfolgen.
Erstellen einer Tabelle mit hochgradig geschachtelten Daten
Führen Sie die folgende Abfrage aus, um eine Tabelle mit hochgradig geschachtelten Daten zu erstellen. In den Beispielen dieses Artikels wird immer auf diese Tabelle verwiesen.
CREATE TABLE store_data AS SELECT
'{
"store":{
"fruit": [
{"weight":8,"type":"apple"},
{"weight":9,"type":"pear"}
],
"basket":[
[1,2,{"b":"y","a":"x"}],
[3,4],
[5,6]
],
"book":[
{
"author":"Nigel Rees",
"title":"Sayings of the Century",
"category":"reference",
"price":8.95
},
{
"author":"Herman Melville",
"title":"Moby Dick",
"category":"fiction",
"price":8.99,
"isbn":"0-553-21311-3"
},
{
"author":"J. R. R. Tolkien",
"title":"The Lord of the Rings",
"category":"fiction",
"reader":[
{"age":25,"name":"bob"},
{"age":26,"name":"jack"}
],
"price":22.99,
"isbn":"0-395-19395-8"
}
],
"bicycle":{
"price":19.95,
"color":"red"
}
},
"owner":"amy",
"zip code":"94025",
"fb:testid":"1234"
}' as raw
Extrahieren einer Spalte der obersten Ebene
Um eine Spalte zu extrahieren, geben Sie den Namen des JSON-Felds in Ihrem Extraktionspfad an.
Sie können Spaltennamen in Klammern bereitstellen. Bei Spalten, die in Klammern angegeben sind, wird die Groß-/Kleinschreibung beachtet. Auf den Spaltennamen wird auch ohne Unterscheidung nach Groß-/Kleinschreibung verwiesen.
SELECT raw:owner, RAW:owner FROM store_data
+-------+-------+
| owner | owner |
+-------+-------+
| amy | amy |
+-------+-------+
-- References are case sensitive when you use brackets
SELECT raw:OWNER case_insensitive, raw:['OWNER'] case_sensitive FROM store_data
+------------------+----------------+
| case_insensitive | case_sensitive |
+------------------+----------------+
| amy | null |
+------------------+----------------+
Verwenden Sie Backticks, um Leerzeichen und Sonderzeichen mit Escapezeichen zu versehen. Bei den Feldnamen wird die Groß-/Kleinschreibung nicht beachtet.
-- Use backticks to escape special characters. References are case insensitive when you use backticks.
-- Use brackets to make them case sensitive.
SELECT raw:`zip code`, raw:`Zip Code`, raw:['fb:testid'] FROM store_data
+----------+----------+-----------+
| zip code | Zip Code | fb:testid |
+----------+----------+-----------+
| 94025 | 94025 | 1234 |
+----------+----------+-----------+
Hinweis
Wenn ein JSON-Datensatz mehrere Spalten enthält, die aufgrund nicht beachteter Groß-/Kleinschreibung mit Ihrem Extraktionspfad übereinstimmen können, erhalten Sie eine Fehlermeldung, die Sie auffordert, eckige Klammern zu verwenden. Wenn zeilenübergreifende Übereinstimmungen von Spalten vorhanden sind, erhalten Sie keine Fehler. Folgendes führt zu einer Fehlermeldung: {"foo":"bar", "Foo":"bar"} und Folgendes führt nicht zu einer Fehlermeldung:
{"foo":"bar"}
{"Foo":"bar"}
Extrahieren geschachtelter Felder
Geschachtelte Felder werden über Punktnotation oder mithilfe von eckigen Klammern angegeben. Wenn Sie Klammern verwenden, wird bei Spalten die Groß-/Kleinschreibung beachtet.
-- Use dot notation
SELECT raw:store.bicycle FROM store_data
-- the column returned is a string
+------------------+
| bicycle |
+------------------+
| { |
| "price":19.95, |
| "color":"red" |
| } |
+------------------+
-- Use brackets
SELECT raw:store['bicycle'], raw:store['BICYCLE'] FROM store_data
+------------------+---------+
| bicycle | BICYCLE |
+------------------+---------+
| { | null |
| "price":19.95, | |
| "color":"red" | |
| } | |
+------------------+---------+
Extrahieren von Werten aus Arrays
Elemente in Arrays werden mit Klammern indiziert. Indizes basieren auf 0. Sie können ein Sternchen (*) gefolgt von der Punkt- oder Klammern-Notation verwenden, um Unterfelder aus allen Elementen in einem Array zu extrahieren.
-- Index elements
SELECT raw:store.fruit[0], raw:store.fruit[1] FROM store_data
+------------------+-----------------+
| fruit | fruit |
+------------------+-----------------+
| { | { |
| "weight":8, | "weight":9, |
| "type":"apple" | "type":"pear" |
| } | } |
+------------------+-----------------+
-- Extract subfields from arrays
SELECT raw:store.book[*].isbn FROM store_data
+--------------------+
| isbn |
+--------------------+
| [ |
| null, |
| "0-553-21311-3", |
| "0-395-19395-8" |
| ] |
+--------------------+
-- Access arrays within arrays or structs within arrays
SELECT
raw:store.basket[*],
raw:store.basket[*][0] first_of_baskets,
raw:store.basket[0][*] first_basket,
raw:store.basket[*][*] all_elements_flattened,
raw:store.basket[0][2].b subfield
FROM store_data
+----------------------------+------------------+---------------------+---------------------------------+----------+
| basket | first_of_baskets | first_basket | all_elements_flattened | subfield |
+----------------------------+------------------+---------------------+---------------------------------+----------+
| [ | [ | [ | [1,2,{"b":"y","a":"x"},3,4,5,6] | y |
| [1,2,{"b":"y","a":"x"}], | 1, | 1, | | |
| [3,4], | 3, | 2, | | |
| [5,6] | 5 | {"b":"y","a":"x"} | | |
| ] | ] | ] | | |
+----------------------------+------------------+---------------------+---------------------------------+----------+
Umwandeln von Werten
Sie können :: verwenden, um Werte in einfache Datentypen umzuwandeln. Verwenden Sie die from_json-Methode, um geschachtelte Ergebnisse in komplexere Datentypen wie Arrays oder Strukturen umzuwandeln.
-- price is returned as a double, not a string
SELECT raw:store.bicycle.price::double FROM store_data
+------------------+
| price |
+------------------+
| 19.95 |
+------------------+
-- use from_json to cast into more complex types
SELECT from_json(raw:store.bicycle, 'price double, color string') bicycle FROM store_data
-- the column returned is a struct containing the columns price and color
+------------------+
| bicycle |
+------------------+
| { |
| "price":19.95, |
| "color":"red" |
| } |
+------------------+
SELECT from_json(raw:store.basket[*], 'array<array<string>>') baskets FROM store_data
-- the column returned is an array of string arrays
+------------------------------------------+
| basket |
+------------------------------------------+
| [ |
| ["1","2","{\"b\":\"y\",\"a\":\"x\"}]", |
| ["3","4"], |
| ["5","6"] |
| ] |
+------------------------------------------+
NULL-Verhalten
Wenn ein JSON-Feld mit einem null-Wert vorhanden ist, erhalten Sie einen SQL-null-Wert für diese Spalte und keinen null-Textwert.
select '{"key":null}':key is null sql_null, '{"key":null}':key == 'null' text_null
+-------------+-----------+
| sql_null | text_null |
+-------------+-----------+
| true | null |
+-------------+-----------+
Transformieren geschachtelter Daten mithilfe von Spark SQL-Operatoren
Apache Spark verfügt über eine Reihe von integrierten Funktionen für die Verwendung komplexer und geschachtelter Daten. Das folgende Notebook enthält entsprechende Beispiele.
Darüber hinaus bieten Funktionen höherer Ordnung viele zusätzliche Optionen, wenn keine integrierten Spark-Operatoren für die gewünschte Datentransformation verfügbar sind.