Aller au contenu principal
Dernière mise à jour le

Interroger les chaînes JSON

Cet article décrit les opérateurs Databricks SQL que vous pouvez utiliser pour interroger et transformer des données semi-structurées stockées en tant que chaînes JSON.

remarque

Cette fonctionnalité vous permet de lire des données semi-structurées sans aplatir les fichiers. Cependant, pour des performances optimales des queries en lecture, Databricks vous recommande d'extraire les colonnes imbriquées avec les types de données corrects.

Vous extrayez une colonne de champs contenant des chaînes JSON en utilisant la syntaxe <column-name>:<extraction-path>, où <column-name> est le nom de la colonne de chaîne et <extraction-path> est le chemin d’accès au champ à extraire. Les résultats renvoyés sont des chaînes de caractères.

Créer une table avec des données fortement imbriquées​

Exécutez la query suivante pour créer une table avec des données fortement imbriquées. Les exemples de cet article font tous référence à cette table.

SQL
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

Extraire une colonne de niveau supérieur​

Pour extraire une colonne, spécifiez le nom du champ JSON dans votre chemin d'extraction.

Vous pouvez fournir les noms de colonne entre parenthèses. Les colonnes référencées entre crochets sont mises en correspondance de manière sensible à la casse . Le nom de la colonne est également référencé de manière insensible à la casse.

SQL
SELECT raw:owner, RAW:owner FROM store_data
+-------+-------+
| owner | owner |
+-------+-------+
| amy   | amy   |
+-------+-------+
SQL
-- 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           |
+------------------+----------------+

Utilisez des accents graves pour échapper les espaces et les caractères spéciaux. Les noms de champ ne sont pas sensibles à la casse.

SQL
-- 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      |
+----------+----------+-----------+
remarque

Si un enregistrement JSON contient plusieurs colonnes pouvant correspondre à votre chemin d'extraction en raison d'une correspondance insensible à la casse, vous recevrez une erreur vous invitant à utiliser des crochets. Si vous avez des correspondances de colonnes entre les lignes, vous ne recevrez aucune erreur. Ce qui suit générera une erreur : {"foo":"bar", "Foo":"bar"}, et ce qui suit ne générera pas d'erreur :

{"foo":"bar"}
{"Foo":"bar"}

Extraire les champs imbriqués​

Vous spécifiez les champs imbriqués via la notation par points ou en utilisant des crochets. Lorsque vous utilisez des crochets, les colonnes sont mises en correspondance en respectant la casse.

SQL
-- Use dot notation
SELECT raw:store.bicycle FROM store_data
-- the column returned is a string
+------------------+
| bicycle          |
+------------------+
| {                |
|   "price":19.95, |
|   "color":"red"  |
| }                |
+------------------+
SQL
-- Use brackets
SELECT raw:store['bicycle'], raw:store['BICYCLE'] FROM store_data
+------------------+---------+
| bicycle          | BICYCLE |
+------------------+---------+
| {                | null    |
|   "price":19.95, |         |
|   "color":"red"  |         |
| }                |         |
+------------------+---------+

Extraire des valeurs de tableaux​

Vous indexez les éléments des tableaux avec des crochets. Les index sont basés sur 0. Vous pouvez utiliser un astérisque (*), suivi d'une notation par points ou par crochets, pour extraire des sous-champs de tous les éléments d'un tableau.

remarque

La syntaxe [*] n'est valide que dans une expression de chemin JSON, suivant l'opérateur: sur une colonne de chaîne JSON. Ce n'est pas pris en charge pour :

  • Colonnes ARRAY natives. L'application de [*] à une colonne de tableau renvoie l'erreur [INVALID_USAGE_OF_STAR_OR_REGEX]. Pour extraire un champ de chaque élément d'un tableau de structs, utilisez array_column.field_name, transformer ou exploser à la place.
  • VARIANT colonnes. Voir Comment query les données de variants ?.
SQL
-- Index elements
SELECT raw:store.fruit[0], raw:store.fruit[1] FROM store_data
+------------------+-----------------+
| fruit            | fruit           |
+------------------+-----------------+
| {                | {               |
|   "weight":8,    |   "weight":9,   |
|   "type":"apple" |   "type":"pear" |
| }                | }               |
+------------------+-----------------+
SQL
-- Extract subfields from arrays
SELECT raw:store.book[*].isbn FROM store_data
+--------------------+
| isbn               |
+--------------------+
| [                  |
|   null,            |
|   "0-553-21311-3", |
|   "0-395-19395-8"  |
| ]                  |
+--------------------+
SQL
-- 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"} |                                 |          |
| ]                          | ]                | ]                   |                                 |          |
+----------------------------+------------------+---------------------+---------------------------------+----------+

Conversion des valeurs​

Vous pouvez utiliser :: pour convertir les valeurs en types de données de base. Utilisez la méthode from_json pour convertir les résultats imbriqués en types de données plus complexes, tels que des tableaux ou des structs.

SQL
-- price is returned as a double, not a string
SELECT raw:store.bicycle.price::double FROM store_data
+------------------+
| price            |
+------------------+
| 19.95            |
+------------------+
SQL
-- 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"  |
| }                |
+------------------+
SQL
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"]                              |
| ]                                        |
+------------------------------------------+

Comportement NULL​

Lorsqu’un champ JSON existe avec une valeur null, vous recevrez une valeur SQL null pour cette colonne, et non une valeur de texte null.

SQL
select '{"key":null}':key is null sql_null, '{"key":null}':key == 'null' text_null
+-------------+-----------+
| sql_null    | text_null |
+-------------+-----------+
| true        | null      |
+-------------+-----------+

Transformer les données imbriquées à l'aide d'opérateurs Spark SQL​

Apache Spark dispose d'un certain nombre de fonctions intégrées pour travailler avec des données complexes et imbriquées. Le notebook suivant contient des exemples.

De plus, les fonctions d'ordre supérieur offrent de nombreuses options supplémentaires lorsque les opérateurs Spark intégrés ne sont pas disponibles pour transformer les données comme vous le souhaitez.

Notebook de données imbriquées complexes

Ouvrir le notebook dans un nouvel ongletOuvrir dans Databricks
Sur cette page