SQL : Comment traiter des chaînes de caractères (string) JSON dans BigQuery

Par Xavier le Dec 14, 2021
La donnée peut arriver sous bien des manières dans un Data Warehouse. Un format plutôt commun est la chaine de caractère JSON qui permet d'ordonner des informations selon une certaine structure et notamment de gérer les "sous-objets" (les custom dimensions dans Google Analytics peuvent par exemple être renvoyées sous cette forme pour chaque hit). Cependant, pour extraire l'information recherchée ce n'est pas forcément évident. Au lieu de s'embêter avec des REGEX, le plus facile dans BigQuery est d'utiliser les fonctions JSON, qui permettrons de facilement mettre en colonnes ce que nous souhaitons.
Image without caption

Code 👇

Ci-dessous l'essentiel des fonctions utiles avec les résultats en colonnes qu'elles retournent
sql
select id, json_value(info,'$.name') as name, json_value(info,'$.age') as age, json_value(info,'$.secretIdentity') as secretIdentity, json_value(info,'$.powers') as powers, json_query(info,'$.powers') as powers_query, json_extract_scalar(info, '$.powers[0]') as first_power, json_extract(info, '$.powers[1]') as second_power, json_extract_array(info, '$.powers') as powers_array, json_extract_string_array(info, '$.powers') as powers_array_string from `VOTRE_TABLE`
Image without caption

Explications

json_value(champ_json,'$.clé') permet d'extraire rapidement les informations de premier niveaux. Cependant, si cette fonction est appliquée sur une clé qui contient des sous-objets, la valeur retournée sera null comme il est possible de le voir dans la colonne "powers".
Pour y remédier json_query(champ_json,'$.clé') permet de retourner la liste de tout les sous-objets (colonne "powers_query").
Pour obtenir la valeur d'une position précise dans cette liste json_extract_scalar(champ_json, '$.clé[0]') json_extract(champ_json, '$.clé[1]') permettent d'extraire ces valeurs. json_extract renvoie toujours le résultat sous forme de chaine de caractères (entre " ") tandis que json_extract_scalar va renvoyer la valeur telle quelle.
Enfin, json_extract_array(champ_json,'$.clé') json_extract_string_array(champ_json,'$.clé') permettent d'extraire l'information sous forme de liste (scalaire ou en chaine de caractère)

Annexe

Table utilisée en entrée :
TutoJSON
Sheet1 id,info 2314, { "name": "Molecule Man", "age": 29, "secretIdentity": "Dan Jukes", "powers": "Turning tiny" } 1231, { "name": "Madame Uppercut", "age": 39, "secretIdentity": "Jane Wilson", "powers": "Million tonne punch", ...
TutoJSON
sql
select id, json_value(info,'$.name') as name, json_value(info,'$.age') as age, json_value(info,'$.secretIdentity') as secretIdentity, json_extract_scalar(info, '$.powers[0]') as first_power, json_extract(info, '$.powers[1]') as second_power, from `VOTRE_TABLE`
Code permettant d'obtenir le résultat de la première capture d'écran