JSONB PostgreSQL : requêtes et choix des index

JSONB permet de stocker des données JSON dans PostgreSQL, puis de les interroger et de les indexer. Utilisez -> pour obtenir une valeur JSON, ->> pour obtenir du texte et @> pour rechercher des documents contenant une structure donnée. Choisissez l’index après avoir identifié l’expression et l’opérateur de votre requête.

Ce guide utilise quatre petits documents de commande, puis un jeu distinct de 20 000 lignes pour les contrôles d’index. Nous avons exécuté 23 contrôles avec PostgreSQL 18.6 dans une nouvelle base locale. Les exemples vérifient les résultats et les plans observés, sans annoncer de gain de vitesse en production.

Conserver l’identité de la ligne hors du document

La table associe une clé primaire ordinaire à un contenu JSONB. Ce contenu doit être un objet JSON non nul, mais cette contrainte ne valide pas chacun de ses champs. Un montant est volontairement une chaîne, une note vaut JSON null et une autre note est absente.

Exécutez les blocs SQL dans l’ordre dans une base jetable. Arrêtez-vous si jsonb_lab existe déjà. Notre guide de l’hébergement PostgreSQL, en anglais explique les choix d’exploitation ; cet exercice utilise un serveur local et des données fictives.

CREATE SCHEMA jsonb_lab;
CREATE TABLE jsonb_lab.orders (
    id bigint PRIMARY KEY,
    payload jsonb NOT NULL CHECK (jsonb_typeof(payload) = 'object')
);
INSERT INTO jsonb_lab.orders (id, payload) VALUES
(1, '{"status":"paid","amount":12,"customer":{"country":"FR"},"tags":["priority","export"],"note":null}'),
(2, '{"status":"pending","amount":2,"customer":{"country":"DE"},"tags":["standard"]}'),
(3, '{"status":"paid","amount":"12","customer":{"country":"FR"},"tags":[],"note":""}'),
(4, '{"status":"paid","amount":100,"customer":{"country":"US"},"tags":["priority"],"note":"ok"}');

La documentation des types JSON décrit les différences de stockage entre json et jsonb. JSONB ne conserve pas les espaces d’origine, l’ordre des clés d’un objet ni les clés répétées. Dans un contrôle distinct, convertir un objet contenant deux clés x a conservé la dernière valeur, 2. Si vous devez garder la représentation entrante exacte, conservez-la séparément.

Un identifiant, une séparation entre tenants ou un champ métier soumis à des contraintes fréquentes peut être plus facile à encadrer dans une colonne typée ordinaire. Les champs flexibles d’un document ont tout de même besoin d’un sens défini et d’une règle de validation.

Choisir entre extraction JSON et extraction textuelle

Comparez le montant sous forme JSON, sous forme de texte et selon son type JSON. L’opérateur de chemin #>> extrait le pays imbriqué sous forme de texte :

SELECT id,
       payload -> 'amount' AS json_value,
       payload ->> 'amount' AS text_value,
       jsonb_typeof(payload -> 'amount') AS json_type,
       payload #>> '{customer,country}' AS country
FROM jsonb_lab.orders
ORDER BY id;

Sortie observée dans le format non aligné de psql :

1|12|12|number|FR
2|2|2|number|DE
3|"12"|12|string|FR
4|100|100|number|US

Les lignes 1 et 3 donnent toutes deux le texte 12, mais leurs valeurs JSON diffèrent : la ligne 1 contient un nombre et la ligne 3 une chaîne. Notre contrôle de type a confirmé que -> renvoie le type SQL jsonb, et ->> le type SQL text. Après extraction du texte, la valeur affichée ne permet plus à elle seule de retrouver lequel de ces types JSON était présent.

La référence des opérateurs JSON répertorie leurs types de résultat. Conservez une valeur JSON pour poursuivre la navigation dans un document ; extrayez du texte pour effectuer une comparaison textuelle ou une conversion explicite.

Distinguer une clé absente, JSON null et une chaîne vide

