Interrogation de données semi-structurées

Cette rubrique explique comment utiliser des opérateurs et des fonctions spéciales pour interroger des données hiérarchiques complexes stockées dans un VARIANT.

(Pour des exemples simples d’extraction de valeurs de ARRAYs et OBJECTs, voir Accès aux éléments d’un ARRAY par indice ou par tranche et Accès aux éléments d’un OBJECT par clé).

En général, les données hiérarchiques ont été importées dans un VARIANT à partir de l’un des formats de données pris en charge suivants :

  • JSON

  • Avro

  • ORC

  • Parquet

(Pour plus d’informations sur l’interrogation de données XML [par exemple, des données qui proviennent d’un format de données XML et ont été converties en OBJECT en appelant PARSE_XML], voir XMLGET).

Astuce

Vous pouvez utiliser le service d’optimisation de recherche pour améliorer les performances des requêtes. Pour plus de détails, voir Service d’optimisation de la recherche.

Dans ce chapitre :

Échantillon de données utilisé dans des exemples

Sauf indication contraire, les exemples de ce chapitre se réfèrent à un tableau nommé car_sales qui contient une seule colonne VARIANT nommée src : Ce VARIANT contient des ARRAYs et OBJECTs imbriqués.

Créer la table et la charger :

CREATE OR REPLACE TABLE car_sales
( 
  src variant
)
AS
SELECT PARSE_JSON(column1) AS src
FROM VALUES
('{ 
    "date" : "2017-04-28", 
    "dealership" : "Valley View Auto Sales",
    "salesperson" : {
      "id": "55",
      "name": "Frank Beasley"
    },
    "customer" : [
      {"name": "Joyce Ridgely", "phone": "16504378889", "address": "San Francisco, CA"}
    ],
    "vehicle" : [
      {"make": "Honda", "model": "Civic", "year": "2017", "price": "20275", "extras":["ext warranty", "paint protection"]}
    ]
}'),
('{ 
    "date" : "2017-04-28", 
    "dealership" : "Tindel Toyota",
    "salesperson" : {
      "id": "274",
      "name": "Greg Northrup"
    },
    "customer" : [
      {"name": "Bradley Greenbloom", "phone": "12127593751", "address": "New York, NY"}
    ],
    "vehicle" : [
      {"make": "Toyota", "model": "Camry", "year": "2017", "price": "23500", "extras":["ext warranty", "rust proofing", "fabric protection"]}  
    ]
}') v;
Copy

Sélectionner des données :

SELECT * FROM car_sales;
+-------------------------------------------+
| SRC                                       |
|-------------------------------------------|
| {                                         |
|   "customer": [                           |
|     {                                     |
|       "address": "San Francisco, CA",     |
|       "name": "Joyce Ridgely",            |
|       "phone": "16504378889"              |
|     }                                     |
|   ],                                      |
|   "date": "2017-04-28",                   |
|   "dealership": "Valley View Auto Sales", |
|   "salesperson": {                        |
|     "id": "55",                           |
|     "name": "Frank Beasley"               |
|   },                                      |
|   "vehicle": [                            |
|     {                                     |
|       "extras": [                         |
|         "ext warranty",                   |
|         "paint protection"                |
|       ],                                  |
|       "make": "Honda",                    |
|       "model": "Civic",                   |
|       "price": "20275",                   |
|       "year": "2017"                      |
|     }                                     |
|   ]                                       |
| }                                         |
| {                                         |
|   "customer": [                           |
|     {                                     |
|       "address": "New York, NY",          |
|       "name": "Bradley Greenbloom",       |
|       "phone": "12127593751"              |
|     }                                     |
|   ],                                      |
|   "date": "2017-04-28",                   |
|   "dealership": "Tindel Toyota",          |
|   "salesperson": {                        |
|     "id": "274",                          |
|     "name": "Greg Northrup"               |
|   },                                      |
|   "vehicle": [                            |
|     {                                     |
|       "extras": [                         |
|         "ext warranty",                   |
|         "rust proofing",                  |
|         "fabric protection"               |
|       ],                                  |
|       "make": "Toyota",                   |
|       "model": "Camry",                   |
|       "price": "23500",                   |
|       "year": "2017"                      |
|     }                                     |
|   ]                                       |
| }                                         |
+-------------------------------------------+
Copy

