Applies to: Databricks SQL Databricks Runtime
A JSON path expression is used to extract values from a JSON string or a VARIANT using the : operator
{ { identifier | [ field ] | [ * ] | [ index ] }
[ . identifier | [ field ] | [ * ] | [ index ] ] [...] }
The brackets surrounding field, * and index are actual brackets and not indicating an optional syntax.
STRING types, the identifier is case insensitive. For VARIANT types, it is case-sensitive.[ field ]: A bracketed case-sensitive STRING literal identifying a JSON field.[ * ]: Identifying all elements in a JSON array. This syntax is not supported for VARIANT.[ index ]: An integer literal identifying a specific element in a 0-based JSON array.A STRING if the expression being navigated is a STRING. A VARIANT if the expression being navigated is a VARIANT.
When a JSON field exists with an un-delimited null value, you will receive a SQL NULL value for that column, not a null text value.
You can use :: operator to cast values to basic data types.
Use the from_json function to cast nested results into more complex data types, such as arrays or structs.
NotesâYou can use an un-delimited identifier to refer to a JSON field if the name does not contain spaces or special characters, and there is no field in the JSON STRING of the same name in a different case.
Use a delimited identifier if there is no field of the same name in a different case.
The [ field ] notation can always be used, but requires you to exactly match the case of the field.
If Databricks cannot uniquely identify a field an error is returned. If no match is found for any field Databricks returns NULL.
A NULL value can be encoded within a VARIANT, and that value is not a SQL NULL. Therefore, parse_json('null') IS NULL is false, but is_variant_null(parse_json('null')) is true. A VARIANT encoded null can be converted to a SQL NULL by casting it to some type. For example, parse_json('null')::int IS NULL is true.
The following examples use the data created with the statement in Example data.
In this section:
SQL
> SELECT raw:owner, raw:OWNER, raw:['owner'], raw:['OWNER'] FROM store_data;
amy amy amy NULL
> SELECT raw:`zip code`, raw:`Zip Code`, raw:['fb:testid'] FROM store_data;
94025 94025 1234
SQL
> SELECT raw:store.bicycle FROM store_data;
'{ "price":19.95, "color":"red" }'
> SELECT raw:['store']['bicycle'] FROM store_data;
'{ "price":19.95, "color":"red" }'
SQL
> SELECT raw:store.fruit[0], raw:store.fruit[1] FROM store_data;
'{ "weight":8, "type":"apple" }' '{ "weight":9, "type":"pear" }'
> SELECT raw:store.book[*].isbn FROM store_data;
'[ null, "0-553-21311-3", "0-395-19395-8" ]'
> 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"}
] ] ]
SQL
> SELECT '{"key":null}':key IS NULL sql_null, '{"key":"null"}':key IS NULL;
true false
SQL
> SELECT raw:store.bicycle.price::double FROM store_data
19.95
> SELECT from_json(raw:store.bicycle, 'price double, color string') bicycle FROM store_data
'{ "price":19.95, "color":"red" }'
> SELECT from_json(raw:store.basket[*], 'array<array<string>>') baskets FROM store_data
'[
["1","2","{\"b\":\"y\",\"a\":\"x\"}]",
["3","4"],
["5","6"]
]'
SQL
> SELECT raw:store.bicycle FROM store_data_variant;
'{ "price":19.95, "color":"red" }'
> SELECT raw:store.fruit[0], raw_variant:store.fruit[1] FROM store_data_variant;
'{ "weight":8, "type":"apple" }' '{ "weight":9, "type":"pear" }'
> 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
> SELECT raw:store.bicycle.price::double FROM store_data_variant
19.95
SQL
> 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;
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4