data / sql

Types de données avancés : JSON, tableaux, full-text search

Leçon 201 exercice

Explication

Ce que vous allez apprendre

Stocker et interroger du semi-structuré avec JSONB en PostgreSQL
Extraire une valeur JSON avec les opérateurs ->, ->> et #>>
Accélérer les requêtes JSONB avec un index GIN
Manipuler des tableaux natifs (TEXT[]) pour des listes simples comme des tags
Distinguer une recherche LIKE naïve d'une vraie recherche plein texte (tsvector/tsquery)

Dans quel contexte ?

Une plateforme de tracking d'événements reçoit des payloads dont la structure varie selon le type d'événement (clic, achat, erreur), chacun avec des champs différents dans une table evenements. Forcer une colonne par champ possible créerait une table avec des dizaines de colonnes vides. Stocker le tout dans une colonne JSONB interrogeable, comme le montre cette leçon, résout ce problème sans sacrifier la capacité de filtrer ou d'indexer.

D'abord, imagine le problème : une fiche qui n'a pas la même forme à chaque fois

Depuis le début du cours, chaque ligne d'une table a exactement les mêmes colonnes. Mais certaines données ne rentrent pas dans ce moule : les métadonnées d'un événement de tracking changent de forme selon le type d'événement, une liste de tags n'a pas de taille fixe. Forcer ça dans des colonnes rigides devient vite lourd.

Une première solution imparfaite : du texte brut

On pourrait stocker ces données comme du texte brut contenant du JSON. Mais alors la base ne comprend rien à ce texte : impossible de filtrer sur une valeur interne, impossible de l'indexer, il faudrait tout relire et parser côté application à chaque fois.

La vraie solution : JSONB, un format binaire interrogeable

PostgreSQL propose JSONB, qui stocke le document dans un format binaire optimisé. Contrairement au texte brut, la base sait "regarder à l'intérieur" : elle peut extraire une valeur avec -> (retourne du JSON) ou ->> (retourne du texte), et filtrer dessus directement avec WHERE.

Un problème réapparaît : que faire des grands volumes ?

Sur une table de millions de lignes, filtrer sur un champ JSON obligerait à réanalyser chaque document un par un — lent. La solution est la même que pour n'importe quelle colonne : un index. Un index GIN sur une colonne JSONB accélère les recherches, y compris avec l'opérateur @>, qui teste si un document en contient un autre.

Type	Interrogeable / indexable	Cas d'usage
Texte brut contenant du JSON	Non	À éviter
`JSONB` + index `GIN`	Oui	Payload d'événement à structure variable
`TEXT[]` + index `GIN`	Oui	Liste simple (tags) sans structure imbriquée
`tsvector`/`tsquery` + index `GIN`	Oui (recherche par sens)	Recherche plein texte dans un titre/contenu

Piège fréquent