Le seul test payload ->> 'note' IS NULL ne distingue pas notre note absente de notre valeur JSON null explicite. Examinez à la fois l’existence de la clé et la valeur JSON :

SELECT id,
       payload ? 'note' AS key_exists,
       (payload -> 'note') IS NULL AS extracted_is_sql_null,
       (payload ->> 'note') IS NULL AS text_is_sql_null,
       COALESCE(jsonb_typeof(payload -> 'note'), 'missing') AS kind
FROM jsonb_lab.orders
ORDER BY id;

Sortie observée ; t signifie vrai et f faux :

1|t|f|t|null
2|f|t|t|missing
3|t|f|f|string
4|t|f|f|string

La ligne 1 possède une clé note contenant JSON null. L’extraction JSON renvoie cette valeur JSON : son test SQL NULL est donc faux, tandis que l’extraction textuelle renvoie SQL NULL. La ligne 2 n’a pas de clé note ; les deux extractions renvoient SQL NULL. Les lignes 3 et 4 contiennent des chaînes, dont une chaîne vide pour la ligne 3.

Le mot missing est notre libellé d’affichage lorsque le résultat du test de type est SQL NULL. Ce n’est pas un type JSON de PostgreSQL. Le test ? s’applique également au niveau interrogé : pour le premier document, payload ? 'country' était faux, alors que (payload -> 'customer') ? 'country' était vrai.

Utiliser la contenance pour les filtres structurés

Recherchez les commandes payées dont les tags comprennent priority :

SELECT id
FROM jsonb_lab.orders
WHERE payload @> '{"status":"paid","tags":["priority"]}'::jsonb
ORDER BY id;

Identifiants observés :

1
4

La ligne 1 possède aussi le tag export ; ce tag supplémentaire n’empêche pas la correspondance. Un contrôle distinct sur les tableaux a confirmé que la contenance n’exigeait ni le même ordre ni le même nombre de répétitions. Il s’agit ici d’un filtre de présence, pas d’un test d’égalité de deux séquences et de leurs nombres d’occurrences.

Les types comptent toujours. Le filtre {"amount":12} n’a sélectionné que la ligne 1. Le filtre {"amount":"12"} n’a sélectionné que la ligne 3. Convertir les deux montants en texte avant de les comparer exprimerait une autre règle.

Convertir les valeurs numériques avant de les trier

Trier le texte extrait des montants a classé les lignes dans l’ordre 4, 1, 3, 2, soit les textes 100, 12, 12, 2. Ce n’est pas un ordre numérique. Une conversion explicite change la comparaison :

SELECT id, (payload ->> 'amount')::numeric AS amount
FROM jsonb_lab.orders
ORDER BY (payload ->> 'amount')::numeric, id;

Sortie observée :

2|2
1|12
3|12
4|100

La conversion a accepté le nombre JSON 12 et la chaîne JSON "12" de cet exemple. Cela peut être trop permissif pour votre contrat de données. Nous avons aussi testé la chaîne "unknown" : sa conversion en numeric a déclenché SQLSTATE 22P02. Validez le type et la plage du champ avant de dépendre d’une conversion dans les requêtes ou la définition d’un index. Pour un montant obligatoire utilisé dans toute l’application, envisagez une colonne numeric.

Faire correspondre un index d’expression à une requête scalaire

Quatre lignes suffisent pour comprendre les opérateurs, mais offrent peu d’éléments pour choisir un index. Créez une table distincte contenant 20 000 documents, dont 20 ont le statut flagged et un tag priority :

CREATE TABLE jsonb_lab.events (
    id bigint PRIMARY KEY,
    payload jsonb NOT NULL
);
INSERT INTO jsonb_lab.events
SELECT n,
       jsonb_build_object(
           'status', CASE WHEN n % 1000 = 0 THEN 'flagged' ELSE 'normal' END,
           'account', n,
           'tags', jsonb_build_array(CASE WHEN n % 1000 = 0 THEN 'priority' ELSE 'standard' END)
       )
FROM generate_series(1, 20000) AS g(n);
ANALYZE jsonb_lab.events;

