JSON-padexpressie
Van toepassing op: Databricks Databricks Runtime
Een JSON-padexpressie wordt gebruikt om waarden uit een JSON-tekenreeks te extraheren met behulp van de operator :
Syntaxis
{ { identifier | [ field ] | [ * ] | [ index ] }
[ . identifier | [ field ] | [ * ] | [ index ] ] [...] }
De vierkante haken rond field
en *
index
zijn werkelijke vierkante haken en geven geen optionele syntaxis aan.
Parameters
- id: een niet-hoofdlettergevoelige id van een JSON-veld.
[ field ]
: een hoofdlettergevoelige tekenreeks die een JSON-veld identificeert.[ * ]
: alle elementen in een JSON-matrix identificeren.[ index ]
: Een letterlijk geheel getal dat een specifiek element in een op 0 gebaseerde JSON-matrix identificeert.
Retourneert
EEN TEKENREEKS.
Wanneer een JSON-veld bestaat met een niet-gescheiden null
waarde, ontvangt u een SQL-waarde NULL
voor die kolom, geen null
tekstwaarde.
U kunt de operator :: gebruiken om waarden te casten naar basisgegevenstypen.
Gebruik de functie from_json om geneste resultaten te casten naar complexere gegevenstypen, zoals matrices of structs.
Notities
U kunt een niet-gescheiden id gebruiken om naar een JSON-veld te verwijzen als de naam geen spaties of speciale tekens bevat en er geen veld met dezelfde naam is in verschillende gevallen.
Gebruik een id met scheidingstekens als er geen veld met dezelfde naam is in een ander geval.
De [ field ]
notatie kan altijd worden gebruikt, maar vereist dat u exact overeenkomt met het hoofdlettergebruik van het veld.
Als Databricks SQL een veld niet uniek kan identificeren, wordt er een fout geretourneerd. Als er geen overeenkomst wordt gevonden voor een veld, retourneert Databricks NULL
SQL .
Voorbeelden
In de volgende voorbeelden worden de gegevens gebruikt die zijn gemaakt met de instructie in Voorbeeldgegevens.
In deze sectie:
- Extraheren met behulp van id's en scheidingstekens
- Geneste velden extraheren
- Waarden extraheren uit matrices
- NULL-gedrag
- Cast-waarden
- Voorbeeldgegevens
Extraheren met behulp van id's en scheidingstekens
> 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
Geneste velden extraheren
-- 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" }'
Waarden extraheren uit matrices
-- 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-gedrag
> SELECT '{"key":null}':key IS NULL sql_null, '{"key":"null"}':key IS NULL;
true false
Cast-waarden
-- 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"]
]'
Voorbeeldgegevens
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