Interroger des données de variante

Cet article décrit les opérateurs SQL Databricks que vous pouvez utiliser pour interroger et transformer des données semi-structurées stockées au format JSON VARIANT. Le type de données VARIANT est disponible dans Databricks Runtime 15.3 et versions ultérieures.

Databricks recommande d’utiliser VARIANT sur les chaînes JSON. Pour les utilisateurs qui utilisent actuellement des chaînes JSON qui cherchent à migrer, consultez Comment la variante diffère-t-elle des chaînes JSON ?.

Si vous souhaitez voir des exemples d’interrogation de données semi-structurées stockées avec des chaînes JSON, consultez Chaînes JSON de requête.

Créer une table avec une colonne variant

Exécutez la requête suivante pour créer une table avec des données hautement imbriquées stockées en tant que VARIANT. Les exemples de cet article font tous référence à cette table.

CREATE TABLE store_data AS
SELECT parse_json(
  '{
    "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

Interroger des champs dans une colonne variant

La syntaxe d’interrogation de chaînes JSON et d’autres types de données complexes sur Azure Databricks s’applique aux données VARIANT, notamment les suivantes :

• Utilisez : pour sélectionner des champs de niveau supérieur.
• Utilisez . ou [<key>] pour sélectionner des champs imbriqués avec des clés nommées.
• Utilisez [<index>] pour sélectionner des valeurs dans des tableaux.

SELECT raw:['zip.code'] FROM store_data

Extraire un champ de variante de niveau supérieur

Pour extraire un champ, spécifiez le nom du champ JSON dans votre chemin d’extraction. Les noms de champs respectent toujours la casse.

SELECT raw:owner FROM store_data

+-------+
| owner |
+-------+
| "amy" |
+-------+

-- Use backticks to escape special characters.
SELECT raw:`zip code`, raw:`fb:testid` FROM store_data

+----------+-----------+
| zip code | fb:testid |
+----------+-----------+
| "94025"  | "1234"    |
+----------+-----------+

Si un chemin d’accès est introuvable, le résultat est NULL de type VARIANT.

Extraire des champs imbriqués variants

Vous spécifiez des champs imbriqués à l’aide de la notation par points ou entre crochets. Les noms de champs respectent toujours la casse.

-- Use dot notation
SELECT raw:store.bicycle FROM store_data

+------------------+
| bicycle          |
+------------------+
| {                |
|   "color":"red", |
|   "price":19.95  |
| }                |
+------------------+

-- Use brackets
SELECT raw:store['bicycle'] FROM store_data

+------------------+
| bicycle          |
+------------------+
| {                |
|   "color":"red", |
|   "price":19.95  |
| }                |
+------------------+

Si un chemin d’accès est introuvable, le résultat est NULL de type VARIANT.

Extraire des valeurs à partir de tableaux de variantes

Vous indexez des éléments dans des tableaux à l’aide de crochets. Les indices sont de base 0.

-- Index elements
SELECT raw:store.fruit[0], raw:store.fruit[1] FROM store_data

+-------------------+------------------+
| fruit             | fruit            |
+-------------------+------------------+
| {                 | {                |
|   "type":"apple", |   "type":"pear", |
|   "weight":8      |   "weight":9     |
| }                 | }                |
+-------------------+------------------+

Si le chemin d’accès est introuvable ou si l’index de tableau est hors limites, le résultat est NULL.

Aplatir les objets et tableaux de variantes

La fonction de générateur de valeurs de tableau variant_explode peut être utilisée pour aplatir les tableaux et les objets VARIANT.

Étant donné que variant_explode est une fonction de générateur, vous l’utilisez dans le cadre de la clause FROM plutôt que dans la liste SELECT, comme dans les exemples suivants :

SELECT key, value
  FROM store_data,
  LATERAL variant_explode(store_data.raw:store);

+-------+--------------------+
|    key|               value|
+-------+--------------------+
| basket|[[1,2,{"a":"x","b...|
|bicycle|{"color":"red","p...|
|   book|[{"author":"Nigel...|
|  fruit|[{"type":"apple",...|
+-------+--------------------+

SELECT pos, value
  FROM store_data,
  LATERAL variant_explode(store_data.raw:store.basket[0]);

+---+-----------------+
|pos|            value|
+---+-----------------+
|  0|                1|
|  1|                2|
|  2|{"a":"x","b":"y"}|
+---+-----------------+

Règles de distribution des types de variantes

Vous pouvez stocker des tableaux et des scalaires en utilisant le type VARIANT. Lorsque vous essayez de convertir des types de variantes en d’autres types, les règles de distribution normales s’appliquent aux valeurs et champs individuels, avec les règles supplémentaires suivantes.

Lors de l’inférence de type avec schema_of_variant ou schema_of_variant_agg, les fonctions reviennent au type VARIANT plutôt qu’au type STRING lorsque des types en conflit sont présents et ne peuvent pas être résolus.

Vous pouvez utiliser :: ou cast pour distribuer des valeurs aux types de données pris en charge.

-- price is returned as a double, not a string
SELECT raw:store.bicycle.price::double FROM store_data

+------------------+
| price            |
+------------------+
| 19.95            |
+------------------+

-- cast into more complex types
SELECT cast(raw:store.bicycle AS STRUCT<price DOUBLE, color STRING>) bicycle FROM store_data;
-- `::` also supported
SELECT raw:store.bicycle::STRUCT<price DOUBLE, color STRING> bicycle FROM store_data;

+------------------+
| bicycle          |
+------------------+
| {                |
|   "price":19.95, |
|   "color":"red"  |
| }                |
+------------------+

Règles null de variante

Les variantes peuvent contenir deux types de valeurs Null :

• SQL NULL : SQL NULLindique que la valeur est manquante. Il s’agit des mêmes NULLque lors de la gestion des données structurées.
• Variante NULL : les variantes NULL indiquent que la variante contient explicitement une valeur NULL. Elles ne sont pas identiques aux NULL de SQL, car la valeur NULL est stockée dans les données.

Utilisez la fonction is_variant_null pour déterminer si la valeur de variante est une variante NULL.

SELECT
  is_variant_null(parse_json(NULL)) AS sql_null,
  is_variant_null(parse_json('null')) AS variant_null,
  is_variant_null(parse_json('{ "field_a": null }'):field_a) AS variant_null_value,
  is_variant_null(parse_json('{ "field_a": null }'):missing) AS missing_sql_value_null

+--------+------------+------------------+----------------------+
|sql_null|variant_null|variant_null_value|missing_sql_value_null|
+--------+------------+------------------+----------------------+
|   false|        true|              true|                 false|
+--------+------------+------------------+----------------------+