Parcours de données semi-structurées

Insérez deux-points : entre le nom de la colonne VARIANT et un élément de premier niveau quelconque : <colonne>:<élément_niveau1>.

Note

Dans les exemples suivants, la sortie de requête est entourée de guillemets doubles, car la sortie de la requête est VARIANT et non VARCHAR. (Les valeurs VARIANT ne sont pas des chaînes ; les valeurs VARIANT contiennent des chaînes.) Les opérateurs :, . et [] renvoient toujours des valeurs VARIANT contenant des chaînes.

Par exemple, obtenez une liste de tous les noms de concessionnaire :

SELECT src:dealership
    FROM car_sales
    ORDER BY 1;
+--------------------------+
| SRC:DEALERSHIP           |
|--------------------------|
| "Tindel Toyota"          |
| "Valley View Auto Sales" |
+--------------------------+
Copy

Il existe deux façons d’accéder aux éléments d’un JSON objet :

Important

Quelle que soit la notation que vous utilisez, le nom de colonne n’est pas sensible à la casse, mais les noms d’élément le sont. Par exemple, dans la liste suivante, les deux premiers chemins sont équivalents, mais pas le troisième :

  • src:salesperson.name

  • SRC:salesperson.name

  • SRC:Salesperson.Name

Notation par points

Utilisez la notation par points pour parcourir un chemin dans un objet JSON : <colonne>:<élément_niveau1>.<élément_niveau2>.<élément_niveau3>. Vous pouvez aussi inclure les noms d’élément entre guillemets doubles : <colonne>:"<élément_niveau1>"."<élément_niveau2>"."<élément_niveau3>".

Note

Les règles pour les clés JSON (noms d’élément) sont différentes des règles pour les identificateurs SQL Snowflake.

Pour plus d’informations sur les règles concernant les identificateurs SQL de Snowflake, voir : Exigences relatives à l’identificateur.

Pour plus d’informations sur les clés JSON, voir http://json.org, en particulier la description d’une « chaîne ».

Si le nom d’un élément n’est pas conforme aux règles d’identification SQL de Snowflake, par exemple s’il contient des espaces, alors vous devez mettre le nom entre guillemets. Vous trouverez ci-dessous quelques exemples (qui ne proviennent pas tous de l’exemple car_sales ci-dessus) de noms d’élément JSON valides qui ne sont pas des noms d’identificateurs Snowflake valides, sauf s’ils sont entourés de guillemets :

-- This contains a blank.
SELECT src:"company name" FROM partners;

-- This does not start with a letter or underscore.
SELECT zipcode_info:"94987" FROM addresses;

-- This contains characters that are not letters, digits, or underscores, and
-- it does not start with a letter or underscore.
SELECT measurements:"#sPerSquareInch" FROM english_metrics;
Copy

Obtenez les noms de tous les vendeurs qui ont vendu des voitures :

SELECT src:salesperson.name
    FROM car_sales
    ORDER BY 1;
+----------------------+
| SRC:SALESPERSON.NAME |
|----------------------|
| "Frank Beasley"      |
| "Greg Northrup"      |
+----------------------+
Copy

Notation par crochets

Vous pouvez également utiliser la notation par crochets pour parcourir le chemin d’un objet : <colonne>['<élément_niveau1>']['<élément_niveau2>']. Mettez les noms d’élément entre guillemets simples. Les valeurs sont récupérées sous forme de chaînes.

Obtenez les noms de tous les vendeurs qui ont vendu des voitures :

SELECT src['salesperson']['name']
    FROM car_sales
    ORDER BY 1;
+----------------------------+
| SRC['SALESPERSON']['NAME'] |
|----------------------------|
| "Frank Beasley"            |
| "Greg Northrup"            |
+----------------------------+
Copy

