Databricks Runtime JSON-Pfadausdruck

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

Syntax

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

Die Klammern um field und * sind tatsächliche index Klammern und geben keine optionale Syntax an.

Parameter

  • identifier:Ein Bezeichner eines JSON-Felds, bei dem die Groß-/Kleinschreibung nicht beachtet wird.
  • [ field ]: Zeichenfolgenliteral mit Klammern, das ein JSON-Feld identifiziert.
  • [ * ]: Identifizieren aller Elemente in einem JSON-Array.
  • [ index ]: Ein ganzzahliges Literal, das ein bestimmtes Element in einem 0-basierten JSON-Array identifiziert.

Gibt zurück

EINE ZEICHENFOLGE.

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

Sie können den Operator :: verwenden, um Werte in grundlegende Datentypen zu casten.

Verwenden Sie die from_json Funktion, um geschachtelte Ergebnisse in komplexere Datentypen wie Arrays oder Strukturen zu casten.

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 in unterschiedlichen Fällen kein Feld mit demselben Namen vorhanden ist.

Verwenden Sie einen durch Trennzeichen getrennten Bezeichner, wenn in unterschiedlichen Fällen kein Feld mit demselben Namen vorhanden ist.

Die [ field ] Notation kann immer verwendet werden, erfordert jedoch eine genaue Übereinstimmung mit dem Fall des Felds.

Wenn Databricks Runtime ein Feld nicht eindeutig identifizieren können, wird ein Fehler zurückgegeben. Wenn keine Übereinstimmung für ein Feld gefunden wird Databricks Runtime gibt NULL zurück.

Beispiele

In den folgenden Beispielen werden die Daten verwendet, die mit der -Anweisung in Beispieldatenerstellt wurden.

In diesem Abschnitt

Extrahieren mithilfe von 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

Umwandlungswerte

-- 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"]
  ]'

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