Manipuler du JSON dans PostgreSQL : insérer, lire, modifier, projeter, joindre

Les deux premiers articles de cette série répondaient à « faut-il ? » et « combien ça coûte ? ». Celui-ci répond à « comment ? ».

C’est le troisième qui manque le plus souvent, parce que la documentation de PostgreSQL sur JSON est une liste d’opérateurs et de fonctions, exhaustive et parfaitement inutilisable quand on cherche juste à mettre à jour une clé imbriquée un mardi après-midi. On finit par copier un jsonb_set trouvé sur Stack Overflow, il marche, et on ne sait toujours pas pourquoi le || d’à côté a effacé la moitié du document.

Chaque exemple ci-dessous a été exécuté sur PostgreSQL 18.4, et les sorties affichées sont les vraies. Le jeu de données est en fin d’article : deux tables, customers en relationnel et orders avec une colonne payload jsonb, de quoi tout rejouer en copiant-collant.

Le document sur lequel on travaille a la forme d’une commande : quelques champs à la racine, un sous-objet shipping, un tableau lines.

{
  "customer_id": 1,
  "status": "paid",
  "coupon": "SUMMER26",
  "shipping": {"country": "FR", "city": "Lyon", "express": true},
  "lines": [{"sku": "A-100", "qty": 2, "price": 19.90},
            {"sku": "B-220", "qty": 1, "price": 45.00}]
}

1. Insérer un document

Deux façons de persister du json sur Postgres au format jsonb existent.

Le choix pour l’une ou l’autre dépend de la provenance des valeurs.

jsonb_build_object, quand les valeurs viennent du SQL

INSERT INTO orders (payload)
VALUES (jsonb_build_object(
    'customer_id', 2,
    'status',      'pending',
    'shipping',    jsonb_build_object('country', 'GB', 'city', 'Bristol', 'express', false),
    'lines',       jsonb_build_array(
                       jsonb_build_object('sku', 'A-100', 'qty', 3, 'price', 19.90))))
RETURNING id, payload;

 id |                                       payload