Récupération d’une instance unique d’un élément répétitif

Récupérez une instance numérotée spécifique d’un élément enfant dans un tableau répétitif en ajoutant un prédicat numéroté (à partir de 0) à la référence de tableau.

Notez que pour récupérer toutes instances d’un tableau enfant dans un tableau répétitif, il est nécessaire d’aplatir l’ensemble. Voir un exemple dans Utilisation de la fonction FLATTEN pour analyser des tableaux dans ce chapitre.

Obtenez les détails du véhicule pour chaque vente :

SELECT src:customer[0].name, src:vehicle[0]
    FROM car_sales
    ORDER BY 1;
+----------------------+-------------------------+
| SRC:CUSTOMER[0].NAME | SRC:VEHICLE[0]          |
|----------------------+-------------------------|
| "Bradley Greenbloom" | {                       |
|                      |   "extras": [           |
|                      |     "ext warranty",     |
|                      |     "rust proofing",    |
|                      |     "fabric protection" |
|                      |   ],                    |
|                      |   "make": "Toyota",     |
|                      |   "model": "Camry",     |
|                      |   "price": "23500",     |
|                      |   "year": "2017"        |
|                      | }                       |
| "Joyce Ridgely"      | {                       |
|                      |   "extras": [           |
|                      |     "ext warranty",     |
|                      |     "paint protection"  |
|                      |   ],                    |
|                      |   "make": "Honda",      |
|                      |   "model": "Civic",     |
|                      |   "price": "20275",     |
|                      |   "year": "2017"        |
|                      | }                       |
+----------------------+-------------------------+
Copy

Obtenez le prix de chaque voiture vendue :

SELECT src:customer[0].name, src:vehicle[0].price
    FROM car_sales
    ORDER BY 1;
+----------------------+----------------------+
| SRC:CUSTOMER[0].NAME | SRC:VEHICLE[0].PRICE |
|----------------------+----------------------|
| "Bradley Greenbloom" | "23500"              |
| "Joyce Ridgely"      | "20275"              |
+----------------------+----------------------+
Copy

Valeurs de conversion explicites

Lorsque vous extrayez des valeurs d’un VARIANT, vous pouvez explicitement convertir ces valeurs dans le type de données souhaité. Par exemple, vous pouvez extraire les prix sous forme de valeurs numériques et effectuer des calculs sur celles-ci :

SELECT src:vehicle[0].price::NUMBER * 0.10 AS tax
    FROM car_sales
    ORDER BY tax;
+--------+
|    TAX |
|--------|
| 2027.5 |
| 2350.0 |
+--------+
Copy

Par défaut, lorsque VARCHARs, DATEs, TIMEs et TIMESTAMPs sont extraits d’une colonne VARIANT, les valeurs sont entourées de guillemets doubles. Vous pouvez éliminer les guillemets en convertissant explicitement les valeurs. Par exemple :

SELECT src:dealership, src:dealership::VARCHAR
    FROM car_sales
    ORDER BY 2;
+--------------------------+-------------------------+
| SRC:DEALERSHIP           | SRC:DEALERSHIP::VARCHAR |
|--------------------------+-------------------------|
| "Tindel Toyota"          | Tindel Toyota           |
| "Valley View Auto Sales" | Valley View Auto Sales  |
+--------------------------+-------------------------+
Copy

Pour plus d’informations sur la conversion des valeurs de VARIANT, voir Utilisation des valeurs dans un VARIANT.

Pour plus d’informations sur la conversion en général, voir Conversion de type de données.

Using FLATTEN to Filter the Results in a WHERE Clause

The FLATTEN function explodes nested values into separate columns. You can use the function to filter query results in a WHERE clause.

The following example returns key-value pairs that match a WHERE clause and displays them in separate columns:

CREATE TABLE pets (v variant);

