Skip to main content
Voici d’autres approches de modélisation du JSON dans ClickHouse. Elles sont documentées par souci d’exhaustivité, car elles étaient pertinentes avant l’apparition du type JSON ; elles ne sont donc généralement ni recommandées ni adaptées à la plupart des cas d’utilisation.
Adopter une approche au niveau de l’objetIl est possible d’appliquer différentes techniques à différents objets au sein d’un même schéma. Par exemple, certains objets se prêtent mieux au type String, tandis que d’autres sont mieux adaptés au type Map. Notez qu’une fois le type String choisi, aucune autre décision de schéma n’est nécessaire. À l’inverse, il est possible d’imbriquer des sous-objets dans une clé Map — y compris un String représentant du JSON — comme nous le montrons ci-dessous :

​
Utilisation du type String

Si les objets sont très dynamiques, sans structure prévisible, et contiennent des objets imbriqués arbitraires, vous devez utiliser le type String. Les valeurs peuvent être extraites à l’exécution de la requête à l’aide de fonctions JSON, comme nous le montrons ci-dessous. Traiter les données à l’aide de l’approche structurée décrite ci-dessus n’est souvent pas envisageable pour les utilisateurs qui manipulent du JSON dynamique, soit susceptible d’évoluer, soit dont le schéma est mal défini. Pour une flexibilité totale, vous pouvez simplement stocker le JSON sous forme de String, puis utiliser des fonctions pour extraire les champs selon les besoins. Cela représente l’exact opposé du traitement du JSON comme objet structuré. Cette flexibilité a toutefois un coût, avec des inconvénients importants — principalement une syntaxe de requête plus complexe ainsi que des performances moindres. Comme indiqué précédemment, pour l’objet person d’origine, nous ne pouvons pas garantir la structure de la colonne tags. Nous insérons la ligne d’origine (y compris company.labels, que nous ignorons pour l’instant), en déclarant la colonne Tags en String :
Nous pouvons sélectionner la colonne tags et constater que le JSON a été inséré comme une chaîne de caractères :
Les fonctions JSONExtract peuvent être utilisées pour extraire des valeurs de ce JSON. Prenons l’exemple simple ci-dessous :
Notez que les fonctions nécessitent à la fois une référence à la colonne String tags et un chemin JSON à extraire. Les chemins imbriqués exigent d’imbriquer les fonctions, par exemple JSONExtractUInt(JSONExtractString(tags, 'car'), 'year'), ce qui extrait la colonne tags.car.year. L’extraction des chemins imbriqués peut être simplifiée grâce aux fonctions JSON_QUERY et JSON_VALUE. Considérez le cas extrême du jeu de données arxiv, où l’on considère l’intégralité du contenu comme une String.
Pour insérer des données dans ce schéma, nous devons utiliser le format JSONAsString :
Supposons que nous voulions compter le nombre d’articles publiés chaque année. Comparez la requête suivante, qui n’utilise qu’une simple chaîne, à la version structurée du schéma :
Notez ici l’utilisation d’une expression XPath pour filtrer le JSON par méthode, c’est-à-dire JSON_VALUE(body, '$.versions[0].created'). Les fonctions String sont nettement plus lentes (> 10x) que les conversions de type explicites avec indices. Les requêtes ci-dessus nécessitent toujours un parcours complet de la table et le traitement de chaque ligne. Bien que ces requêtes restent rapides sur un petit jeu de données comme celui-ci, les performances se dégraderont sur des jeux de données plus volumineux. La flexibilité de cette approche a un coût évident en termes de performances et de syntaxe, et elle ne doit être utilisée que pour des objets très dynamiques dans le schéma.

​
Fonctions JSON simples

Les exemples ci-dessus utilisent la famille de fonctions JSON*. Celles-ci s’appuient sur un parser JSON complet basé sur simdjson, rigoureux dans son parsing et capable de distinguer un même champ imbriqué à différents niveaux. Ces fonctions peuvent traiter du JSON syntaxiquement correct mais mal formaté, par exemple avec des doubles espaces entre les clés. Un ensemble de fonctions plus rapide et plus strict est également disponible. Ces fonctions simpleJSON* peuvent offrir de meilleures performances, principalement parce qu’elles reposent sur des hypothèses strictes concernant la structure et le format du JSON. Plus précisément :
  • Les noms de champ doivent être des constantes
  • Encodage cohérent des noms de champ, par ex. simpleJSONHas('{"abc":"def"}', 'abc') = 1, mais visitParamHas('{"\\u0061\\u0062\\u0063":"def"}', 'abc') = 0
  • Les noms de champ doivent être uniques dans toutes les structures imbriquées. Aucune distinction n’est faite entre les niveaux d’imbrication et la correspondance se fait sans distinction. En cas de correspondance avec plusieurs champs, la première occurrence est utilisée.
  • Aucun caractère spécial en dehors des littéraux de chaîne. Cela inclut les espaces. L’exemple suivant est invalide et ne sera pas parsé.
