JSON-sökvägsuttryck

Artikel
03/01/2024

Gäller för: check markerad ja Databricks SQL-kontroll markerad ja Databricks Runtime

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 som omger fieldoch *index är faktiska hakparenteser och anger inte en valfri syntax.

Parametrar

identifierare: En skiftlägesokänslig identifierare för ett JSON-fält.
[ field ]: En skiftlägeskänslig STRING-literal som identifierar ett JSON-fält.
[ * ]: Identifiera alla element i en JSON-matris.
[ index ]: En heltalsliteral som identifierar ett specifikt element i en 0-baserad JSON-matris.

Returnerar

EN STRÄNG.

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

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

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

Anteckningar

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 ett annat fall.

Notationen [ field ] kan alltid användas, men kräver att du exakt matchar fallet med fältet.

Om Databricks SQL inte unikt kan identifiera ett fält returneras ett fel. Om ingen matchning hittas för något fält returnerar NULLDatabricks SQL .

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
Extrahera kapslade fält
Extrahera värden från matriser
NULL-beteende
Gjutna värden
Exempeldata

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

Gjutna 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

:Operatör