INSERT INTO pets SELECT PARSE_JSON ('{"species":"dog", "name":"Fido", "is_dog":"true"} ');
INSERT INTO pets SELECT PARSE_JSON ('{"species":"cat", "name":"Bubby", "is_dog":"false"}');
INSERT INTO pets SELECT PARSE_JSON ('{"species":"cat", "name":"dog terror", "is_dog":"false"}');

SELECT a.v, b.key, b.value FROM pets a,LATERAL FLATTEN(input => a.v) b
WHERE b.value LIKE '%dog%';

+-------------------------+---------+--------------+
| V                       | KEY     | VALUE        |
|-------------------------+---------+--------------|
| {                       | species | "dog"        |
|   "is_dog": "true",     |         |              |
|   "name": "Fido",       |         |              |
|   "species": "dog"      |         |              |
| }                       |         |              |
| {                       | name    | "dog terror" |
|   "is_dog": "false",    |         |              |
|   "name": "dog terror", |         |              |
|   "species": "cat"      |         |              |
| }                       |         |              |
+-------------------------+---------+--------------+
Copy

Using FLATTEN to List Distinct Key Names

When working with unfamiliar semi-structured data, you might not know the key names in an OBJECT. You can use the FLATTEN function with the RECURSIVE argument to return the list of distinct key names in all nested elements in an OBJECT:

SELECT REGEXP_REPLACE(f.path, '\\[[0-9]+\\]', '[]') AS "Path",
  TYPEOF(f.value) AS "Type",
  COUNT(*) AS "Count"
FROM <table>,
LATERAL FLATTEN(<variant_column>, RECURSIVE=>true) f
GROUP BY 1, 2 ORDER BY 1, 2;
Copy

The REGEXP_REPLACE function removes the array index values (e.g. [0]) and replaces them with brackets ([]) to group array elements.

For example:

{"a": 1, "b": 2, "special" : "data"}   <--- row 1 of VARIANT column
{"c": 3, "d": 4, "normal" : "data"}    <----row 2 of VARIANT column

Output from query:

+---------+---------+-------+
| Path    | Type    | Count |
|---------+---------+-------|
| a       | INTEGER |     1 |
| b       | INTEGER |     1 |
| c       | INTEGER |     1 |
| d       | INTEGER |     1 |
| normal  | VARCHAR |     1 |
| special | VARCHAR |     1 |
+---------+---------+-------+
Copy

Using FLATTEN to List Paths in an OBJECT

Related to Using FLATTEN to List Distinct Key Names, you can use the FLATTEN function with the RECURSIVE argument to retrieve all keys and paths in an OBJECT.

The following query returns keys, paths, and values (including VARIANT « null » values) for all data types stored in a VARIANT column. The code assumes that the VARIANT column contains an OBJECT in each row.

SELECT
  t.<variant_column>,
  f.seq,
  f.key,
  f.path,
  REGEXP_COUNT(f.path,'\\.|\\[') +1 AS Level,
  TYPEOF(f.value) AS "Type",
  f.index,
  f.value AS "Current Level Value",
  f.this AS "Above Level Value"
FROM <table> t,
LATERAL FLATTEN(t.<variant_column>, recursive=>true) f;
Copy

The following query is similar to the first query, but excludes nested OBJECTs and ARRAYs:

SELECT
  t.<variant_column>,
  f.seq,
  f.key,
  f.path,
  REGEXP_COUNT(f.path,'\\.|\\[') +1 AS Level,
  TYPEOF(f.value) AS "Type",
  f.value AS "Current Level Value",
  f.this AS "Above Level Value"
FROM <table> t,
LATERAL FLATTEN(t.<variant_column>, recursive=>true) f
WHERE "Type" NOT IN ('OBJECT','ARRAY');
Copy

The queries return the following values:

<variant_column>

OBJECT stored as a row in the VARIANT column.

Seq

Unique sequence number associated with the data in the row.

Key

String associated with a value in the data structure.

Path

Path to the element within the data structure.

Level

Level of the key-value pair within the data structure.

Type

Data type for the value.

Index