En revanche, l’exemple suivant sera parsé correctement :
La requête ci-dessus utilise simpleJSONExtractString pour extraire la clé created, en tirant parti du fait que nous ne voulons que la première valeur pour la date de publication. Dans ce cas, les limites des fonctions simpleJSON* sont acceptables au vu du gain de performances.

​
Utilisation du type Map

Si l’objet sert à stocker des clés arbitraires, majoritairement d’un même type, envisagez d’utiliser le type Map. Idéalement, le nombre de clés uniques ne devrait pas dépasser quelques centaines. Le type Map peut également convenir aux objets contenant des sous-objets, à condition que leurs types restent homogènes. De manière générale, nous recommandons d’utiliser le type Map pour les labels et les tags, par exemple les labels des pods Kubernetes dans les données de logs. Bien que les Map offrent un moyen simple de représenter des structures imbriquées, elles présentent certaines limites importantes :
  • Tous les champs doivent être du même type.
  • L’accès aux sous-colonnes nécessite une syntaxe propre aux maps, puisque les champs n’existent pas en tant que colonnes. L’objet tout entier est une colonne.
  • L’accès à une sous-colonne charge l’intégralité de la valeur Map, c.-à-d. tous les éléments au même niveau ainsi que leurs valeurs respectives. Pour les maps volumineuses, cela peut entraîner une dégradation significative des performances.
Clés StringLorsque des objets sont modélisés sous forme de Map, une clé String est utilisée pour stocker le nom de la clé JSON. La map sera donc toujours de type Map(String, T), où T dépend des données.

​
Valeurs primitives

L’utilisation la plus simple d’un Map est lorsque l’objet contient des valeurs du même type primitif. Dans la plupart des cas, cela implique d’utiliser le type String pour la valeur T. Prenons notre JSON person précédent, où l’objet company.labels a été identifié comme dynamique. Il est important de noter que nous nous attendons uniquement à ce que des paires clé-valeur de type String soient ajoutées à cet objet. Nous pouvons donc le déclarer sous la forme Map(String, String) :
Nous pouvons insérer notre objet JSON complet initial :
Pour interroger ces champs dans l’objet de requête, il faut utiliser la syntaxe Map, par exemple :
Un ensemble complet de fonctions Map est disponible pour interroger ce type, comme décrit ici. Si vos données ne sont pas d’un type homogène, des fonctions permettent d’effectuer la conversion de type nécessaire.

​
Valeurs d’objet

Le type Map peut également être envisagé pour des objets qui comportent des sous-objets, à condition que ces derniers aient des types cohérents. Supposons que la clé tags de notre objet persons nécessite une structure cohérente, où le sous-objet de chaque tag possède les colonnes name et time. Un exemple simplifié d’un tel document JSON pourrait ressembler à ceci :
Cela peut être modélisé à l’aide d’un Map(String, Tuple(name String, time DateTime)), comme illustré ci-dessous :
L’utilisation de maps dans ce cas est assez rare et suggère qu’il faudrait restructurer les données de sorte que les noms de clés dynamiques n’aient pas de sous-objets. Par exemple, l’exemple ci-dessus pourrait être restructuré comme suit, ce qui permettrait d’utiliser Array(Tuple(key String, name String, time DateTime)).

​
Utilisation du type Nested

Le type Nested peut être utilisé pour modéliser des objets statiques qui changent rarement, et constitue une alternative à Tuple et Array(Tuple). Nous recommandons généralement d’éviter d’utiliser ce type pour du JSON, car son comportement prête souvent à confusion. Le principal avantage de Nested est que les sous-colonnes peuvent être utilisées dans les clés de tri. Ci-dessous, nous présentons un exemple d’utilisation du type Nested pour modéliser un objet statique. Prenons l’exemple de l’entrée de log JSON simple suivante :
Nous pouvons déclarer la clé request en tant que Nested. Comme avec Tuple, il faut spécifier les sous-colonnes.

