- Catégories :
Fonctions de table , Fonctions de données semi-structurées et structurées (extraction)
FLATTEN¶
Aplatit (explose) les valeurs composées en plusieurs lignes.
FLATTEN est une fonction de table qui prend une colonne VARIANT, OBJECT ou ARRAY et produit une vue latérale (c’est-à-dire une vue en ligne qui contient une corrélation référant aux autres tables qui la précèdent dans la clause FROM).
FLATTEN peut être utilisé pour convertir des données semi-structurées en une représentation relationnelle.
Syntaxe¶
FLATTEN( INPUT => <expr> [ , PATH => <constant_expr> ]
[ , OUTER => TRUE | FALSE ]
[ , RECURSIVE => TRUE | FALSE ]
[ , MODE => 'OBJECT' | 'ARRAY' | 'BOTH' ] )
Arguments¶
Obligatoire :
INPUT => exprL’expression qui sera désassemblée en lignes. L’expression doit avoir un type de données VARIANT, OBJECT ou ARRAY.
Facultatif :
PATH => constant_exprLe chemin vers l’élément au sein d’une structure de données VARIANT devant être aplati. Peut être une chaîne de caractères de longueur nulle (c’est-à-dire un chemin vide) si l’élément le plus à l’extérieur doit être aplati.
Par défaut : chaîne de longueur nulle (c.-à-d. chemin vide)
OUTER => TRUE | FALSESi
FALSE, toutes les lignes d’entrée qui ne peuvent pas être développées, soit parce qu’elles ne sont pas accessibles dans le chemin, soit parce qu’elles ont zéro champ ou entrée, sont complètement omises de la sortie.Si
TRUE, une seule ligne est générée pour les expansions de zéro ligne (avec NULL dans les colonnes KEY, INDEX et VALUE).
Par défaut :
FALSENote
Une expansion à zéro ligne d’un composé vide affiche NULL dans la colonne de sortie THIS, le distinguant d’une tentative d’expansion d’un composé inexistant ou d’un mauvais type de composé.
RECURSIVE => TRUE | FALSESi
FALSE, seul l’élément référencé parPATHest développé.Si
TRUE, l’expansion est effectuée pour tous les sous-éléments de manière récursive.
Par défaut :
FALSEMODE => 'OBJECT' | 'ARRAY' | 'BOTH'Indique si seuls les objets, les tableaux ou les deux doivent être aplatis.
Par défaut :
BOTH
Sortie¶
Les lignes renvoyées sont constituées d’un ensemble fixe de colonnes :
+-----+------+------+-------+-------+------+ | SEQ | KEY | PATH | INDEX | VALUE | THIS | |-----+------+------+-------+-------+------|
- SEQ:
Un numéro de séquence unique associé à l’enregistrement d’entrée ; il n’est pas garanti que la séquence ne présente pas d’écart ou qu’elle soit ordonnée d’une manière particulière.
- KEY:
Pour les cartes ou les objets, cette colonne contient la clé de la valeur éclatée.
- PATH:
Le chemin vers l’élément d’une structure de données qui doit être aplati.
- INDEX:
L’index de l’élément, s’il s’agit d’un tableau ; sinon NULL.
- VALUE:
La valeur de l’élément du tableau/objet aplati.
- THIS:
L’élément en cours d’aplatissement (utile pour l’aplatissement récursif).
Note
Les colonnes de la table originale (corrélée) qui a servi de source de données pour FLATTEN sont également accessibles. Si une seule ligne de la table d’origine a donné plusieurs lignes dans la vue aplatie, les valeurs de cette ligne d’entrée sont répliquées pour correspondre au nombre de lignes produites par FLATTEN.
Notes sur l’utilisation¶
Pour des lignes directrices sur l’utilisation de cette fonction avec les types structurés, voir Utilisation de la fonction FLATTEN avec des types structurés.
Exemples¶
Exemple simple d’aplatissement d’un enregistrement (notez que l’élément central du tableau est manquant) :
SELECT * FROM TABLE(FLATTEN(input => parse_json('[1, ,77]'))) f; +-----+------+------+-------+-------+------+ | SEQ | KEY | PATH | INDEX | VALUE | THIS | |-----+------+------+-------+-------+------| | 1 | NULL | [0] | 0 | 1 | [ | | | | | | | 1, | | | | | | | , | | | | | | | 77 | | | | | | | ] | | 1 | NULL | [2] | 2 | 77 | [ | | | | | | | 1, | | | | | | | , | | | | | | | 77 | | | | | | | ] | +-----+------+------+-------+-------+------+
Exemples d’effet du paramètre PATH :
SELECT * FROM TABLE(FLATTEN(input => parse_json('{"a":1, "b":[77,88]}'), outer => true)) f; +-----+-----+------+-------+-------+-----------+ | SEQ | KEY | PATH | INDEX | VALUE | THIS | |-----+-----+------+-------+-------+-----------| | 1 | a | a | NULL | 1 | { | | | | | | | "a": 1, | | | | | | | "b": [ | | | | | | | 77, | | | | | | | 88 | | | | | | | ] | | | | | | | } | | 1 | b | b | NULL | [ | { | | | | | | 77, | "a": 1, | | | | | | 88 | "b": [ | | | | | | ] | 77, | | | | | | | 88 | | | | | | | ] | | | | | | | } | +-----+-----+------+-------+-------+-----------+ SELECT * FROM TABLE(FLATTEN(input => parse_json('{"a":1, "b":[77,88]}'), path => 'b')) f; +-----+------+------+-------+-------+-------+ | SEQ | KEY | PATH | INDEX | VALUE | THIS | |-----+------+------+-------+-------+-------| | 1 | NULL | b[0] | 0 | 77 | [ | | | | | | | 77, | | | | | | | 88 | | | | | | | ] | | 1 | NULL | b[1] | 1 | 88 | [ | | | | | | | 77, | | | | | | | 88 | | | | | | | ] | +-----+------+------+-------+-------+-------+
Exemple de l’effet du paramètre OUTER :
SELECT * FROM TABLE(FLATTEN(input => parse_json('[]'))) f; +-----+-----+------+-------+-------+------+ | SEQ | KEY | PATH | INDEX | VALUE | THIS | |-----+-----+------+-------+-------+------| +-----+-----+------+-------+-------+------+ SELECT * FROM TABLE(FLATTEN(input => parse_json('[]'), outer => true)) f; +-----+------+------+-------+-------+------+ | SEQ | KEY | PATH | INDEX | VALUE | THIS | |-----+------+------+-------+-------+------| | 1 | NULL | | NULL | NULL | [] | +-----+------+------+-------+-------+------+
Exemple de l’effet du paramètre RECURSIVE :
SELECT * FROM TABLE(FLATTEN(input => parse_json('{"a":1, "b":[77,88], "c": {"d":"X"}}'))) f; +-----+-----+------+-------+------------+--------------+ | SEQ | KEY | PATH | INDEX | VALUE | THIS | |-----+-----+------+-------+------------+--------------| | 1 | a | a | NULL | 1 | { | | | | | | | "a": 1, | | | | | | | "b": [ | | | | | | | 77, | | | | | | | 88 | | | | | | | ], | | | | | | | "c": { | | | | | | | "d": "X" | | | | | | | } | | | | | | | } | | 1 | b | b | NULL | [ | { | | | | | | 77, | "a": 1, | | | | | | 88 | "b": [ | | | | | | ] | 77, | | | | | | | 88 | | | | | | | ], | | | | | | | "c": { | | | | | | | "d": "X" | | | | | | | } | | | | | | | } | | 1 | c | c | NULL | { | { | | | | | | "d": "X" | "a": 1, | | | | | | } | "b": [ | | | | | | | 77, | | | | | | | 88 | | | | | | | ], | | | | | | | "c": { | | | | | | | "d": "X" | | | | | | | } | | | | | | | } | +-----+-----+------+-------+------------+--------------+ SELECT * FROM TABLE(FLATTEN(input => parse_json('{"a":1, "b":[77,88], "c": {"d":"X"}}'), recursive => true )) f; +-----+------+------+-------+------------+--------------+ | SEQ | KEY | PATH | INDEX | VALUE | THIS | |-----+------+------+-------+------------+--------------| | 1 | a | a | NULL | 1 | { | | | | | | | "a": 1, | | | | | | | "b": [ | | | | | | | 77, | | | | | | | 88 | | | | | | | ], | | | | | | | "c": { | | | | | | | "d": "X" | | | | | | | } | | | | | | | } | | 1 | b | b | NULL | [ | { | | | | | | 77, | "a": 1, | | | | | | 88 | "b": [ | | | | | | ] | 77, | | | | | | | 88 | | | | | | | ], | | | | | | | "c": { | | | | | | | "d": "X" | | | | | | | } | | | | | | | } | | 1 | NULL | b[0] | 0 | 77 | [ | | | | | | | 77, | | | | | | | 88 | | | | | | | ] | | 1 | NULL | b[1] | 1 | 88 | [ | | | | | | | 77, | | | | | | | 88 | | | | | | | ] | | 1 | c | c | NULL | { | { | | | | | | "d": "X" | "a": 1, | | | | | | } | "b": [ | | | | | | | 77, | | | | | | | 88 | | | | | | | ], | | | | | | | "c": { | | | | | | | "d": "X" | | | | | | | } | | | | | | | } | | 1 | d | c.d | NULL | "X" | { | | | | | | | "d": "X" | | | | | | | } | +-----+------+------+-------+------------+--------------+
Exemple de l’effet du paramètre MODE :
SELECT * FROM TABLE(FLATTEN(input => parse_json('{"a":1, "b":[77,88], "c": {"d":"X"}}'), recursive => true, mode => 'object' )) f; +-----+-----+------+-------+------------+--------------+ | SEQ | KEY | PATH | INDEX | VALUE | THIS | |-----+-----+------+-------+------------+--------------| | 1 | a | a | NULL | 1 | { | | | | | | | "a": 1, | | | | | | | "b": [ | | | | | | | 77, | | | | | | | 88 | | | | | | | ], | | | | | | | "c": { | | | | | | | "d": "X" | | | | | | | } | | | | | | | } | | 1 | b | b | NULL | [ | { | | | | | | 77, | "a": 1, | | | | | | 88 | "b": [ | | | | | | ] | 77, | | | | | | | 88 | | | | | | | ], | | | | | | | "c": { | | | | | | | "d": "X" | | | | | | | } | | | | | | | } | | 1 | c | c | NULL | { | { | | | | | | "d": "X" | "a": 1, | | | | | | } | "b": [ | | | | | | | 77, | | | | | | | 88 | | | | | | | ], | | | | | | | "c": { | | | | | | | "d": "X" | | | | | | | } | | | | | | | } | | 1 | d | c.d | NULL | "X" | { | | | | | | | "d": "X" | | | | | | | } | +-----+-----+------+-------+------------+--------------+
Exemple pratique qui explose un tableau imbriqué dans un autre tableau :
create or replace table persons as select column1 as id, parse_json(column2) as c from values (12712555, '{ name: { first: "John", last: "Smith"}, contact: [ { business:[ { type: "phone", content:"555-1234" }, { type: "email", content:"j.smith@company.com" } ] } ] }'), (98127771, '{ name: { first: "Jane", last: "Doe"}, contact: [ { business:[ { type: "phone", content:"555-1236" }, { type: "email", content:"j.doe@company.com" } ] } ] }') v; -- Note the multiple instances of LATERAL FLATTEN in the FROM clause of the following query. -- Each LATERAL view is based on the previous one to refer to elements in -- multiple levels of arrays. SELECT id as "ID", f.value AS "Contact", f1.value:type AS "Type", f1.value:content AS "Details" FROM persons p, lateral flatten(input => p.c, path => 'contact') f, lateral flatten(input => f.value:business) f1; +----------+-----------------------------------------+---------+-----------------------+ | ID | Contact | Type | Details | |----------+-----------------------------------------+---------+-----------------------| | 12712555 | { | "phone" | "555-1234" | | | "business": [ | | | | | { | | | | | "content": "555-1234", | | | | | "type": "phone" | | | | | }, | | | | | { | | | | | "content": "j.smith@company.com", | | | | | "type": "email" | | | | | } | | | | | ] | | | | | } | | | | 12712555 | { | "email" | "j.smith@company.com" | | | "business": [ | | | | | { | | | | | "content": "555-1234", | | | | | "type": "phone" | | | | | }, | | | | | { | | | | | "content": "j.smith@company.com", | | | | | "type": "email" | | | | | } | | | | | ] | | | | | } | | | | 98127771 | { | "phone" | "555-1236" | | | "business": [ | | | | | { | | | | | "content": "555-1236", | | | | | "type": "phone" | | | | | }, | | | | | { | | | | | "content": "j.doe@company.com", | | | | | "type": "email" | | | | | } | | | | | ] | | | | | } | | | | 98127771 | { | "email" | "j.doe@company.com" | | | "business": [ | | | | | { | | | | | "content": "555-1236", | | | | | "type": "phone" | | | | | }, | | | | | { | | | | | "content": "j.doe@company.com", | | | | | "type": "email" | | | | | } | | | | | ] | | | | | } | | | +----------+-----------------------------------------+---------+-----------------------+