jsonb est le type de colonne JSON binaire de PostgreSQL. Lisez les champs avec -> (renvoie du jsonb) et ->> (renvoie du text), trouvez des lignes avec l'opérateur de contenance @>, et modifiez des champs avec jsonb_set ou ||. Un index GIN rend ces requêtes rapides.
Les commandes 1 et 3 correspondent. data->'customer' renvoie l'objet imbriqué en jsonb, ->>'name' en extrait le nom sous forme de texte, et ->> suivi d'une conversion transforme le total en nombre.
Extraire des valeurs d'un JSONB
| Opérateur | Renvoie | Exemple |
|---|---|---|
-> clé ou indice | jsonb | data->'customer', data->'tags'->0 |
->> clé ou indice | text | data->>'status' |
#> chemin | jsonb | data#>'{customer,city}' |
#>> chemin | text | data#>>'{customer,city}' |
[...] indiçage (PostgreSQL 14 et plus) | jsonb | data['customer']['city'] |
Les indices de tableau commencent à 0, et un indice négatif compte depuis la fin (-> -1 est le dernier élément). Une clé absente donne un NULL SQL, pas une erreur, donc la commande 3 ci-dessus a une ville NULL.
Utilisez ->> pour comparer avec du texte ou pour convertir : data->>'status' = 'paid'. Utilisez -> pour continuer à naviguer. Une erreur fréquente est d'écrire data->'status' = 'paid'. PostgreSQL lit 'paid' comme du JSON, un mot sans guillemets n'est pas du JSON valide, et la requête échoue avec « invalid input syntax for type json ».
Trouver des lignes par leur contenu JSON
@> teste si la valeur de gauche contient celle de droite. Il marche à toute profondeur et dans les tableaux, et un index GIN le prend en charge :
Les produits 1 et 2 existent en taille M et ont un prix ; seul le 2 coûte plus de 30. Les opérateurs de recherche :
@>contient,<@est contenu dans.?la clé (ou la chaîne du tableau) existe,?|l'une des clés existe,?&toutes existent.@?et@@lancent une requête JSON path (PostgreSQL 12 et plus), pour les comparaisons à l'intérieur du document.
Mettre à jour des champs d'un JSONB
PostgreSQL réécrit toute la valeur à chaque mise à jour, mais ces fonctions vous permettent de ne décrire que le changement :
jsonb_set(target, path, new_value)remplace une valeur à un chemin donné.-supprime une clé, et#-supprime un chemin imbriqué comme'{notify,sms}'.||ajoute ou écrase des clés de premier niveau.
Depuis PostgreSQL 14, vous pouvez aussi affecter par indiçage : SET prefs['notify']['email'] = 'false'.
Deux pièges :
jsonb_setrenvoieNULLquand la nouvelle valeur est leNULLSQL, et l'UPDATEefface alors toute la colonne. PassezCOALESCE(to_jsonb(x), 'null'), ou utilisezjsonb_set_lax(PostgreSQL 13 et plus), qui stocke par défaut unnullJSON.jsonb_setajoute une dernière clé manquante, mais pas des parents manquants : définir'{a,b}'sur{}le laisse inchangé. L'affectation par indiçage, elle, les crée.
Transformer des tableaux JSON en lignes
jsonb_array_elements renvoie une ligne par élément du tableau, et jsonb_to_recordset transforme un tableau d'objets en colonnes typées. Depuis PostgreSQL 17, le JSON_TABLE standard fait les deux au même endroit :
A-1 s'est vendu 3 fois et B-2 une fois. Dans l'autre sens, jsonb_build_object('id', id, 'name', name) construit un objet à partir de colonnes et jsonb_agg rassemble des lignes dans un tableau (voir string_agg et array_agg).
Indexer un JSONB avec GIN
Un index GIN sur toute la colonne sert @>, ?, ?|, ?&, @? et @@ :
CREATE INDEX orders_data_idx ON orders USING gin (data);
-- smaller and faster, but only for @>, @? and @@
CREATE INDEX orders_data_path_idx ON orders USING gin (data jsonb_path_ops);
-- one field compared with = or a range: a B-tree on the expression
CREATE INDEX orders_status_idx ON orders ((data->>'status'));
Écrivez la requête sous la forme que l'index prend en charge. WHERE data @> '{"status": "paid"}' peut utiliser l'index GIN. WHERE data->>'status' = 'paid' ne le peut pas ; il lui faut l'index sur expression. Un test d'intervalle comme (data->>'total')::numeric > 50 demande un index sur cette expression exacte.
JSON ou JSONB
json | jsonb | |
|---|---|---|
| Stocké sous forme de | Le texte d'entrée | Une forme binaire déjà analysée |
| Espaces, ordre des clés, clés en double | Conservés | Supprimés ; le dernier doublon gagne |
| Écriture | Un peu plus rapide (seulement validé) | Analysé à l'entrée |
| Lecture et requêtes | Réanalysé à chaque accès | Rapide |
@>, ? et index GIN | Non | Oui |
json renvoie le texte inchangé ; jsonb ne garde que "a": 3 et réordonne les clés. Utilisez jsonb, sauf si vous devez renvoyer le texte d'origine octet pour octet, comme le payload d'un webhook stocké. Gardez dans des colonnes normales les champs que toutes les lignes ont, ou qui ont besoin d'une clé étrangère. La référence officielle des fonctions JSON liste chaque opérateur et chaque fonction.
Questions fréquentes
Quelle est la différence entre -> et ->> en PostgreSQL ?
-> renvoie la valeur en jsonb, ce qui permet de continuer à naviguer (data->'customer'->'city'). ->> la renvoie en text, la forme à comparer avec une chaîne ou à convertir en nombre : (data->>'total')::numeric.
Comment vérifier qu'un tableau JSONB contient une valeur ?
Utilisez la contenance : data->'tags' @> '["gift"]', ou data @> '{"tags": ["gift"]}' pour tester depuis la racine du document. Pour un tableau de chaînes, data->'tags' ? 'gift' marche aussi. Un index GIN sur data accélère la deuxième forme.
JSONB garde-t-il l'ordre des clés ?
Non. jsonb range les clés dans son propre ordre, supprime les espaces et ne garde que la dernière des clés en double. Si vous devez récupérer exactement le texte d'origine, utilisez le type json.
Comment vérifier qu'une clé existe dans un JSONB ?
data ? 'key' est vrai quand l'objet de premier niveau a cette clé, même si sa valeur est le null JSON. ?| prend un tableau et est vrai si l'une des clés existe, ?& si toutes existent.
Peut-on mettre une clé étrangère ou une contrainte NOT NULL sur un champ d'un JSONB ?
Pas directement. Une contrainte CHECK peut tester un champ (CHECK (data ? 'email')), mais une clé étrangère a besoin d'une vraie colonne. Les champs que toutes les lignes ont et que d'autres tables référencent ont leur place dans des colonnes ordinaires.