Index of the element in the data structure. Applies to ARRAY values only; otherwise NULL.

Current Level Value

Value at the current level in the data structure.

Above Level Value

Value one level higher in the data structure.

Utilisation de la fonction FLATTEN pour analyser des tableaux

Analysez un tableau à l’aide de la fonction FLATTEN. FLATTEN est une fonction de table qui produit une vue latérale d’une colonne VARIANT, OBJECT ou ARRAY. La fonction retourne une ligne pour chaque objet, et le modificateur LATERAL joint les données avec toute information en dehors de l’objet.

Obtenez les noms et adresses de tous les clients. Convertissez la sortie VARIANT en valeurs de chaîne :

SELECT
  value:name::string as "Customer Name",
  value:address::string as "Address"
  FROM
    car_sales
  , LATERAL FLATTEN(INPUT => SRC:customer);

+--------------------+-------------------+
| Customer Name      | Address           |
|--------------------+-------------------|
| Joyce Ridgely      | San Francisco, CA |
| Bradley Greenbloom | New York, NY      |
+--------------------+-------------------+
Copy

Utilisation de la fonction FLATTEN pour analyser des tableaux imbriqués

Le tableau extras est imbriqué dans le tableau vehicle dans les données d’échantillon :

"vehicle" : [
     {"make": "Honda", "model": "Civic", "year": "2017", "price": "20275", "extras":["ext warranty", "paint protection"]}
   ]
Copy

Ajoutez une deuxième clause FLATTEN pour aplatir le tableau extras à l’intérieur du tableau vehicle aplati et récupérer les « extras » achetés pour chaque voiture vendue :

SELECT
  vm.value:make::string as make,
  vm.value:model::string as model,
  ve.value::string as "Extras Purchased"
  FROM
    car_sales
    , LATERAL FLATTEN(INPUT => SRC:vehicle) vm
    , LATERAL FLATTEN(INPUT => vm.value:extras) ve
  ORDER BY make, model, "Extras Purchased";
+--------+-------+-------------------+
| MAKE   | MODEL | Extras Purchased  |
|--------+-------+-------------------|
| Honda  | Civic | ext warranty      |
| Honda  | Civic | paint protection  |
| Toyota | Camry | ext warranty      |
| Toyota | Camry | fabric protection |
| Toyota | Camry | rust proofing     |
+--------+-------+-------------------+
Copy

Analyse de texte sous forme de valeurs VARIANT à l’aide de la fonction PARSE_JSON

Analysez un texte comme un document JSON en utilisant la fonction PARSE_JSON.

Si l’entrée est NULL, la sortie sera également NULL. Cependant, si la chaîne d’entrée est null, elle est interprétée comme une valeur VARIANT null. C’est-à-dire que le résultat n’est pas un SQL NULL, mais une valeur réelle utilisée pour représenter une valeur nulle dans des formats semi-structurés.

Pour un exemple, voir Échantillon de données utilisé dans des exemples dans ce chapitre.

Extraction de valeurs à l’aide de la fonction GET

GET accepte une valeur VARIANT, OBJECT ou ARRAY comme premier argument et extrait la valeur VARIANT de l’élément dans le chemin fourni comme second argument.

Calculez et extrayez le dernier élément de chaque tableau dans une colonne VARIANT en utilisant les fonctions GET et ARRAY_SIZE. ARRAY_SIZE renvoie la taille du tableau d’entrée :

Note

Cet exemple s’écarte de la table car_sales utilisée ailleurs dans ce chapitre.

CREATE OR replace TABLE colors (v variant);

INSERT INTO
   colors
   SELECT
      parse_json(column1) AS v
   FROM
   VALUES
     ('[{r:255,g:12,b:0},{r:0,g:255,b:0},{r:0,g:0,b:255}]'),
     ('[{c:0,m:1,y:1,k:0},{c:1,m:0,y:1,k:0},{c:1,m:1,y:0,k:0}]')
    v;

SELECT *, GET(v, ARRAY_SIZE(v)-1) FROM colors;

