data / sql

JSONB avancé : SQL/JSON path, index ciblés, mises à jour partielles

Leçon 251 exercice

Explication

Ce que vous allez apprendre

Filtrer à l'intérieur d'une structure JSON avec jsonb_path_query (SQL/JSON path)
Modifier un champ précis d'un document JSONB avec jsonb_set/jsonb_insert, sans le reconstruire
Choisir entre jsonb_ops et jsonb_path_ops pour l'indexation GIN d'une colonne JSONB
Créer un index B-Tree ciblé sur une clé JSON précise, souvent plus efficace qu'un GIN générique
Valider la structure minimale d'un document JSON avec une contrainte CHECK

Dans quel contexte ?

Un service de tracking doit mettre à jour uniquement le champ utilisateur.derniere_connexion d'un document JSON stocké dans une colonne payload, sans jamais toucher au reste du document ni le renvoyer intégralement depuis l'application. Cette leçon montre comment jsonb_set réalise cette mise à jour ciblée directement en SQL, en allant plus loin que les simples extractions ->/->> déjà vues.

D'abord, rappel de ce qu'on sait déjà faire

La leçon sur les types avancés a montré comment extraire une valeur JSON avec -> et ->>. C'est suffisant pour des accès simples, mais deux besoins plus riches restent sans réponse : filtrer à l'intérieur d'un tableau JSON selon une condition, et modifier UN SEUL champ profondément imbriqué sans réécrire tout le document.

Étape 1 : un vrai langage de requête, SQL/JSON path

jsonb_path_query introduit une syntaxe standardisée, proche de XPath pour le XML, capable de filtrer directement à l'intérieur d'une structure JSON — y compris dans des tableaux imbriqués, avec des conditions comme "où le montant dépasse 100". C'est bien plus expressif que d'enchaîner des -> un par un.

Il reste un problème : modifier sans tout casser

Sans outil dédié, mettre à jour un seul champ d'un document JSON obligerait à récupérer le document entier côté application, le modifier, puis le renvoyer en entier — un aller-retour coûteux, et risqué si deux processus modifient le même document en même temps.

Étape 2 : modifier un champ précis, en place

jsonb_set et jsonb_insert résolvent exactement ce problème : ils modifient un champ ou un élément précis directement en SQL, sans jamais reconstruire tout le document. jsonb_set avec create_missing = true crée même le chemin s'il n'existe pas encore.

Étape 3 : choisir son index, un vrai compromis

Deux stratégies d'index existent pour le JSONB. jsonb_ops (par défaut) supporte un éventail large d'opérateurs mais reste volumineux. jsonb_path_ops se limite à l'opérateur de containment @>, mais produit un index nettement plus compact et souvent plus rapide. C'est un arbitrage classique entre polyvalence et performance.

Stratégie d'index	Opérateurs supportés	Taille	Quand l'utiliser
`jsonb_ops` (défaut)	`@>`, `?`, `?&`, `?\|`	Plus volumineux	Besoin de plusieurs opérateurs différents
`jsonb_path_ops`	`@>` uniquement	~30% plus compact	Seul `@>` est utilisé (cas fréquent)
B-Tree ciblé `(payload->>'clé')`	`=`, `<`, `>` sur une clé précise	Très compact	Filtre toujours sur le même champ

Piège courant

Oublier qu'un index B-Tree ciblé sur une seule clé JSON ((payload->>'type')) peut être bien plus efficace qu'un GIN générique quand on filtre toujours sur le même champ — pas besoin de sortir l'artillerie lourde pour un cas simple et répété.

Bonne pratique

Si tes requêtes filtrent presque toujours sur la même clé JSON avec un = simple, préfère un index B-Tree ciblé sur cette clé (CREATE INDEX ON evenements ((payload->>'type'))) à un index GIN générique : il sera plus petit et souvent plus rapide pour ce cas précis.

Vers la suite

Après avoir approfondi le stockage et l'interrogation des données, la dernière leçon du parcours revient à un sujet transversal essentiel : savoir lire un plan d'exécution en détail, pour diagnostiquer n'importe laquelle des requêtes vues jusqu'ici.

Commandes & code

JSONB avancé

-- SQL/JSON path (jsonb_path_query) : langage d'interrogation JSON standardisé
SELECT jsonb_path_query(payload, '$.commandes[*] ? (@.montant > 100)')
FROM evenements;

