Expression de chemin JSON

S’applique à : Databricks SQL Databricks Runtime

Une expression de chemin JSON est utilisée pour extraire des valeurs d’une chaîne JSON ou d’un VARIANT à l’aide de l’opérateur : (deux-points)

Syntaxe

{ { identifier | [ field ] | [ * ] | [ index ] }
  [ . identifier | [ field ] | [ * ] | [ index ] ] [...] }

Les crochets entourant field, * et index sont des crochets réels et n’indiquent pas une syntaxe facultative.

Paramètres

• identificateur : un identificateur d’un champ JSON. Pour les types JSON STRING, l’identificateur ne respecte pas la casse. Pour les types VARIANT, il respecte la casse.
• [ field ] : un littéral STRING respectant la casse entre crochets identifiant un champ JSON.
• [ * ] : identification de tous les éléments figurant dans un tableau JSON. Cette syntaxe n’est pas prise en charge pour VARIANT.
• [ index ] : littéral entier identifiant un élément spécifique dans un tableau JSON basé sur zéro.

Retours

Un STRING si l’expression en cours de navigation est un STRING. Un VARIANT si l’expression en cours de navigation est un VARIANT.

Quand il existe un champ JSON avec une valeur null non délimitée, vous recevez une valeur SQL NULL pour cette colonne, non une valeur de texte null.

Vous pouvez utiliser l’opérateur :: pour caster les valeurs en types de données de base.

Utilisez la fonction from_json pour caster des résultats imbriqués en types de données plus complexes, tels que des tableaux ou des structs.

Notes

Vous pouvez utiliser un identificateur non délimité pour référencer un champ JSON si le nom ne contient pas d’espaces ou de caractères spéciaux et s’il n’existe aucun champ dans le STRING JSON portant le même nom avec une casse différente.

Utilisez un identificateur délimité s’il n’existe aucun champ du même nom dans une casse différente.

La notation [ field ] peut toujours être utilisée, mais vous oblige à respecter exactement la casse du champ.

Si Azure Databricks ne peut pas identifier un champ de manière unique, une erreur est renvoyée. Si aucune correspondance n’est trouvée pour aucun champ, Azure Databricks renvoie NULL.

Une valeur NULL peut être encodée dans un VARIANT, et cette valeur n’est pas un NULL SQL. Par conséquent, parse_json('null') IS NULL est false, mais is_variant_null(parse_json('null')) est true. Une valeur nulle encodée en VARIANT peut être convertie en NULL SQL en le forçant sur un type quelconque. Par exemple, parse_json('null')::int IS NULL est true.

Exemples

Les exemples suivants utilisent les données créées avec l’instruction dans Exemple de données.

Dans cette section :

• Extraire à l’aide d’un identificateur et de délimiteurs
• Extraire les champs imbriqués
• Extraire des valeurs des tableaux
• Comportement NULL
• Convertir les valeurs
• Expressions VARIANT
• Exemple de données

Extraire à l’aide d’un identificateur et de délimiteurs

> 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

Extraire les champs imbriqués

-- 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" }'

Extraire des valeurs des tableaux

-- 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"}
  ]                            ]                  ]

Comportement NULL

> SELECT '{"key":null}':key IS NULL sql_null, '{"key":"null"}':key IS NULL;
  true          false

Convertir les valeurs

-- 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"]
  ]'

Expressions 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

Exemple de données

> 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;

Fonctions connexes

• : opérateur