Expresión de ruta de acceso JSON

Se aplica a: Databricks SQL Databricks Runtime

Una expresión de ruta de acceso de JSON se usa para extraer valores de una cadena JSON o VARIANT 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

• identificador: identificador de un campo JSON. En el caso de los tipos STRING de JSON, el identificador no distingue mayúsculas y minúsculas. Para los tipos VARIANT, distingue entre mayúsculas y minúsculas.
• [ field ]: literal STRING entre corchetes que distingue mayúsculas y minúsculas, e identifica un campo JSON.
• [ * ]: identificación de todos los elementos de una matriz JSON. Esta sintaxis no se admite para VARIANT.
• [ index ]: literal entero que identifica un elemento concreto de una matriz JSON basada en 0.

Devoluciones

Una instancia de STRING si la expresión por la que se navega es una instancia de STRING. Una instancia de VARIANT si la expresión por la que se navega es una instancia de VARIANT.

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 en la instancia STRING de JSON 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 Azure Databricks no puede identificar de forma única un campo, se devuelve un error. Si no se encuentra ninguna coincidencia en ningún campo, Azure Databricks devuelve NULL.

Un valor NULL se puede codificar dentro de una instancia de VARIANTy ese valor no es un elemento NULLde SQL. Por tanto, parse_json('null') IS NULL es false, pero is_variant_null(parse_json('null')) es true. Un valor null codificado con VARIANT se puede convertir en un elemento NULL de SQL mediante la conversión a algún tipo. Por ejemplo, parse_json('null')::int IS NULL es true.

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
• Expresiones VARIANT
• 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"]
  ]'

Expresiones 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

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

 > CREATE TABLE store_data_variant AS SELECT parse_json(raw) FROM store_data;

Funciones relacionadas

• : operador