-- Extraire toutes les valeurs correspondant à un chemin, sous forme de tableau
SELECT jsonb_path_query_array(payload, '$.tags[*]') FROM evenements;

-- Test d'existence conditionnelle avec filtre
SELECT * FROM evenements
WHERE jsonb_path_exists(payload, '$.utilisateur ? (@.actif == true)');

-- Deux stratégies d'index GIN pour JSONB
-- jsonb_ops (défaut)  : supporte @>, ?, ?&, ?|      -- index plus gros, plus polyvalent
-- jsonb_path_ops      : supporte seulement @>        -- index ~30% plus petit, souvent plus rapide
CREATE INDEX idx_payload_ops     ON evenements USING GIN (payload);
CREATE INDEX idx_payload_pathops ON evenements USING GIN (payload jsonb_path_ops);

-- Index B-Tree ciblé sur une clé JSON précise, souvent plus efficace qu'un GIN générique
CREATE INDEX idx_payload_type ON evenements ((payload->>'type'));
EXPLAIN ANALYZE SELECT * FROM evenements WHERE payload->>'type' = 'clic';  -- Index Scan

-- Mise à jour PARTIELLE d'un document JSONB, sans réécrire tout le champ
UPDATE evenements
SET payload = jsonb_set(payload, '{utilisateur,derniere_connexion}', to_jsonb(now()::text))
WHERE id = 42;

-- jsonb_set avec create_missing = true : crée le chemin s'il n'existe pas encore
UPDATE evenements
SET payload = jsonb_set(payload, '{meta,version}', '2'::jsonb, true)
WHERE payload->'meta' IS NOT NULL;

-- Insérer un élément dans un tableau JSON sans écraser les autres
UPDATE evenements
SET payload = jsonb_insert(payload, '{tags,0}', '"urgent"', false)  -- insère en tête
WHERE id = 42;

-- Supprimer une clé, y compris un chemin imbriqué
UPDATE evenements SET payload = payload - 'champ_obsolete' WHERE id = 42;
UPDATE evenements SET payload = payload #- '{meta,debug}' WHERE id = 42;

-- Agrégation SQL -> JSON : construire un document depuis des lignes relationnelles
SELECT jsonb_build_object(
    'client', c.nom,
    'commandes', jsonb_agg(jsonb_build_object('id', co.id, 'montant', co.montant))
) AS export_client
FROM clients c
JOIN commandes co ON co.client_id = c.id
GROUP BY c.id, c.nom;

-- Valider la structure d'un document via une contrainte CHECK
ALTER TABLE evenements ADD CONSTRAINT chk_payload_type
    CHECK (payload ? 'type' AND jsonb_typeof(payload->'type') = 'string');

-- Convertir un document JSONB en lignes exploitables comme une vraie table
SELECT id, cle, valeur FROM evenements, jsonb_each_text(payload) AS t(cle, valeur);

Résumé

jsonb_path_query/jsonb_path_exists apportent un langage de requête JSON standardisé (SQL/JSON path), plus expressif que les opérateurs ->/->>.
jsonb_path_ops produit un index plus petit et souvent plus rapide que jsonb_ops quand seul @> est utilisé.
jsonb_set/jsonb_insert modifient un document en place sans le reconstruire entièrement côté application.
Une contrainte CHECK peut valider la forme minimale d'un document JSON directement en base.

Exercices pratiques

1 disponible

Mission : mettre à jour un champ de tracking sans tout réécrire

Objectif : Construire une mise à jour JSONB ciblée avec jsonb_set et choisir la bonne stratégie d'index pour les requêtes du service de tracking.

Contexte

Le service de tracking stocke des événements dans une colonne payload (JSONB) de la table evenements, avec un champ imbriqué utilisateur.derniere_connexion. Un développeur a codé la mise à jour de ce champ en récupérant tout le document côté application, en modifiant le champ, puis en le renvoyant intégralement en SQL. Sous forte charge, deux mises à jour concurrentes sur le même événement se sont déjà écrasées l'une l'autre, perdant des données. Il faut aussi choisir un index adapté sachant que les requêtes filtrent presque toujours avec payload->>'type' = 'clic'.

Résoudre l’exercice →