Expresión de ruta de acceso JSON
Se aplica a: Databricks SQL Databricks Runtime
Las expresiones de ruta de acceso de JSON se usan para extraer valores de una cadena JSON mediante el : operador
Sintaxis
{ { identifier | [ field ] | [ * ] | [ index ] }
[ . identifier | [ field ] | [ * ] | [ index ] ] [...] }
Los corchetes de field
, *
y index
son corchetes reales y no indican una sintaxis opcional.
Parámetros
- identifier: identificador que no distingue mayúsculas de minúsculas de un campo JSON.
[ field ]
: literal STRING entre corchetes que distingue mayúsculas de minúsculas e identifica un campo JSON.[ * ]
: identificación de todos los elementos de una matriz JSON.[ index ]
: literal entero que identifica un elemento concreto de una matriz JSON basada en 0.
Devoluciones
Un valor STRING.
Cuando exista un campo JSON con un valor null
sin delimitar, recibirá un valor NULL
de SQL para esa columna, no un valor de texto null
.
Puede usar el operador :: para convertir valores a tipos de datos básicos.
Use la función from_json para convertir los resultados anidados en tipos de datos más complejos, como matrices o estructuras.
Notas
Puede usar un identificador sin delimitar para hacer referencia a un campo JSON si el nombre no contiene espacios ni caracteres especiales, y no hay ningún campo con el mismo nombre tanto en mayúsculas como en minúsculas.
Use un identificador delimitado si no hay ningún campo con el mismo nombre ni en mayúsculas ni en minúsculas.
Siempre se puede usar la notación [ field ]
, pero requiere que coincida exactamente con el caso del campo.
Si Databricks SQL no puede identificar de forma única un campo, se devuelve un error. Si no se encuentra ninguna coincidencia en ningún campo Databricks SQL, devuelve NULL
.
Ejemplos
En los siguientes ejemplos se usan los datos creados con la instrucción en Datos de ejemplo.
En esta sección:
- Extracción mediante un identificador y delimitadores
- Extracción de campos anidados
- Extracción de valores de matrices
- Comportamiento de NULL
- Conversión de valores
- Datos de ejemplo
Extracción mediante un identificador y delimitadores
> 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
Extracción de campos anidados
-- 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" }'
Extracción de valores de 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"}
] ] ]
Comportamiento de NULL
> SELECT '{"key":null}':key IS NULL sql_null, '{"key":"null"}':key IS NULL;
true false
Conversión de valores
-- 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"]
]'
Datos de ejemplo
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