7 min de lecture

SQL avance et PostgreSQL - 10 - JSON et JSONB : le NoSQL dans ton PostgreSQL

Stocker et requeter du JSON dans PostgreSQL. JSONB, opérateurs, index GIN et quand utiliser JSON vs colonnes relationnelles.

SQL PostgreSQL JSON JSONB NoSQL backend
SQL avance et PostgreSQL 11 / 16
  1. 01 SQL avance et PostgreSQL - 00 - Pourquoi comprendre SQL meme avec un ORM
  2. 02 SQL avance et PostgreSQL - 01 - SELECT, WHERE, ORDER BY : les fondamentaux solides
  3. 03 SQL avance et PostgreSQL - 02 - Les JOINs : INNER, LEFT, RIGHT, FULL et CROSS
  4. 04 SQL avance et PostgreSQL - 03 - GROUP BY, HAVING et les fonctions d'aggregation
  5. 05 SQL avance et PostgreSQL - 04 - Sous-requêtes, CTEs et WITH RECURSIVE
  6. 06 SQL avance et PostgreSQL - 05 - Les index : comprendre, créer et optimiser
  7. 07 SQL avance et PostgreSQL - 06 - Transactions et ACID : garantir l'intégrité
  8. 08 SQL avance et PostgreSQL - 07 - Fonctions et stored procedures en PL/pgSQL
  9. 09 SQL avance et PostgreSQL - 08 - Les triggers : reagir automatiquement aux changements
  10. 10 SQL avance et PostgreSQL - 09 - Vues, vues materialisees et generated columns
  11. 11 SQL avance et PostgreSQL - 10 - JSON et JSONB : le NoSQL dans ton PostgreSQL
  12. 12 SQL avance et PostgreSQL - 11 - Le problème N+1 : le détecter et le corriger
  13. 13 SQL avance et PostgreSQL - 12 - Migrations : versionner ton schema de base de donnees
  14. 14 SQL avance et PostgreSQL - 13 - Performance : EXPLAIN ANALYZE, connection pooling et tuning
  15. 15 SQL avance et PostgreSQL - 14 - Sécurité : rôles, permissions et Row Level Security
  16. 16 SQL avance et PostgreSQL - 15 - Glossaire
← Precedent Suivant →

10 - JSON et JSONB : le NoSQL dans ton PostgreSQL

Ce que tu vas apprendre

  • La différence entre JSON et JSONB (et pourquoi toujours utiliser JSONB)
  • Les opérateurs de requête : ->, ->>, @>, ?, #>
  • Les fonctions : jsonb_each, jsonb_array_elements, jsonb_set
  • Index GIN sur JSONB
  • Quand utiliser JSONB et quand garder des colonnes classiques

Prerequisites

Avoir lu l'article 05 sur les index (pour GIN).

JSON vs JSONB

PostgreSQL propose deux types JSON :

  • JSON : stocke le texte tel quel. Garde les espaces, l'ordre des clés, les doublons. Pas indexable. Valide le format a l'insertion.
  • JSONB : stocke en format binaire. Pas d'ordre garanti, pas de doublons. Indexable avec GIN. Requetes beaucoup plus rapides.

Utilise toujours JSONB. Le type JSON n'a d'interet que si tu dois preserver exactement le format du JSON original (logs bruts, par exemple). Dans tous les autres cas, JSONB est supérieur.

Creer une table avec JSONB