+---------------+-------------------------+
| V             | GET(V, ARRAY_SIZE(V)-1) |
|---------------+-------------------------|
| [             | {                       |
|   {           |   "b": 255,             |
|     "b": 0,   |   "g": 0,               |
|     "g": 12,  |   "r": 0                |
|     "r": 255  | }                       |
|   },          |                         |
|   {           |                         |
|     "b": 0,   |                         |
|     "g": 255, |                         |
|     "r": 0    |                         |
|   },          |                         |
|   {           |                         |
|     "b": 255, |                         |
|     "g": 0,   |                         |
|     "r": 0    |                         |
|   }           |                         |
| ]             |                         |
| [             | {                       |
|   {           |   "c": 1,               |
|     "c": 0,   |   "k": 0,               |
|     "k": 0,   |   "m": 1,               |
|     "m": 1,   |   "y": 0                |
|     "y": 1    | }                       |
|   },          |                         |
|   {           |                         |
|     "c": 1,   |                         |
|     "k": 0,   |                         |
|     "m": 0,   |                         |
|     "y": 1    |                         |
|   },          |                         |
|   {           |                         |
|     "c": 1,   |                         |
|     "k": 0,   |                         |
|     "m": 1,   |                         |
|     "y": 0    |                         |
|   }           |                         |
| ]             |                         |
+---------------+-------------------------+
Copy

Extraction de valeurs par chemin à l’aide de la fonction GET_PATH

Extrayez une valeur d’une colonne VARIANT à l’aide de la fonction GET_PATH , :. La fonction est une variation de GET utilisée pour extraire une valeur en utilisant un nom de chemin. GET_PATH est équivalent à une chaîne de fonctions GET.

Obtenez la marque de la voiture achetée par chaque client :

SELECT GET_PATH(src, 'vehicle[0]:make') FROM car_sales;

+----------------------------------+
| GET_PATH(SRC, 'VEHICLE[0]:MAKE') |
|----------------------------------|
| "Honda"                          |
| "Toyota"                         |
+----------------------------------+
Copy

Parcours de données semi-structurées décrit la syntaxe de chemin utilisée pour récupérer des éléments d’une colonne VARIANT. La syntaxe est abrégée pour la fonction GET ou GET_PATH , :. Contrairement à la syntaxe de chemin, ces fonctions peuvent gérer des chemins irréguliers ou des éléments de chemin.

Les requêtes suivantes produisent les mêmes résultats :

SELECT GET_PATH(src, 'vehicle[0].make') FROM car_sales;

SELECT src:vehicle[0].make FROM car_sales;
Copy

Analyse de tableaux directement depuis un fichier de données en zone de préparation

Supposons qu’un fichier en zone de préparation nommé contacts.json.gz contienne les données suivantes :

{
    "root": [
        {
            "employees": [
                {
                    "firstName": "Anna",
                    "lastName": "Smith"
                },
                {
                    "firstName": "Peter",
                    "lastName": "Jones"
                }
            ]
        }
    ]
}
Copy

Supposons également qu’un format de fichier nommé my_json_format inclut TYPE=JSON dans sa définition.

Interrogez le nom du premier employé dans le fichier en zone de préparation. Dans cet exemple, le fichier est situé dans la zone de préparation de table customers, mais il peut être situé dans n’importe quelle zone de préparation interne (c-à-d. Snowflake) ou externe :

SELECT 'The First Employee Record is '||
    S.$1:root[0].employees[0].firstName||
    ' '||S.$1:root[0].employees[0].lastName
FROM @%customers/contacts.json.gz (file_format => 'my_json_format') as S;

+----------------------------------------------+
| 'THE FIRST EMPLOYEE RECORD IS '||            |
|      S.$1:ROOT[0].EMPLOYEES[0].FIRSTNAME||   |
|      ' '||S.$1:ROOT[0].EMPLOYEES[0].LASTNAME |
|----------------------------------------------|
| The First Employee Record is Anna Smith      |
+----------------------------------------------+
Copy