Výraz cesty JSON
Platí pro: 
Databricks SQL Databricks Runtime
Výraz cesty JSON slouží k extrakci hodnot z řetězce JSON nebo VARIANT pomocí operátoru :
Syntaxe
{ { identifier | [ field ] | [ * ] | [ index ] }
[ . identifier | [ field ] | [ * ] | [ index ] ] [...] }
Hranaté závorky obklopující field* a index jsou skutečnými hranatými závorkami, které neudávají volitelnou syntaxi.
Parametry
- identifikátor: Identifikátor pole JSON. U typů JSON
STRINGse identifikátor nerozlišuje malá a velká písmena. UVARIANTtypů se rozlišují malá a velká písmena. [ field ]: Literál rozlišujícíSTRINGvelká a malá písmena v hranatých závorkách identifikující pole JSON.[ * ]: Identifikace všech prvků v poli JSON Tato syntaxe není podporována proVARIANT.[ index ]: Celočíselná literála identifikující konkrétní prvek v poli JSON založeném na 0.
Návraty
A STRING pokud se jedná o výraz, který procházíte, STRINGje . A VARIANT pokud se jedná o výraz, který procházíte, VARIANTje .
Pokud pole JSON existuje s hodnotou bez oddělovače null , obdržíte pro tento sloupec hodnotu SQL NULL , nikoli textovou null hodnotu.
Operátor :: můžete použít k přetypování hodnot na základní datové typy.
Pomocí funkce from_json přetypujte vnořené výsledky do složitějších datových typů, jako jsou pole nebo struktury.
Notes
Identifikátor bez oddělovače můžete použít k odkazování na pole JSON, pokud název neobsahuje mezery nebo speciální znaky a v kódu JSON STRING stejného názvu není žádné pole v jiném případě.
Pokud v jiném případě neexistuje žádné pole se stejným názvem, použijte identifikátor s oddělovači.
Zápis [ field ] lze vždy použít, ale vyžaduje, abyste přesně odpovídali případu pole.
Pokud Azure Databricks nemůže jednoznačně identifikovat pole, vrátí se chyba. Pokud se nenajde žádná shoda pro jakékoli pole, vrátí NULLAzure Databricks .
Hodnotu NULL lze zakódovat v rámci a VARIANTtato hodnota není SQL NULL.
Proto je false, parse_json('null') IS NULL ale is_variant_null(parse_json('null')) je true.
VARIANT Kódovaná hodnota null může být převedena na SQL NULL přetypováním na určitý typ. Například parse_json('null')::int IS NULL je true.
Příklady
Následující příklady používají data vytvořená pomocí příkazu v příkladech dat.
V této části:
- Extrakce pomocí identifikátorů a oddělovačů
- Extrahování vnořených polí
- Extrahování hodnot z polí
- Chování NULL
- Přetypování hodnot
- VÝRAZY VARIANT
- Ukázková data
Extrakce pomocí identifikátorů a oddělovačů
> 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
Extrahování vnořených polí
-- 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" }'
Extrahování hodnot z polí
-- 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"}
] ] ]
Chování NULL
> SELECT '{"key":null}':key IS NULL sql_null, '{"key":"null"}':key IS NULL;
true false
Přetypování hodnot
-- 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"]
]'
VÝRAZY VARIANT
-- 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
Příklad dat
> 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;