​
flatten_nested

Le paramètre flatten_nested contrôle le comportement du type Nested.

​
flatten_nested=1

Une valeur de 1 (par défaut) ne permet pas un niveau d’imbrication arbitraire. Avec cette valeur, le plus simple est de considérer une structure de données imbriquée comme plusieurs colonnes Array de même longueur. Les champs method, path et version correspondent en pratique à des colonnes Array(Type) distinctes, avec une contrainte essentielle : la longueur des champs method, path et version doit être identique. Si l’on utilise SHOW CREATE TABLE, cela se présente ainsi :
Ci-dessous, nous insérons des données dans cette table :
Voici quelques points importants :
  • Nous devons utiliser le paramètre input_format_import_nested_json pour insérer le JSON comme structure imbriquée. Sans cela, il faut aplatir le JSON, c.-à-d.
  • Les champs imbriqués method, path et version doivent être transmis sous forme de tableaux JSON, c.-à-d.
Les colonnes peuvent être interrogées à l’aide de la notation pointée :
À noter que l’utilisation de Array pour les sous-colonnes signifie que toute la palette des fonctions sur les tableaux peut potentiellement être exploitée, y compris la clause ARRAY JOIN — ce qui est utile si vos colonnes contiennent plusieurs valeurs.

​
flatten_nested=0

Cela autorise un niveau d’imbrication arbitraire et signifie que les colonnes imbriquées restent sous la forme d’un seul tableau de Tuple — elles deviennent donc, en pratique, équivalentes à Array(Tuple). C’est l’approche à privilégier, et souvent la plus simple, pour utiliser du JSON avec Nested. Comme nous le montrons ci-dessous, cela exige simplement que tous les objets soient présentés sous forme de liste. Ci-dessous, nous recréons notre table et réinsérons une ligne :
Quelques points importants à noter ici :
  • input_format_import_nested_json n’est pas nécessaire pour insérer des données.
  • Le type Nested est conservé dans SHOW CREATE TABLE. En interne, cette colonne est en pratique un Array(Tuple(Nested(method LowCardinality(String), path String, version LowCardinality(String))))
  • Par conséquent, nous devons insérer request sous la forme d’un tableau, c’est-à-dire :
Les colonnes peuvent de nouveau être interrogées à l’aide d’une notation pointée :

​
Exemple

Un exemple plus détaillé des données ci-dessus est disponible dans un bucket public sur S3 à l’adresse suivante : s3://datasets-documentation/http/.
Compte tenu des contraintes et du format d’entrée JSON, nous insérons ce jeu de données d’exemple à l’aide de la requête suivante. Ici, nous définissons flatten_nested=0. L’instruction suivante insère 10 millions de lignes ; son exécution peut donc prendre quelques minutes. Ajoutez un LIMIT si nécessaire :
Pour interroger ces données, nous devons accéder aux champs de la requête comme à des tableaux. Ci-dessous, nous récapitulons les erreurs et les méthodes HTTP sur une période fixe.

​
Utilisation de tableaux par paires

Les tableaux par paires offrent un bon compromis entre la flexibilité d’une représentation du JSON sous forme de Strings et les performances d’une approche plus structurée. Le schéma reste flexible, puisque de nouveaux champs peuvent potentiellement être ajoutés à la racine. En revanche, cela nécessite une syntaxe de requête nettement plus complexe et n’est pas compatible avec les structures imbriquées. À titre d’exemple, considérez la table suivante :
Pour insérer dans cette table, nous devons structurer le JSON sous la forme d’une liste de clés et de valeurs. La requête suivante illustre l’utilisation de JSONExtractKeysAndValues pour y parvenir :
Notez que la colonne request reste une structure imbriquée représentée sous forme de chaîne. Nous pouvons insérer de nouvelles clés au niveau racine. Le JSON lui-même peut également présenter des variations arbitraires. Pour insérer dans notre table locale, exécutez ce qui suit :
Pour interroger cette structure, il faut utiliser la fonction indexOf pour identifier l’indice de la clé requise (qui doit correspondre à l’ordre des valeurs). Cela permet d’accéder à la colonne de tableau values, c.-à-d. values[indexOf(keys, 'status')]. Il faut toujours une méthode de parsing JSON pour la colonne request — dans ce cas, simpleJSONExtractString.
Dernière modification le 3 juillet 2026