Filtrer sur payload->>'utilisateur_id' sans index GIN adapté force PostgreSQL à décoder chaque document JSON ligne par ligne (équivalent d'un Seq Scan, vu en leçon 16), ce qui devient très lent sur une table de plusieurs millions d'événements.

Un cas plus simple : les tableaux natifs

Pas besoin de JSON pour une simple liste, comme des tags associés à un article. PostgreSQL propose directement le type TEXT[], avec des opérateurs dédiés (ANY, @>, &&) et son propre index GIN — plus simple qu'une table de liaison pour ce cas précis.

Une dernière limite à connaître : LIKE ne comprend pas le sens

Une fois les données bien stockées, chercher dedans avec LIKE '%mot%' reste une comparaison de caractères aveugle : il ne trouvera pas "chercher" si on tape "recherche". C'est le problème que la recherche plein texte (tsvector/tsquery) est faite pour résoudre.

Bonne pratique

Dès qu'une recherche texte doit comprendre les variantes d'un mot (singulier/pluriel, conjugaisons) plutôt qu'une simple sous-chaîne, préfère to_tsvector/to_tsquery avec un index GIN à un LIKE '%mot%', qui ne peut de toute façon pas utiliser d'index efficacement sur un motif commençant par %.

Vers la suite

Cette leçon pose trois bases que le parcours expert va creuser une par une : la recherche plein texte avancée (pondération, tolérance aux fautes), l'extension géospatiale PostGIS, et la manipulation fine du JSONB.

Commandes & code

Types avancés : JSON, tableaux, recherche plein texte

-- JSONB (PostgreSQL) : JSON stocké en binaire, indexable, interrogeable
CREATE TABLE evenements (
    id      SERIAL PRIMARY KEY,
    type    VARCHAR(50),
    payload JSONB
);

INSERT INTO evenements (type, payload) VALUES
    ('clic', '{"page": "/accueil", "utilisateur_id": 42, "tags": ["mobile", "fr"]}');

-- Extraire une valeur : ->> retourne du texte, -> retourne du JSON
SELECT payload->>'page' AS page, payload->'utilisateur_id' AS uid
FROM evenements;

-- Chemin imbriqué avec #>>
SELECT payload #>> '{meta,navigateur}' AS navigateur FROM evenements;

-- Filtrer sur une valeur JSON
SELECT * FROM evenements WHERE payload->>'page' = '/accueil';
SELECT * FROM evenements WHERE (payload->>'utilisateur_id')::INTEGER = 42;

-- Opérateur de containment : payload contient-il ce fragment ?
SELECT * FROM evenements WHERE payload @> '{"type": "clic"}';

-- Index GIN pour accélérer les requêtes JSONB
CREATE INDEX idx_evenements_payload ON evenements USING GIN (payload);

-- Tableaux natifs (PostgreSQL)
CREATE TABLE articles (
    id   SERIAL PRIMARY KEY,
    titre VARCHAR(200),
    tags TEXT[]
);

INSERT INTO articles (titre, tags) VALUES ('Guide SQL', ARRAY['sql', 'base-de-donnees', 'tutoriel']);

-- Rechercher dans un tableau
SELECT * FROM articles WHERE 'sql' = ANY(tags);
SELECT * FROM articles WHERE tags @> ARRAY['sql', 'tutoriel'];  -- contient les deux
SELECT * FROM articles WHERE tags && ARRAY['python', 'sql'];    -- au moins une intersection

-- Index GIN sur tableau
CREATE INDEX idx_articles_tags ON articles USING GIN (tags);

-- Recherche plein texte (full-text search)
ALTER TABLE articles ADD COLUMN recherche tsvector
    GENERATED ALWAYS AS (to_tsvector('french', titre)) STORED;

CREATE INDEX idx_articles_recherche ON articles USING GIN (recherche);

SELECT titre FROM articles
WHERE recherche @@ to_tsquery('french', 'sql & tutoriel');

-- Classement par pertinence
SELECT titre, ts_rank(recherche, to_tsquery('french', 'sql')) AS pertinence
FROM articles
WHERE recherche @@ to_tsquery('french', 'sql')
ORDER BY pertinence DESC;

-- UUID comme clé primaire (utile en environnement distribué / multi-shard)
CREATE TABLE sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    utilisateur_id INTEGER,
    cree_le TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Résumé

JSONB permet de stocker du semi-structuré tout en restant interrogeable et indexable (GIN).
Les opérateurs ->, ->>, #>>, @> couvrent l'essentiel des accès JSON en PostgreSQL.
Les tableaux natifs (TEXT[]) évitent une table de liaison pour de simples listes de tags.
La recherche plein texte (tsvector/tsquery) surpasse largement LIKE '%mot%' en pertinence et performance.
Un UUID en clé primaire facilite la génération d'identifiants sans coordination centrale (utile en environnement distribué).

Exercices pratiques

1 disponible

Mission : choisir le bon outil de stockage pour trois problèmes de recherche différents

Objectif : Diagnostiquer une requête JSON lente et choisir entre JSONB, tableau natif et recherche plein texte selon le besoin réel.

Contexte

La plateforme de tracking d'événements a mis en production la table evenements avec sa colonne payload JSONB, mais sans index dessus. La requête WHERE payload->>'utilisateur_id' = '42' met maintenant plusieurs secondes à répondre sur 5 millions de lignes. Au même moment, l'équipe éditoriale se plaint que la recherche d'articles avec LIKE '%mot%' ne trouve jamais les bons résultats, et veut aussi pouvoir filtrer les articles qui ont à la fois les tags "sql" et "tutoriel".

Résoudre l’exercice →