JSON-Pfadausdruck

Gilt für: Databricks SQL Databricks Runtime

Ein JSON-Pfadausdruck wird verwendet, um mit dem Operator : Werte aus einer JSON-Zeichenfolge oder aus VARIANT zu extrahieren.

Syntax

{ { identifier | [ field ] | [ * ] | [ index ] }
  [ . identifier | [ field ] | [ * ] | [ index ] ] [...] }

Die eckigen Klammern, die field, * und index umgeben, sind tatsächliche Klammern und geben keine optionale Syntax an.

Parameter

• Bezeichner: Ein Bezeichner eines JSON-Felds. Bei JSON-STRING-Typen wird die Groß-/Kleinschreibung von Bezeichnern nicht beachtet. Bei VARIANT-Typen muss die Groß-/Kleinschreibung beachtet werden.
• [ field ]: Ein STRING-Literal in Klammern, bei dem die Groß-/Kleinschreibung beachtet wird und das ein JSON-Feld identifiziert.
• [ * ]: Identifiziert alle Elemente in einem JSON-Array. Diese Syntax wird für VARIANT nicht unterstützt.
• [ index ]: Ein ganzzahliges Literal, das ein bestimmtes Element in einem 0-basierten JSON-Array identifiziert.

Gibt zurück

STRING, wenn der Ausdruck, zu dem navigiert wird, ein STRING-Element ist. VARIANT, wenn der Ausdruck, zu dem navigiert wird, ein VARIANT-Element ist.

Wenn ein JSON-Feld mit einem null-Wert ohne Trennzeichen vorhanden ist, erhalten Sie einen SQL-NULL-Wert für diese Spalte und keinen null-Textwert.

Sie können den Operator :: verwenden, um Werte in einfache Datentypen umzuwandeln.

Verwenden Sie die Funktion „from_json“, um geschachtelte Ergebnisse in komplexere Datentypen wie Arrays oder Strukturen umzuwandeln.

Hinweise

Sie können einen Bezeichner ohne Trennzeichen verwenden, um auf ein JSON-Feld zu verweisen, wenn der Name keine Leerzeichen oder Sonderzeichen enthält und kein Feld mit demselben Namen in einer anderen Groß-/Kleinschreibung im JSON-STRING-Element vorhanden ist.

Verwenden Sie einen Bezeichner mit Trennzeichen, wenn kein gleichnamiges Feld in einer anderen Groß-/Kleinschreibung vorhanden ist.

Die Notation [ field ] kann immer verwendet werden, erfordert jedoch, dass Sie die Groß-/Kleinschreibung des Felds genau beachten.

Wenn Azure Databricks ein Feld nicht eindeutig identifizieren kann, wird ein Fehler zurückgegeben. Wenn keine Übereinstimmung für ein Feld gefunden wird, gibt Azure Databricks NULL zurück.

Ein NULL-Wert kann in VARIANT codiert werden, und dieser Wert ist kein SQL-Wert vom Typ NULL. Daher ist parse_json('null') IS NULL gleich false, aber is_variant_null(parse_json('null')) ist true. Ein mit VARIANT codierter NULL-Wert kann in einen SQL-NULL-Wert konvertiert werden, indem er in einen bestimmten Typ umgewandelt wird. parse_json('null')::int IS NULL ist z. B. true.

Beispiele

In den folgenden Beispielen werden die Daten verwendet, die mit der Anweisung unter Beispieldaten erstellt wurden.

Inhalt dieses Abschnitts:

• Extrahieren mit Bezeichnern und Trennzeichen
• Extrahieren geschachtelter Felder
• Extrahieren von Werten aus Arrays
• NULL-Verhalten
• Umwandeln von Werten
• VARIANT-Ausdrücke
• Beispieldaten

Extrahieren mit Bezeichnern und Trennzeichen

> SELECT raw:owner, raw:OWNER, raw:['owner'], raw:['OWNER'] FROM store_data;
  amy  amy  amy  NULL

-- 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;
  94025      94025      1234

Extrahieren geschachtelter Felder

-- Use dot notation
> SELECT raw:store.bicycle FROM store_data;
  '{ "price":19.95, "color":"red" }'

-- Use brackets
> SELECT raw:['store']['bicycle'] FROM store_data;
  '{ "price":19.95, "color":"red" }'

Extrahieren von Werten aus Arrays

-- Index elements
> SELECT raw:store.fruit[0], raw:store.fruit[1] FROM store_data;
  '{ "weight":8, "type":"apple" }'  '{ "weight":9, "type":"pear" }'

-- Extract subfields from arrays
> SELECT raw:store.book[*].isbn FROM store_data;
  '[ 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"}
  ]                            ]                  ]

NULL-Verhalten

> SELECT '{"key":null}':key IS NULL sql_null, '{"key":"null"}':key IS NULL;
  true          false

Umwandeln von Werten

-- price is returned as a double, not a string
> SELECT raw:store.bicycle.price::double FROM store_data
  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
  '{ "price":19.95, "color":"red" }'

-- the column returned is an array of string arrays
> SELECT from_json(raw:store.basket[*], 'array<array<string>>') baskets FROM store_data
  '[
    ["1","2","{\"b\":\"y\",\"a\":\"x\"}]",
    ["3","4"],
    ["5","6"]
  ]'

VARIANT-Ausdrücke

-- Using JSON paths for VARIANT
> SELECT raw:store.bicycle FROM store_data_variant;
  '{ "price":19.95, "color":"red" }'

-- Extracting from VARIANT arrays
> SELECT raw:store.fruit[0], raw_variant:store.fruit[1] FROM store_data_variant;
  '{ "weight":8, "type":"apple" }'  '{ "weight":9, "type":"pear" }'

-- SQL NULL behavior of VARIANT NULL values
> SELECT
  parse_json(NULL) IS NULL AS sql_null,
  parse_json('null') IS NULL AS variant_null,
  parse_json('{ "field_a": null }'):field_a IS NULL AS variant_null_value,
  parse_json('{ "field_a": null }'):missing IS NULL AS missing_sql_value_null
  true  false  false  true

-- price is returned as a double, not a VARIANT
> SELECT raw:store.bicycle.price::double FROM store_data_variant
  19.95

Beispieldaten

> 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

 > CREATE TABLE store_data_variant AS SELECT parse_json(raw) FROM store_data;

Zugehörige Funktionen

• :-Operator