Consulta de cadenas JSON
En este artículo se describen los operadores SQL de Databricks que puede usar para consultar y transformar datos semiestructurados almacenados como cadenas JSON.
Nota:
Esta característica permite leer datos semiestructurados sin aplanar los archivos. Sin embargo, para obtener un rendimiento óptimo de las consultas de lectura, Databricks recomienda extraer las columnas anidadas con los tipos de datos correctos.
Para extraer una columna de campos que contienen cadenas JSON, se usa la sintaxis <column-name>:<extraction-path>, donde <column-name> es el nombre de la columna de cadena y <extraction-path>, la ruta de acceso al campo que se va a extraer. Los resultados devueltos son cadenas.
Creación de una tabla con datos altamente anidados
Ejecute la consulta siguiente para crear una tabla con datos altamente anidados. Todos los ejemplos de este artículo hacen referencia a esta tabla.
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
Extracción de una columna de nivel superior
Para extraer una columna, especifique el nombre del campo JSON en la ruta de acceso de extracción.
Puede proporcionar nombres de columna entre corchetes. Al buscar coincidencias con las columnas a las que se hace referencia entre corchetes, se tienen en cuenta las mayúsculas y minúsculas. También se hace referencia al nombre de columna sin tener en cuenta las mayúsculas y minúsculas.
SELECT raw:owner, RAW:owner FROM store_data
+-------+-------+
| owner | owner |
+-------+-------+
| amy | amy |
+-------+-------+
-- References are case sensitive when you use brackets
SELECT raw:OWNER case_insensitive, raw:['OWNER'] case_sensitive FROM store_data
+------------------+----------------+
| case_insensitive | case_sensitive |
+------------------+----------------+
| amy | null |
+------------------+----------------+
Use tildes agudas para los espacios de escape y los caracteres especiales. Al buscar coincidencias con los nombres de campo, se tienen en cuenta las mayúsculas y minúsculas.
-- 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
+----------+----------+-----------+
| zip code | Zip Code | fb:testid |
+----------+----------+-----------+
| 94025 | 94025 | 1234 |
+----------+----------+-----------+
Nota:
Si un registro JSON contiene varias columnas que pueden coincidir con la ruta de acceso de extracción sin tener en cuenta las mayúsculas y minúsculas, recibirá un error en el que se le pedirá que use corchetes. Si hay coincidencias de columnas entre filas, no recibirá ningún error. Lo siguiente devolverá un error: {"foo":"bar", "Foo":"bar"}; sin embargo, lo siguiente no devolverá un error:
{"foo":"bar"}
{"Foo":"bar"}
Extracción de campos anidados
Los campos anidados se especifican mediante notación de puntos o mediante corchetes. Cuando se usan corchetes, se buscan coincidencias con las columnas teniendo en cuenta las mayúsculas y minúsculas.
-- Use dot notation
SELECT raw:store.bicycle FROM store_data
-- the column returned is a string
+------------------+
| bicycle |
+------------------+
| { |
| "price":19.95, |
| "color":"red" |
| } |
+------------------+
-- Use brackets
SELECT raw:store['bicycle'], raw:store['BICYCLE'] FROM store_data
+------------------+---------+
| bicycle | BICYCLE |
+------------------+---------+
| { | null |
| "price":19.95, | |
| "color":"red" | |
| } | |
+------------------+---------+
Extracción de valores de matrices
Los elementos de las matrices se indexan con corchetes. Los índices tienen base 0. Puede usar un asterisco (*) seguido de la notación de puntos o corchetes para extraer subcampos de todos los elementos de una matriz.
-- Index elements
SELECT raw:store.fruit[0], raw:store.fruit[1] FROM store_data
+------------------+-----------------+
| fruit | fruit |
+------------------+-----------------+
| { | { |
| "weight":8, | "weight":9, |
| "type":"apple" | "type":"pear" |
| } | } |
+------------------+-----------------+
-- Extract subfields from arrays
SELECT raw:store.book[*].isbn FROM store_data
+--------------------+
| isbn |
+--------------------+
| [ |
| 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"} | | |
| ] | ] | ] | | |
+----------------------------+------------------+---------------------+---------------------------------+----------+
Conversión de valores
Puede usar :: para convertir valores a tipos de datos básicos. Use el método from_json para convertir resultados anidados a tipos de datos más complejos, como matrices o estructuras.
-- price is returned as a double, not a string
SELECT raw:store.bicycle.price::double FROM store_data
+------------------+
| price |
+------------------+
| 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
-- the column returned is a struct containing the columns price and color
+------------------+
| bicycle |
+------------------+
| { |
| "price":19.95, |
| "color":"red" |
| } |
+------------------+
SELECT from_json(raw:store.basket[*], 'array<array<string>>') baskets FROM store_data
-- the column returned is an array of string arrays
+------------------------------------------+
| basket |
+------------------------------------------+
| [ |
| ["1","2","{\"b\":\"y\",\"a\":\"x\"}]", |
| ["3","4"], |
| ["5","6"] |
| ] |
+------------------------------------------+
Comportamiento de NULL
Cuando exista un campo JSON con un valor null, recibirá un valor null de SQL para dicha columna, no un valor de texto null.
select '{"key":null}':key is null sql_null, '{"key":null}':key == 'null' text_null
+-------------+-----------+
| sql_null | text_null |
+-------------+-----------+
| true | null |
+-------------+-----------+
Transformación de datos anidados mediante operadores de Spark SQL
Apache Spark tiene varias funciones integradas para trabajar con datos complejos y anidados. El cuaderno siguiente contiene ejemplos.
Además, las funciones de orden superior proporcionan muchas opciones adicionales cuando los operadores integrados de Spark no están disponibles para transformar los datos de la manera que usted quiere.