JSON en PostgreSQL — à coller dans psql

Ce que fait cet outil

Un échantillon JSON devient un CREATE TABLE.

{ "id": 1, "firstName": "Ada", "score": 9.5, "address": { "city": "Paris" } }

CREATE TABLE "root" (
  "id" BIGINT NOT NULL,
  "first_name" TEXT NOT NULL,
  "score" DOUBLE PRECISION NOT NULL,
  "address" JSONB NOT NULL
);

Les noms sont en snake_case et cités, ce qui les garde exacts et permet à un mot réservé comme "order" d’être une colonne sans cérémonie.

TEXT n’a pas de longueur à inventer

PostgreSQL stocke TEXT et VARCHAR(n) de la même façon ; la seule différence est la contrainte. Comme un échantillon ne peut pas vous dire la vraie limite — seulement la plus longue valeur qu’il contient — aucune limite n’est écrite. Ajouter VARCHAR(40) plus tard est une décision sur votre domaine, et cela ne coûte rien en stockage.

C’est aussi pourquoi cette page n’a pas l’équivalent de la note MySQL sur VARCHAR(255) : il n’y a pas de longueur sur laquelle se tromper.

Toutes les lignes, pas seulement la première

[{ "a": 1 }, { "b": "x" }]

CREATE TABLE "root_item" (
  "a" BIGINT NULL,
  "b" TEXT NULL
);

La table était bâtie sur le seul premier objet : la deuxième ligne de votre propre échantillon n’avait donc nulle part où aller. Les colonnes sont maintenant l’union de tous les objets, et une clé qui manque à certaines lignes est nullable — parce qu’elle l’est.

Les types se fondent de la même façon. Quand une même clé est un nombre dans un objet et une chaîne dans un autre, aucune colonne scalaire ne prend les deux : la colonne est JSONB. Un entier à côté d’un décimal, c’est autre chose — DOUBLE PRECISION les couvre tous les deux, et c’est ce qu’il obtient.

JSONB pour ce dont SQL n’a pas de colonne

Les objets et tableaux imbriqués deviennent du JSONB. Pas de table liée, pas de clé étrangère : un échantillon montre une forme, jamais une relation, et inventer une jointure reviendrait à deviner d’un coup une table, une clé et un sens.

JSONB est la forme interrogeable — ->, ->>, @>, et un index GIN au besoin. Le type json simple conserve le texte d’origine, ce qui ne compte que si vous prévoyez de rendre le document inchangé.

Faire entrer les lignes

PostgreSQL n’a pas de bq load. COPY est bâti autour du texte ligne par ligne et du CSV, pas d’un tableau d’objets JSON : la table ci-dessus ne fait que la moitié du travail.

La voie évidente est jsonb_populate_recordset, qui apparie les clés JSON aux noms de colonnes — à l’identique, caractère pour caractère. C’est là le piège : la table ci-dessus a renommé firstName en first_name, si bien que cette voie laisserait la colonne à NULL sans un mot.

Le mappage qui va avec la table ci-dessus est explicite, et dit quelle clé nourrit quelle colonne :

INSERT INTO "root" ("id", "first_name", "score", "address")
SELECT (e->>'id')::bigint,
       e->>'firstName',
       (e->>'score')::double precision,
       e->'address'
FROM jsonb_array_elements(:'doc'::jsonb) AS e;

:'doc' est une variable psql qui porte le document — psql -v doc="$(cat data.json)" -f load.sql tant que le fichier tient dans un argument. Au-delà, copiez le texte dans une table d’attente à une colonne et sélectionnez depuis elle ; la forme de la requête ne change pas.

Ce qu’un échantillon ne peut pas dire

• Pas de clé primaire. Un id qui semble unique dans un document ne promet rien du suivant.
• Ni index, ni valeur par défaut, ni CHECK. Ce sont des affirmations sur la donnée, pas des lectures.
• NOT NULL là où une valeur était présente, NULL pour un null et pour une clé absente de certains objets.

Confidentiel par conception

Tout s’exécute localement dans votre navigateur en JavaScript. Vos données ne sont jamais envoyées sur un serveur, ce qui rend l’outil sûr pour des contenus sensibles, et il fonctionne hors ligne.