sqlCREATE TABLE events (
  id SERIAL PRIMARY KEY,
  type TEXT NOT NULL,
  payload JSONB NOT NULL DEFAULT '{}',
  metadata JSONB,
  created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO events (type, payload, metadata) VALUES
  ('user_created', '{"user_id": 1, "email": "alice@mail.com", "plan": "pro"}',
   '{"ip": "192.168.1.1", "browser": "Firefox"}'),
  ('order_placed', '{"order_id": 42, "items": [{"name": "Clavier", "qty": 2, "price": 89.99}, {"name": "Souris", "qty": 1, "price": 59.99}], "total": 239.97}',
   '{"ip": "10.0.0.5", "browser": "Chrome"}'),
  ('user_updated', '{"user_id": 1, "changes": {"plan": "enterprise", "name": "Alice Pro"}}',
   NULL),
  ('order_shipped', '{"order_id": 42, "tracking": "FR123456789", "carrier": "Colissimo"}',
   '{"ip": "10.0.0.5"}'),
  ('user_created', '{"user_id": 2, "email": "bob@mail.com", "plan": "free"}',
   '{"ip": "172.16.0.1", "browser": "Safari"}');

Les opérateurs de requête

-> et ->> : acceder a une clé

sql-- -> retourne du JSONB (garde le type JSON)
SELECT payload -> 'user_id' FROM events WHERE type = 'user_created';
-- Resultat : 1  (type JSONB)

-- ->> retourne du TEXT
SELECT payload ->> 'email' FROM events WHERE type = 'user_created';
-- Resultat : alice@mail.com  (type TEXT)

La différence est importante. -> retourne du JSONB, donc tu peux chainer les opérateurs. ->> retourne du texte, c'est ce que tu utilises pour les comparaisons et l'affichage.

Acces en profondeur

sql-- Acceder a un objet imbrique avec -> chaine
SELECT payload -> 'changes' -> 'plan' FROM events WHERE type = 'user_updated';
-- Resultat : "enterprise"  (JSONB, avec les guillemets)

SELECT payload -> 'changes' ->> 'plan' FROM events WHERE type = 'user_updated';
-- Resultat : enterprise  (TEXT, sans guillemets)

-- Ou avec #> et un chemin (path)
SELECT payload #> '{changes, plan}' FROM events WHERE type = 'user_updated';
-- Resultat : "enterprise"

SELECT payload #>> '{changes, plan}' FROM events WHERE type = 'user_updated';
-- Resultat : enterprise

Acceder aux éléments d'un array

sql-- Premier element du tableau items (index 0)
SELECT payload -> 'items' -> 0 ->> 'name' FROM events WHERE type = 'order_placed';
-- Resultat : Clavier

-- Deuxieme element
SELECT payload -> 'items' -> 1 ->> 'name' FROM events WHERE type = 'order_placed';
-- Resultat : Souris

@> : contient (le plus utile pour les recherches)

sql-- Tous les events ou le payload contient {"plan": "pro"}
SELECT * FROM events WHERE payload @> '{"plan": "pro"}';

-- Events ou l'user_id est 1
SELECT * FROM events WHERE payload @> '{"user_id": 1}';

L'opérateur @> ("contient") est le plus utilise en production parce qu'il fonctionne avec l'index GIN. C'est la facon performante de chercher dans du JSONB.

? : la clé existe

sql-- Events qui ont une cle "tracking" dans le payload
SELECT * FROM events WHERE payload ? 'tracking';

-- ?| : au moins une des cles existe
SELECT * FROM events WHERE payload ?| array['tracking', 'carrier'];

-- ?& : toutes les cles existent
SELECT * FROM events WHERE payload ?& array['tracking', 'carrier'];

Les fonctions JSONB

jsonb_each : décomposer un objet

sqlSELECT key, value
FROM events, jsonb_each(payload)
WHERE type = 'user_created' AND payload ->> 'email' = 'alice@mail.com';
   key    |        value
----------+---------------------
 plan     | "pro"
 email    | "alice@mail.com"
 user_id  | 1

jsonb_array_elements : décomposer un array

sqlSELECT item ->> 'name' AS product, (item ->> 'qty')::INT AS quantity
FROM events,
  jsonb_array_elements(payload -> 'items') AS item
WHERE type = 'order_placed';
 product  | quantity
----------+----------
 Clavier  |        2
 Souris   |        1

C'est un LATERAL JOIN implicite. Chaque élément du tableau généré une ligne. Super pratique pour "aplatir" des donnees JSON imbriquees.

jsonb_set : modifier une valeur

sql-- Ajouter ou modifier une cle
UPDATE events
SET payload = jsonb_set(payload, '{plan}', '"premium"')
WHERE type = 'user_created' AND payload ->> 'email' = 'alice@mail.com';

-- Ajouter une cle imbriquee
UPDATE events
SET payload = jsonb_set(payload, '{shipping, method}', '"express"', true)
WHERE type = 'order_placed';
-- Le "true" a la fin cree les cles intermediaires si elles n'existent pas

jsonb_strip_nulls et concatenation

sql-- Supprimer les cles avec valeur null
SELECT jsonb_strip_nulls('{"a": 1, "b": null, "c": 3}'::JSONB);
-- {"a": 1, "c": 3}

-- Fusionner deux objets JSONB (le deuxieme ecrase le premier)
SELECT '{"a": 1, "b": 2}'::JSONB || '{"b": 3, "c": 4}'::JSONB;
-- {"a": 1, "b": 3, "c": 4}

-- Supprimer une cle
SELECT '{"a": 1, "b": 2, "c": 3}'::JSONB - 'b';
-- {"a": 1, "c": 3}

Index GIN sur JSONB

Sans index, chaque requête JSONB fait un Seq Scan. Avec un index GIN :

sql-- Index sur tout le payload
CREATE INDEX idx_events_payload ON events USING GIN (payload);

-- Maintenant ces requetes utilisent l'index :
SELECT * FROM events WHERE payload @> '{"user_id": 1}';
SELECT * FROM events WHERE payload ? 'tracking';

L'index GIN supporte : @>, ?, ?|, ?&. Il ne supporte PAS ->> pour les comparaisons de texte. Pour ca, il faut un index sur expression :

sql-- Si tu filtres souvent par email dans le payload
CREATE INDEX idx_events_email ON events ((payload ->> 'email'));

-- Maintenant ca utilise l'index :
SELECT * FROM events WHERE payload ->> 'email' = 'alice@mail.com';

Il y a aussi jsonb_path_ops pour les index GIN, qui est plus compact mais ne supporte que @> :

sqlCREATE INDEX idx_events_payload_path ON events USING GIN (payload jsonb_path_ops);

En général, jsonb_path_ops est suffisant parce que @> couvre la majorite des cas de recherche.

Quand utiliser JSONB

Bons cas :

  • Metadata flexible (settings utilisateur, préférences, config)
  • Payloads d'événements (event sourcing, audit log, webhooks)
  • Donnees externes dont le schema varie (réponses API tierces)
  • Prototypage rapide (le schema va changer souvent)

Mauvais cas :

  • Donnees sur lesquelles tu fais des JOINs (la clé etrangere doit etre une vraie colonne)
  • Donnees que tu agreges (SUM, AVG sur des valeurs JSONB c'est penible et lent)
  • Donnees avec un schema stable et bien défini (utilise des colonnes classiques)
  • Donnees que tu filtres dans chaque requête (extrais-les en colonnes)

Mon opinion la-dessus : JSONB est genial pour les "sacs de donnees" que tu stockes et restitues tel quel. Quand tu commences a écrire payload ->> 'email' dans 10 requêtes différentes, c'est le signal qu'il faut extraire cette donnee en colonne. Ne transforme pas PostgreSQL en MongoDB. PostgreSQL est bien meilleur en relationnel, et tu perds les contraintes (NOT NULL, UNIQUE, FOREIGN KEY) quand tu fourres tout dans du JSONB.

Le pattern que je recommande : des colonnes classiques pour les donnees structurees + une colonne JSONB metadata pour le reste. C'est ce qu'on fait sur paltemps.fr pour les events applicatifs.

Résumé

  • Toujours utiliser JSONB, jamais JSON
  • -> retourne du JSONB, ->> retourne du TEXT
  • @> (contient) est l'opérateur le plus utile et le plus performant avec GIN
  • Index GIN pour les recherches dans JSONB, index expression pour les filtres par clé spécifique
  • JSONB pour les donnees flexibles, colonnes classiques pour les donnees structurees et fréquemment requetees

Article précédent : 09 - Vues et vues materialisees Article suivant : 11 - Le problème N+1

Sources

Publie le · Mis a jour le

Articles liés

Réservez un audit gratuit de 30 minutes. Je vous montre concrètement ce qu'on peut automatiser.

Découvrir Pal'Temps