----+-------------------------------------------------------------------------------------
  4 | {"lines": [{"qty": 3, "sku": "A-100", "price": 19.90}], "status": "pending", "shippi
    | ng": {"city": "Bristol", "country": "GB", "express": false}, "customer_id": 2}

Les arguments alternent clé, valeur, clé, valeur. jsonb_build_array fait la même chose pour les tableaux, et les deux s’imbriquent. Un nombre impair d’arguments est refusé à la compilation de la requête :

SELECT jsonb_build_object('a', 1, 'b');
-- ERROR:  argument list must have even number of elements

Au passage, les clés ressortent dans un ordre qui n’est pas celui d’écriture : jsonb les range par longueur, puis par ordre binaire. Sans conséquence tant qu’on interroge le document par ses clés.

Le cast de texte, quand le document arrive déjà formé

INSERT INTO orders (payload)
VALUES ('{"customer_id": 3, "status": "pending",
          "shipping": {"country": "US", "city": "Denver", "express": true},
          "lines": [{"sku": "C-330", "qty": 1, "price": 7.50}]}'::jsonb)
RETURNING id, payload -> 'shipping' ->> 'city' AS city;

 id |  city
----+--------
  5 | Denver

C’est la bonne forme quand le document vous est donné : payload d’un webhook, réponse d’API. Vous ne le composez pas, vous le stockez.

Ce qui les départage

Le cast ne valide que la syntaxe : bien formé lui suffit, le contenu ne le regarde pas.

SELECT '{"qty": "trois"}'::jsonb;   -- accepté sans broncher

Surtout, dès que vous construisez la chaîne par concaténation, vous écrivez un générateur de JSON à la main, avec les bugs qui vont avec :

SELECT ('{"city": "' || 'O"Hara' || '"}')::jsonb;
-- ERROR:  invalid input syntax for type json
-- DETAIL:  Token "Hara" is invalid.

jsonb_build_object échappe pour vous :

SELECT jsonb_build_object('city', 'O''Hara "X"');
-- {"city": "O'Hara \"X\""}

Il préserve aussi le typage, là où une concaténation vous laisse décider à la main si la valeur porte des guillemets :

SELECT jsonb_build_object('qty', 3)   AS nombre,
       jsonb_build_object('qty', '3') AS chaine;
-- {"qty": 3}  |  {"qty": "3"}

2. Lire une valeur

Toute la lecture d’un document tient dans une flèche de plus ou de moins.

SELECT payload -> 'shipping'            AS fleche,
       pg_typeof(payload -> 'shipping') AS type_fleche,
       payload ->> 'status'             AS double_fleche,
       pg_typeof(payload ->> 'status')  AS type_double
FROM orders WHERE id = 1;

                       fleche                       | type_fleche | double_fleche | type_double
----------------------------------------------------+-------------+---------------+-------------
 {"city": "Lyon", "country": "FR", "express": true} | jsonb       | paid          | text

Sur une valeur scalaire, la différence saute aux yeux : -> reste dans le monde JSON et garde donc les guillemets, ->> en sort.

SELECT payload -> 'status' AS avec, payload ->> 'status' AS sans
FROM orders WHERE id = 1;
-- "paid"  |  paid

D’où la règle de chaînage, qui se résume à une phrase : -> partout, ->> au dernier cran.

SELECT payload -> 'shipping' ->> 'city' FROM orders WHERE id = 1;   -- Lyon

Dans l’autre ordre, il n’y a rien à quoi s’accrocher, puisque ->> a déjà rendu du text :

SELECT payload ->> 'shipping' -> 'city' FROM orders WHERE id = 1;
-- ERROR:  operator does not exist: text -> unknown

Le chemin en une fois

#> et #>> prennent un tableau de texte plutôt qu’une cascade, et acceptent les index de tableau :

SELECT payload #>  '{shipping,city}' AS chemin_jsonb,
       payload #>> '{shipping,city}' AS chemin_text,
       payload #>> '{lines,0,sku}'   AS premier_sku
FROM orders WHERE id = 1;

 chemin_jsonb | chemin_text | premier_sku
--------------+-------------+-------------
 "Lyon"       | Lyon        | A-100

Sur un chemin fixe, c’est affaire de goût.
Sur un chemin construit dynamiquement côté applicatif, #> est le seul praticable.

Deux pièges de lecture

->> rend du text, et le text se compare comme du texte :

SELECT '{"qty": 12}'::jsonb ->> 'qty' > '9'          AS texte,   -- false
       ('{"qty": 12}'::jsonb ->> 'qty')::numeric > 9 AS caste;   -- true

Aucune erreur, juste un mauvais résultat.
Le cast n’est pas optionnel dès qu’on compare ou qu’on agrège.
Une clé absente est indiscernable d’une clé qui vaut null :

SELECT payload ->> 'coupon' AS coupon, payload ? 'coupon' AS presente
FROM orders WHERE id IN (1, 2) ORDER BY id;

  coupon  | presente
----------+----------
 SUMMER26 | t
          | f

Les deux lignes rendent NULL en SQL.
Seul l’opérateur ? répond à la question « la clé existe-t-elle ? ».

3. Modifier un sous-objet

Deux outils, deux comportements. C’est la difficulté qui revient le plus en relecture, et la plus discrète : rien ne casse, des clés disparaissent.

jsonb_set remplace une feuille précise

UPDATE orders
SET payload = jsonb_set(payload, '{shipping,city}', '"Villeurbanne"')
WHERE id = 1
RETURNING payload -> 'shipping';

 {"city": "Villeurbanne", "country": "FR", "express": true}

Le chemin est un tableau de texte, comme pour #>.
Le troisième argument est du jsonb, pas une valeur SQL nue :

SELECT jsonb_set('{"a": 1}', '{a}', 2);
-- ERROR:  function jsonb_set(unknown, unknown, integer) does not exist

Quand la valeur vient d’une colonne ou d’un paramètre, c’est to_jsonb qui fait la conversion :

UPDATE orders
SET payload = jsonb_set(payload, '{shipping,city}', to_jsonb('Lyon 7e'::text))
WHERE id = 1;

Le quatrième argument, optionnel, décide du sort d’une clé qui n’existe pas encore.
Il vaut true par défaut, choix que je trouve à contre-emploi : une écriture silencieuse là où l’on attendrait un refus.

SELECT jsonb_set('{"a": 1}', '{b}', '2')        AS defaut,      -- {"a": 1, "b": 2}
       jsonb_set('{"a": 1}', '{b}', '2', false) AS sans_flag;   -- {"a": 1}

|| fusionne, mais seulement à la racine

Pour ajouter ou remplacer plusieurs clés de premier niveau, || est plus court :

UPDATE orders
SET payload = payload || '{"status": "shipped", "carrier": "DHL"}'::jsonb
WHERE id = 1;

Racine, et seulement racine. Sur un sous-objet, || ne fusionne pas, il remplace :

SELECT '{"shipping": {"city": "Lyon", "country": "FR"}}'::jsonb
    || '{"shipping": {"express": true}}'::jsonb;
-- {"shipping": {"express": true}}

city et country ont disparu. La fusion est superficielle, c’est documenté, et c’est rarement ce qu’on voulait.

Pour une fusion en profondeur, il faut recomposer explicitement le sous-objet et le reposer avec jsonb_set :

SELECT jsonb_set(payload, '{shipping}',
                 payload -> 'shipping' || '{"express": true}'::jsonb) -> 'shipping'
FROM orders WHERE id = 1;
-- {"city": "Lyon 7e", "country": "FR", "express": true}

Le || intérieur fusionne bien, parce qu’à ce niveau-là les clés sont à la racine du sous-document.

4. Supprimer une clé, ou la mettre à null

Retirer une clé et lui donner la valeur null ne reviennent pas au même, et les confondre produit des documents que les requêtes ne retrouvent plus.

Supprimer

L’opérateur - retire une clé à la racine :

UPDATE orders SET payload = payload - 'coupon' WHERE id = 1;

Il accepte aussi une liste, et sur un tableau il travaille par index :

SELECT '{"a":1,"b":2,"c":3}'::jsonb - ARRAY['a','c'] AS restant;      -- {"b": 2}
SELECT '["x","y","z"]'::jsonb - 1                    AS sans_index_1; -- ["x", "z"]

Pour descendre, c’est #-, avec la même syntaxe de chemin que #> :

SELECT '{"shipping": {"city": "Lyon", "express": true}}'::jsonb #- '{shipping,express}';
-- {"shipping": {"city": "Lyon"}}

Retirer une clé qui n’existe pas ne lève rien et ne change rien. Pratique en migration, ennuyeux le jour où l’on comptait sur une erreur pour repérer une faute de frappe.

Mettre à null

SELECT jsonb_set('{"a":1,"b":2}', '{a}', 'null'::jsonb) AS mis_a_null,
       '{"a":1,"b":2}'::jsonb - 'a'                     AS supprime;

     mis_a_null      | supprime
---------------------+----------
 {"a": null, "b": 2} | {"b": 2}

La différence est invisible à la lecture avec ->>, qui rend NULL dans les deux cas. Elle ne se voit qu’avec ? :

Ça compte pour deux raisons. Un @> sur {"a": null} ne se comporte pas comme sur une clé absente, et une contrainte CHECK qui traverse la clé ne verra pas la même chose. Choisissez donc explicitement lequel des deux vous voulez dire. Un null affirme que la valeur est vide ; une clé absente avoue qu’on n’en sait rien.

Pour nettoyer en masse, jsonb_strip_nulls retire toutes les clés à null, à n’importe quelle profondeur :

SELECT jsonb_strip_nulls('{"a": null, "b": 2, "c": {"d": null, "e": 3}}'::jsonb);
-- {"b": 2, "c": {"e": 3}}

Avec une réserve : il ne touche pas aux null à l’intérieur des tableaux, puisque les retirer décalerait les index.

SELECT jsonb_strip_nulls('{"a": [1, null, 3]}'::jsonb);
-- {"a": [1, null, 3]}

5. Projeter le document en table

Projeté en lignes, un jsonb redevient une table ordinaire : les GROUP BY et les fonctions de fenêtrage s’y appliquent comme ailleurs.

jsonb_array_elements : une ligne par élément

SELECT o.id, line ->> 'sku' AS sku, (line ->> 'qty')::int AS qty
FROM orders o, jsonb_array_elements(o.payload -> 'lines') AS line
ORDER BY o.id, sku;

 id |  sku  | qty
----+-------+-----
  1 | A-100 |   2
  1 | B-220 |   1
  2 | A-100 |   1
  3 | B-220 |   2
  3 | C-330 |   5
  4 | A-100 |   3
  5 | C-330 |   1
(7 rows)

La virgule après orders o est un CROSS JOIN LATERAL implicite : la fonction est évaluée une fois par ligne de orders, avec accès aux colonnes de cette ligne.

jsonb_to_recordset : typer en une passe

Plutôt que d’extraire puis caster chaque champ, on déclare la forme attendue :

SELECT o.id, l.sku, l.qty, l.price
FROM orders o,
     jsonb_to_recordset(o.payload -> 'lines') AS l(sku text, qty int, price numeric)
WHERE o.id = 3
ORDER BY l.sku;

 id |  sku  | qty | price
----+-------+-----+-------
  3 | B-220 |   2 | 45.00
  3 | C-330 |   5 |  7.50

Plus lisible, et les casts sont faits une fois. À partir de là, l’agrégation est du SQL ordinaire :

SELECT o.id, sum(l.qty * l.price) AS total
FROM orders o,
     jsonb_to_recordset(o.payload -> 'lines') AS l(qty int, price numeric)
GROUP BY o.id ORDER BY o.id;

 id | total
----+--------
  1 |  84.80
  2 |  19.90
  3 | 127.50
  4 |  59.70
  5 |   7.50

La commande vide de tout à l’heure manque à l’appel, pour la même raison que plus haut.

JSON_TABLE : la racine et le tableau ensemble

Depuis PostgreSQL 17, JSON_TABLE fait ce que les deux précédentes ne savent pas : projeter dans la même passe des champs de la racine et les éléments d’un tableau imbriqué, sans se répéter.

SELECT t.*
FROM orders o,
     JSON_TABLE(o.payload, '$' COLUMNS (
         status text PATH '$.status',
         city   text PATH '$.shipping.city',
         NESTED PATH '$.lines[*]' COLUMNS (
             sku   text    PATH '$.sku',
             qty   int     PATH '$.qty',
             price numeric PATH '$.price'
         )
     )) AS t
WHERE o.id = 3;

 status |  city  |  sku  | qty | price
--------+--------+-------+-----+-------
 paid   | Austin | C-330 |   5 |  7.50
 paid   | Austin | B-220 |   2 | 45.00

Les champs de la racine sont répétés sur chaque ligne du NESTED PATH, exactement comme le ferait une jointure. C’est la forme la plus lisible dès qu’il y a plus d’un niveau à aplatir.

Le chemin inverse

jsonb_agg recompose un document à partir de lignes, ce qui sert à renvoyer du JSON directement depuis la base plutôt que de l’assembler dans le code applicatif :

SELECT jsonb_agg(jsonb_build_object('id', id, 'status', payload ->> 'status')) AS doc
FROM orders WHERE id <= 2;
-- [{"id": 1, "status": "shipped"}, {"id": 2, "status": "pending"}]

6. Joindre avec une table relationnelle

Le cas courant : la clé étrangère vit dans le document, la table de référence est relationnelle.

SELECT o.id, c.name, c.country, o.payload ->> 'status' AS status
FROM orders o
JOIN customers c ON c.id = (o.payload ->> 'customer_id')::bigint
ORDER BY o.id;

 id |     name     | country | status
----+--------------+---------+---------
  1 | Ada Lovelace | FR      | shipped
  2 | Alan Turing  | GB      | pending
  3 | Grace Hopper | US      | paid
  4 | Alan Turing  | GB      | pending
  5 | Grace Hopper | US      | pending
  6 | Ada Lovelace | FR      | draft

Le cast est obligatoire, et aucun raccourci ne marche :

... ON c.id = o.payload ->> 'customer_id'
-- ERROR:  operator does not exist: bigint = text

... ON c.id = o.payload -> 'customer_id'
-- ERROR:  operator does not exist: bigint = jsonb

L’autre sens marche aussi, en convertissant la colonne plutôt que le document :

... ON to_jsonb(c.id) = o.payload -> 'customer_id'

Élégant, mais à réserver aux petites tables : ce n’est plus la même expression, donc un index posé sur (payload ->> 'customer_id')::bigint ne sert plus à rien. Le plan retombe sur un Seq Scan de orders et une jointure par hachage. La section suivante explique pourquoi.

Une fois la jointure posée, tout le SQL habituel redevient disponible :

SELECT c.name,
       count(*)                                                AS commandes,
       count(*) FILTER (WHERE o.payload ->> 'status' = 'paid')  AS payees
FROM customers c
JOIN orders o ON (o.payload ->> 'customer_id')::bigint = c.id
GROUP BY c.name ORDER BY c.name;

     name     | commandes | payees
--------------+-----------+--------
 Ada Lovelace |         2 |      0
 Alan Turing  |         2 |      0
 Grace Hopper |         2 |      1

Indexer l’expression de jointure

Sans index, cette jointure est un Seq Scan sur toute la table de commandes. L’index se pose sur l’expression exacte :

CREATE INDEX idx_orders_customer ON orders (((payload ->> 'customer_id')::bigint));

Le triple parenthésage n’est pas une coquille : la paire extérieure est la liste de colonnes de l’index, les deux autres appartiennent à l’expression elle-même.

Sur six lignes, le planificateur l’ignorera, et il aura raison : un Seq Scan coûte moins cher. Gonflons la table :

INSERT INTO orders (payload)
SELECT jsonb_build_object('customer_id', 1 + g % 3, 'status', 'paid', 'lines', '[]'::jsonb)
FROM generate_series(1, 50000) g;
ANALYZE orders;

Le plan bascule :

EXPLAIN (COSTS OFF)
SELECT o.id FROM orders o WHERE (o.payload ->> 'customer_id')::bigint = 2;

 Bitmap Heap Scan on orders o
   Recheck Cond: (((payload ->> 'customer_id'::text))::bigint = 2)
   ->  Bitmap Index Scan on idx_orders_customer
         Index Cond: (((payload ->> 'customer_id'::text))::bigint = 2)

Un index sur expression est apparié à l’expression du prédicat, texte contre texte. Écrivez le même filtre sans le cast, et il redevient un Seq Scan :

EXPLAIN (COSTS OFF)
SELECT o.id FROM orders o WHERE o.payload ->> 'customer_id' = '2';

 Seq Scan on orders o
   Filter: ((payload ->> 'customer_id'::text) = '2'::text)

Si l’application écrit la condition de deux façons dans deux requêtes, il faut deux index. Ou, plus raisonnablement, une seule écriture partout.

Reste ce qu’aucun index ne rattrape : payload ->> 'customer_id' ne référence rien. Supprimez le client 2 et ses commandes lui survivent, orphelines ; la jointure les ignore, sans un mot. Si l’intégrité compte, la clé doit sortir du document et devenir une colonne avec sa contrainte REFERENCES.

Aide-mémoire

Et les cinq réflexes que j’aimerais avoir eus plus tôt :

1. -> partout, ->> au dernier cran, puis un cast explicite dès qu’on compare ou agrège.
2. jsonb_set pour une feuille, || pour la racine. Jamais || sur un sous-objet qu’on veut conserver.
3. COALESCE(…, 'null'::jsonb) autour de toute valeur qui peut être NULL dans un jsonb_set.
4. Supprimer et mettre à null sont deux choses. Décidez laquelle vous voulez dire.
5. LEFT JOIN LATERAL … ON true dès qu’un tableau peut être vide.

jsonb est un type comme un autre, avec ses opérateurs et ses conversions.

Tout le reste revient à savoir, à chaque cran, si l’on tient encore du document ou déjà du texte.

Reproduire les exemples

Toutes les sorties de cet article viennent d’un PostgreSQL 18.4 en conteneur, paramètres par défaut. Les exemples se suivent : les sections 1, 3 et 4 modifient les données, donc rejouez-les dans l’ordre si vous voulez retrouver les mêmes résultats.

docker container run --rm -d --name pgjson -e POSTGRES_PASSWORD=postgres postgres:18.4
docker container exec -it pgjson psql -U postgres

CREATE TABLE customers (
    id      bigint PRIMARY KEY,
    name    text   NOT NULL,
    country text   NOT NULL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada Lovelace', 'FR'),
    (2, 'Alan Turing',  'GB'),
    (3, 'Grace Hopper', 'US');

CREATE TABLE orders (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT now(),
    payload    jsonb NOT NULL
);

INSERT INTO orders (payload) VALUES
('{"customer_id": 1,
  "status": "paid",
  "coupon": "SUMMER26",
  "shipping": {"country": "FR", "city": "Lyon", "express": true},
  "lines": [{"sku": "A-100", "qty": 2, "price": 19.90},
            {"sku": "B-220", "qty": 1, "price": 45.00}]}'),
('{"customer_id": 2,
  "status": "pending",
  "shipping": {"country": "GB", "city": "London", "express": false},
  "lines": [{"sku": "A-100", "qty": 1, "price": 19.90}]}'),
('{"customer_id": 3,
  "status": "paid",
  "coupon": "WELCOME",
  "shipping": {"country": "US", "city": "Austin", "express": false},
  "lines": [{"sku": "C-330", "qty": 5, "price": 7.50},
            {"sku": "B-220", "qty": 2, "price": 45.00}]}');

Ressources

• JSON Functions and Operators
• JSON_TABLE
• jsonpath
• LATERAL subqueries