UnNest - Data Consulting
UnNest - Data Consulting
🤌

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

Twitter LinkedIn Nos services

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.

image

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`
image

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`
Code permettant d'obtenir le résultat de la première capture d'écran

Tous les articles concernant Google BigQuery