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;
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" |
| } |
| ] |
| } |
+-------------------------------------------+
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" |
+--------------------------+
Il existe deux façons d’accéder aux éléments d’un JSON objet :
Notation par points (dans cette rubrique).
Notation par parenthèses (dans cette rubrique).
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;
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" |
+----------------------+
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" |
+----------------------------+
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" |
| | } |
+----------------------+-------------------------+
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" |
+----------------------+----------------------+
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 |
+--------+
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 |
+--------------------------+-------------------------+
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" | | |
| } | | |
+-------------------------+---------+--------------+
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;
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 |
+---------+---------+-------+
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;
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');
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 |
+--------------------+-------------------+
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"]}
]
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 |
+--------+-------+-------------------+
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 | |
| } | |
| ] | |
+---------------+-------------------------+
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" |
+----------------------------------+
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;
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"
}
]
}
]
}
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 |
+----------------------------------------------+