JSON 경로 식
적용 대상:
Databricks SQL Databricks Runtime
JSON 경로 표현식은 : 연산자을 사용하여 JSON 문자열에서 values을 추출하거나 VARIANT에서 사용됩니다.
구문
{ { identifier | [ field ] | [ * ] | [ index ] }
[ . identifier | [ field ] | [ * ] | [ index ] ] [...] }
field, *, index를 둘러싼 대괄호는 실제 대괄호이며 선택적 구문을 나타내지 않습니다.
Parameters
-
identifier: JSON 필드의 identifier. JSON
STRING형식의 경우 identifier 대/소문자를 구분하지 않습니다. 형식의 경우VARIANT대/소문자를 구분합니다. -
[ field ]: JSON 필드를 식별하는 대/소문자를 구분STRING하는 대괄호로 묶인 리터럴입니다. -
[ * ]: JSON 배열의 모든 요소를 식별합니다. 이 구문은 .에 대해VARIANT지원되지 않습니다. -
[ index ]: 0부터 시작하는 JSON 배열의 특정 요소를 식별하는 정수 리터럴입니다.
반품
탐색 중인 식이 .인 경우 A STRING 입니다 STRING. 탐색 중인 식이 .인 경우 A VARIANT 입니다 VARIANT.
구분되지 않는 null 값이 포함된 JSON 필드가 있을 경우, 해당 column에 대해 null 텍스트 값이 아닌 SQL NULL 값을 받게 됩니다.
:: 연산자 사용하여 values 기본 데이터 형식으로 캐스팅할 수 있습니다.
from_json 함수를 사용하여 중첩 결과를 배열이나 구조체와 같은 더 복잡한 데이터 형식으로 캐스팅합니다.
주의
이름이 공백이나 특수 문자를 포함하지 않고 JSON STRING 같은 이름의 필드가 없는 경우 구분되지 않은 identifier 사용하여 JSON 필드를 참조할 수 있습니다.
다른 경우에 같은 이름의 필드가 없는 경우 구분된 identifier 사용합니다.
[ field ] 표기법은 항상 사용할 수 있지만 필드의 대/소문자를 정확히 일치시켜야 합니다.
Azure Databricks가 필드를 고유하게 식별할 수 없는 경우 오류가 반환됩니다. 모든 필드에 대한 일치 항목이 없으면 Azure Databricks가 반환됩니다 NULL.
값은 A NULLVARIANT내에서 인코딩할 수 있으며 해당 값은 SQL NULL이 아닙니다.
따라서 , parse_json('null') IS NULL 이지만 falseis_variant_null(parse_json('null')) .true
VARIANT 인코딩된 null은 일부 형식으로 캐스팅하여 SQL NULL 로 변환할 수 있습니다. 예를 들어 parse_json('null')::int IS NULL는 true입니다.
예제
다음 예제에서는 예제 데이터의 문으로 만든 데이터를 사용합니다.
이 구역의 내용:
- identifier 및 구분 기호 사용하여 추출
- 중첩 필드 추출
- 배열에서 values 추출
- NULL 동작
- 캐스트 values
- VARIANT 식
- 예제 데이터
identifier 및 구분 기호를 사용하여 추출
> 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" }'
배열에서 values 추출
-- 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
캐스트 values
-- 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;