JSON パス式
適用対象: 
Databricks SQL Databricks Runtime
JSON パス式は、: 演算子を使用して JSON 文字列または VARIANT から値を抽出するために使用されます
構文
{ { identifier | [ field ] | [ * ] | [ index ] }
[ . identifier | [ field ] | [ * ] | [ index ] ] [...] }
field、*、index を囲むかっこは、省略可能な構文を示すものではなく実際のかっこです。
パラメーター
- 識別子: JSON フィールドの識別子。 JSON
STRING型の場合、識別子の大文字と小文字は区別されません。VARIANT型の場合、大文字と小文字が区別されます。 [ field ]: 大文字と小文字が区別される、かっこで囲まれたSTRINGリテラル。JSON フィールドを識別します。[ * ]: JSON 配列内のすべての要素を識別します。 この構文はVARIANTではサポートされていません。[ index ]: 0 ベース JSON 配列内の特定の要素を識別する整数リテラル。
返品
操作対象の式が STRING の場合は STRING です。 操作対象の式が VARIANT の場合は VARIANT です。
区切りのない null 値を持つ JSON フィールドが存在する場合は、null テキスト値ではなく、その列の SQL NULL 値を受け取る必要があります。
:: 演算子を使用して、基本的なデータ型に値をキャストできます。
入れ子になった結果を、配列や構造体などのより複雑なデータ型にキャストするには、from_json 関数を使用します。
メモ
JSON フィールドの名前にスペースや特殊文字が含まれず、同じ名前で大文字と小文字が異なるフィールドが JSON STRING にない場合は、引用符で囲まれていない識別子を使用して JSON フィールドを参照できます。
同じ名前で大文字と小文字が異なるフィールドがない場合は、引用符で囲まれた識別子を使用します。
[ field ] 表記は常に使用できますが、フィールドの大文字と小文字が正確に一致している必要があります。
Azure Databricks でフィールドを一意に識別できない場合は、エラーが返されます。 どのフィールドにも一致するものが見つからない場合、Azure Databricks は NULL を返します。
NULL 値を VARIANT 内にエンコードでき、この値は SQL NULL ではありません。
つまり、parse_json('null') IS NULL は false ですが、is_variant_null(parse_json('null')) は true です。
VARIANT にエンコードされた null を、何らかの型にキャストすることで SQL NULL に変換できます。 たとえば、parse_json('null')::int IS NULL は true です。
例
次の例では、サンプル データのステートメントで作成されたデータを使用します。
このセクションの内容は次のとおりです。
識別子と区切り記号を使用して抽出する
> 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
入れ子になったフィールドを抽出する
-- 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" }'
配列から値を抽出する
-- 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 の動作
> SELECT '{"key":null}':key IS NULL sql_null, '{"key":"null"}':key IS NULL;
true false
値をキャストする
-- 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"]
]'
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
サンプル データ
> 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;