Avant l’ajout des index de document, nos requêtes de statut et de tag parcouraient séquentiellement la table. Pour une égalité sur le statut extrait en texte, ajoutez un index B-tree sur l’expression :

CREATE INDEX events_status_idx
ON jsonb_lab.events ((payload ->> 'status'));
ANALYZE jsonb_lab.events;

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT id FROM jsonb_lab.events
WHERE payload ->> 'status' = 'flagged';

Dans notre exécution, le plan utilisait un Index Scan sur events_status_idx et renvoyait 20 lignes. L’expression indexée correspond à payload ->> 'status' dans la requête. L’index porte sur ce résultat textuel, et non sur le document JSONB complet.

La documentation des index d’expression explique cette approche et ses coûts de maintenance. Testez l’expression réellement envoyée par votre application. Un index d’égalité textuelle ne garantit pas qu’une requête de contenance écrite différemment l’utilisera.

Utiliser GIN pour la requête de contenance

Ajoutez maintenant un index GIN sur payload et recherchez le tag priority par contenance :

CREATE INDEX events_payload_gin
ON jsonb_lab.events USING GIN (payload);
ANALYZE jsonb_lab.events;

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT id FROM jsonb_lab.events
WHERE payload @> '{"tags":["priority"]}'::jsonb;

Notre plan utilisait un Bitmap Index Scan sur events_payload_gin, suivi d’un Bitmap Heap Scan. Il renvoyait les 20 identifiants attendus, de 1000 à 20000 par pas de 1000. Les deux contrôles principaux utilisaient les paramètres du planificateur par défaut ; nous n’avons pas forcé le choix des index.

Un index GIN du document entier ne sert pas directement toutes les expressions qui utilisent ce document. Dans un diagnostic distinct d’éligibilité, (payload -> 'tags') ? 'priority' n’utilisait pas notre index GIN de payload. Nous avons temporairement désactivé les parcours séquentiels uniquement pour ce diagnostic, dans une transaction annulée. Il s’agissait d’un contrôle d’éligibilité, sans mesure de vitesse ni réglage à recopier en production.

Dans ce jeu de données, l’index d’expression occupait 155 648 octets et l’index GIN 1 294 336 octets. Ils servent des requêtes différentes et couvrent des données différentes : ces tailles ne désignent donc pas un vainqueur universel. Ne réutilisez ni ces tailles ni les plans observés dans une estimation de production sans mesurer vos propres documents et requêtes.

Partir de la requête, puis vérifier le plan

• Égalité sur un champ extrait : envisagez un index B-tree sur l’expression correspondante.
• Contenance sur plusieurs champs du document : envisagez un index GIN compatible avec vos opérateurs.
• Autre expression sur une valeur imbriquée : vérifiez son éligibilité propre, sans supposer que l’index du document entier s’applique.
• Tri numérique fréquent ou règles métier strictes : envisagez une colonne typée et des contraintes relationnelles ordinaires.

Lisez le plan réel et le nombre de résultats avant de conclure. Notre guide PostgreSQL EXPLAIN, en anglais explique les plans et rappelle que EXPLAIN ANALYZE exécute l’instruction. Un parcours séquentiel peut être raisonnable si la requête lit une grande partie de la table ; créer un index n’oblige pas le planificateur à le choisir.

Ces contrôles établissent la sémantique des requêtes et l’utilisation des index sur un seul jeu synthétique. Ils ne mesurent ni la latence en production, ni le coût des écritures, ni la concurrence, ni JSONPath, ni la classe d’opérateurs alternative jsonb_path_ops. Avant d’ajouter des index à une table en service, prévoyez également les conséquences opérationnelles de leur construction.

Supprimez uniquement les deux tables jetables et leur schéma à la fin de l’exercice :

DROP TABLE jsonb_lab.events;
DROP TABLE jsonb_lab.orders;
DROP SCHEMA jsonb_lab;

Le programme a confirmé le nettoyage, arrêté son serveur PostgreSQL local et supprimé son fichier de mot de passe temporaire.