Databricks Runtime JSON-sökvägsuttryck

Ett JSON-sökvägsuttryck används för att extrahera värden från en JSON-sträng med operatorn :

Syntax

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

Hakparenteserna field* som index omger och är faktiska hakparenteser och indikerar inte en valfri syntax.

Parametrar

  • identifier: En okänslig identifierare för ett JSON-fält.
  • [ field ]: En bracketed case sensitive STRING-literal som identifierar ett JSON-fält.
  • [ * ]: Identifiera alla element i en JSON-matris.
  • [ index ]: En heltalslitteral som identifierar ett visst element i en 0-baserad JSON-matris.

Returer

EN STRÄNG.

När det finns ett JSON-fält med ett oavgränsat värde får du ett SQL för den nullNULL kolumnen, inte ett null textvärde.

Du kan använda operatorn :: för att typge värden till grundläggande datatyper.

Använd funktionen from_json för att typge kapslade resultat till mer komplexa datatyper, till exempel matriser eller strukturer.

Kommentarer

Du kan använda en oavgränsad identifierare för att referera till ett JSON-fält om namnet inte innehåller blanksteg, eller specialtecken, och det inte finns något fält med samma namn i olika fall.

Använd en avgränsad identifierare om det inte finns något fält med samma namn i olika fall.

[ field ]Notationen kan alltid användas, men kräver att du exakt matchar fältets fall.

Om Databricks Runtime kan identifiera ett fält returneras ett fel. Om ingen matchning hittas för något fält Databricks Runtime NULL returnerar .

Exempel

I följande exempel används de data som skapats med -instruktionen i Exempeldata.

I det här avsnittet:

Extrahera med identifierare och avgränsare

> 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

Extrahera kapslade fält

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

Extrahera värden från matriser

-- 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-beteende

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

Cast-värden

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

Exempeldata

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