DuckDB : colonne JSON en colonnes typées, en SQL

Ce qui se passe

read_json transforme un fichier JSON en table typée après avoir lu une partie des données pour en déduire le schéma. Mais beaucoup de JSON n'arrive pas en fichier : c'est une colonne de texte (charges utiles d'événements dans Postgres, réponses d'API conservées par un job, colonne metadata d'un export CSV). Le contournement courant consiste à exporter la colonne vers un fichier et à le relire. L'équipe DuckDB montre que ce détour est inutile : tout se fait en SQL.

Les quatre étapes

1. Inférer la forme. json_group_structure fusionne la structure de toutes les valeurs de la colonne ; il lui faut du JSON, donc un cast d'abord. Sur une grosse table, on l'applique à un échantillon (USING SAMPLE 10_000 ROWS), au risque de manquer une clé très rare.

SQL

SELECT json_group_structure(payload::JSON) FROM events;

2. Corriger les types. L'inférence rend ts en VARCHAR (JSON n'a pas de type date) et les entiers en UBIGINT. Comme la structure est elle-même un document JSON, json_merge_patch n'a besoin que de ce qui change ; le reste garde le type déduit. Le résultat va dans une variable.

SQL

SET VARIABLE payload_structure = (
  SELECT json_merge_patch(
    json_group_structure(payload::JSON),
    '{"user": {"id": "INTEGER"}, "ts": "TIMESTAMP", "duration_ms": "INTEGER"}')
  FROM events);

3. Construire la table. json_transform (alias from_json) convertit en STRUCT selon cette structure, unnest éclate le premier niveau en colonnes.

SQL

CREATE TABLE events_typed AS
  SELECT id, unnest(json_transform(payload, getvariable('payload_structure')))
  FROM events;

4. Interroger. Le JSON n'est analysé qu'une fois, au chargement. Ensuite ce sont des colonnes ordinaires, stockées et compressées par type : "user".country, ts, etc., sans retoucher au JSON. Un champ manquant donne NULL.

Les pièges

json_transform ne lève pas d'erreur : une valeur non convertible devient NULL. Utilisez json_transform_strict pendant la mise au point, puis le mode tolérant une fois les anomalies connues.
Collision de noms : unnest(…, recursive := true) nomme chaque colonne d'après sa clé la plus interne, donc id du niveau supérieur et id de user se confondent. Ajoutez keep_parent_names := true pour obtenir user.id.
Tableaux d'objets : FROM events_typed, unnest(items) AS u(item) donne une ligne par élément, mais perd les lignes sans élément ; LEFT JOIN unnest(items) AS u(item) ON true les conserve.
VARCHAR ou JSON pour la colonne brute ? Même stockage, donc même taille et même vitesse de lecture ; JSON ajoute seulement la validation à l'insertion.

Source : From JSON Strings to Typed Columns, blog DuckDB, 16 octobre 2026.