Twitter LinkedIn Nos services
Dossiers
Technologies
Formations
En savoir plus
SQL : Comment traiter des chaînes de caractères (string) JSON dans BigQuery
Par Xavier le @December 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.
Code 👇
Ci-dessous l'essentiel des fonctions utiles avec les résultats en colonnes qu'elles retournent
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`
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 :
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`
Tous les articles concernant Google BigQuery
- Présentation de l'interface web de Google Big Query
- Chrome User Experience Report : explorer les données dans Big Query
- Google Search Console to BigQuery avec Python
- Importer les données Matomo vers Google BigQuery, avec Google Cloud Function
- Tutoriel : déployer une application Python avec Google App Engine
- SQL : Comment lisser des coûts entre 2 dates (sur plusieurs mois) dans BigQuery
- SQL : Créer un groupe de contrôle dans BigQuery
- SQL : Comment supprimer les doublons d'une liste (array) sur BigQuery
- SQL : Comment traiter des chaînes de caractères (string) JSON dans BigQuery
- SQL : Comment traiter les lignes en doubles dans ma base de donnée BigQuery
- SQL : Comment calculer la taille de mes tables et dataset dans BigQuery