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

Funciones relacionadas

• : operador