La syntaxe d'entrée/sortie pour les types de données JSON est identique à celle spécifiée dans la RFC 7159.

Les exemples suivants sont tous des expressions json (ou jsonb) valides :

-- Simple valeur scalaire/primitive
-- Les valeurs primitives peuvent être des nombres, chaînes entre guillemets, true, false ou null
SELECT '5'::json;

-- Tableau de zéro ou plus éléments (les éléments doivent être du même type)
SELECT '[1, 2, "foo", null]'::json;

-- Objets contenant des paires de clé et valeurs
-- À noter que les clés d'objets doivent toujours être des chaînes entre guillemets
SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json;

-- Tableaux et objets peuvent être imbriqués arbitrairement
SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json;
   

Comme dit précédemment, quand une valeur JSON est renseignée puis affichée sans traitement additionnel, json renvoie le même texte qui était fourni en entrée, alors que jsonb ne préserve pas les détails sémantiquement non significatifs comme les espaces. Par exemple, il faut noter la différence ici :

SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::json;
                      json
-------------------------------------------------
 {"bar": "baz", "balance": 7.77, "active":false}
(1 row)

SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::jsonb;
                      jsonb
--------------------------------------------------
 {"bar": "baz", "active": false, "balance": 7.77}
(1 row)
   

un détail sémantiquement non significatif qu'il faut souligner est qu'avec jsonb, les nombres seront affichés en fonction du type numeric sous-jacent. En pratique, cela signifie que les nombres renseignés avec la notation E seront affichés sans. Par exemple :

SELECT '{"reading": 1.230e-5}'::json, '{"reading": 1.230e-5}'::jsonb;
         json          |          jsonb
-----------------------+-------------------------
 {"reading": 1.230e-5} | {"reading": 0.00001230}
(1 row)
   

Toutefois, jsonb préservera les zéros en fin de partie fractionnaire, comme on peut le voir dans cet exemple, même si ceux-ci ne sont pas sémantiquement significatifs, pour des besoins tels que des tests d'égalité.

Pour la liste de fonctions et opérateurs intégrés disponibles pour construire et traiter des valeurs JSON, voir Section 9.16.

Représenter des données en JSON peut être considérablement plus flexible que le modèle de données relationnel traditionnel, qui est contraignant dans des environnements où les exigences sont souples. Il est tout à fait possible que ces deux approches puissent coexister, et qu'elles soient complémentaires au sein de la même application. Toutefois, même pour les applications où on désire le maximum de flexibilité, il est toujours recommandé que les documents JSON aient une structure quelque peu fixée. La structure est typiquement non vérifiée (bien que vérifier des règles métier de manière déclarative soit possible), mais le fait d'avoir une structure prévisible rend plus facile l'écriture de requêtes qui résument utilement un ensemble de « documents » (datums) dans une table.

Les données JSON sont sujettes aux mêmes considérations de contrôle de concurrence que pour n'importe quel autre type de données quand elles sont stockées en table. Même si stocker de gros documents est prévisible, il faut garder à l'esprit que chaque mise à jour acquiert un verrou de niveau ligne sur toute la ligne. Il faut envisager de limiter les documents JSON à une taille gérable pour réduire les contentions sur verrou lors des transactions en mise à jour. Idéalement, les documents JSON devraient chacun représenter une donnée atomique, que les règles métiers imposent de ne pas pouvoir subdiviser en données plus petites qui pourraient être modifiées séparément.

Tester l'inclusion est une capacité importante de jsonb. Il n'y a pas d'ensemble de fonctionnalités parallèles pour le type json. L'inclusion teste si un des documents jsonb est contenu dans un autre. Ces exemples renvoient vrai, sauf note explicite :

-- Simple valeur scalaire/primitive qui contient une seule valeur identique :
SELECT '"foo"'::jsonb @> '"foo"'::jsonb;

-- Le tableau de droite est contenu dans celui de gauche :
SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb;

-- L'ordre des éléments d'un tableau n'est pas significatif, donc ceci est tout
-- aussi vrai :
SELECT '[1, 2, 3]'::jsonb @> '[3, 1]'::jsonb;

-- Les éléments dupliqués d'un tableau n'ont pas plus d'importance :
SELECT '[1, 2, 3]'::jsonb @> '[1, 2, 2]'::jsonb;

-- L'objet avec une seule paire à droite est contenu
-- dans l'objet sur le côté gauche :
SELECT '{"product": "PostgreSQL", "version": 9.4, "jsonb":true}'::jsonb @> '{"version":9.4}'::jsonb;

-- Le tableau du côté droit n'est <emphasis>pas</emphasis> considéré comme contenu
-- dans le tableau du côté gauche, même si un tableau similaire est imbriqué dedans :
SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb;  -- renvoie faux

-- Mais avec une couche d'imbrication, il est contenu :
SELECT '[1, 2, [1, 3]]'::jsonb @> '[[1, 3]]'::jsonb;

-- De la même manière, l'inclusion n'est pas valable ici :
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"bar": "baz"}'::jsonb;  -- renvoie faux

-- Une clé du niveau racine et un objet vide sont contenus :
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"foo": {}}'::jsonb;
  

Le principe général est que l'objet inclus doit correspondre à l'objet devant le contenir à la fois pour la structure et pour les données, peut-être après la suppression d'éléments de tableau ou d'objets paires clé/valeur ne correspondant pas à l'objet contenant. Mais rappelez-vous que l'ordre des éléments dans un tableau n'est pas significatif lors d'une recherche de contenance, et que les éléments dupliqués d'un tableau ne sont réellement considérés qu'une seule fois.

Comme exception qui confirme la règle que les structures doivent correspondre, un tableau peut inclure une valeur primitive :

-- Ce tableau inclut la valeur primitive chaîne :
SELECT '["foo", "bar"]'::jsonb @> '"bar"'::jsonb;

-- Cette exception n'est pas réciproque, la non-inclusion est rapportée ici :
SELECT '"bar"'::jsonb @> '["bar"]'::jsonb;  -- renvoie faux
  

jsonb a également un opérateur d'existence, qui est une variation sur le thème de l'inclusion : il teste si une chaîne (sous forme de valeur text) apparaît comme une clé d'objet ou un élément de tableau au niveau supérieur de la valeur jsonb. Ces exemples renvoient vrai; sauf note explicite :

-- La chaîne existe comme un élément de tableau :
SELECT '["foo", "bar", "baz"]'::jsonb ? 'bar';

-- La chaîne existe comme une clé d'objet :
SELECT '{"foo": "bar"}'::jsonb ? 'foo';

-- Les valeurs d'objets ne sont pas examinées :
SELECT '{"foo": "bar"}'::jsonb ? 'bar';  -- renvoie faux

-- Comme pour l'inclusion, l'existence doit correspondre au niveau supérieur :
SELECT '{"foo": {"bar": "baz"}}'::jsonb ? 'bar'; -- renvoie faux

-- Une chaîne est examinée pour l'existence si elle correspond à une primitive chaîne JSON :
SELECT '"foo"'::jsonb ? 'foo';
  

Les objets JSON sont plus adaptés que les tableaux pour tester l'inclusion ou l'existence quand il y a de nombreux éléments ou clés impliqués, car contrairement aux tableaux, ils sont optimisés de manière interne pour la recherche et n'ont pas besoin d'être parcourus linéairement.

SELECT doc->'site_name' FROM websites
  WHERE doc @> '{"tags":[{"term":"paris"}, {"term":"food"}]}';
    

SELECT doc->'site_name' FROM websites
  WHERE doc->'tags' @> '[{"term":"paris"}, {"term":"food"}]';
    

Les différents opérateurs d'inclusion d'existence, avec tous les autres opérateurs et fonctions JSON, sont documentés dans Section 9.16.

Les index GIN peuvent être utilisés pour chercher efficacement des clés ou des paires clé/valeur se trouvant parmi un grand nombre de documents (datums) jsonb. Deux « classes d'opérateurs » GIN sont fournies, offrant différents compromis entre performances et flexibilité.

La classe d'opérateur GIN par défaut pour jsonb supporte les requêtes avec des opérateurs clé-existe ?, ?, ?| et ?&, l'opérateur de contenant @>, et les opérateurs de correspondance jsonpath @? et @@. et l'opérateur path/valeur-existe (Pour des détails sur la sémantique que ces opérateurs implémentent, voir Tableau 9.45.) Un exemple de création d'index avec cette classe d'opérateurs est :

CREATE INDEX idxgin ON api USING GIN (jdoc);
   

La classe d'opérateurs GIN qui n'est pas par défaut jsonb_path_ops ne supporte pas les opérateurs clé-existe mais il supporte @>, @? et @@. Un exemple de création d'index avec cette classe d'opérateurs est :

CREATE INDEX idxginp ON api USING GIN (jdoc jsonb_path_ops);
   

En étudiant l'exemple d'une table qui stocke des documents JSON récupérés par un service web tiers, avec une définition de schéma documentée, un document typique serait :

{
    "guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a",
    "name": "Angela Barton",
    "is_active": true,
    "company": "Magnafone",
    "address": "178 Howard Place, Gulf, Washington, 702",
    "registered": "2009-11-07T08:53:22 +08:00",
    "latitude": 19.793713,
    "longitude": 86.513373,
    "tags": [
        "enim",
        "aliquip",
        "qui"
    ]
}
   

Ces documents sont stockés dans une table nommée api, dans une colonne de type jsonb nommée jdoc. Si un index GIN est créé sur cette colonne, des requêtes semblables à l'exemple suivant peuvent utiliser cet index :

-- Trouver les documents dans lesquels la clé "company" a pour valeur "Magnafone"
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company": "Magnafone"}';
   

Toutefois, cet index ne pourrait pas être utilisé pour des requêtes comme dans l'exemple suivant, car bien que l'opérateur ? soit indexable, il n'est pas appliqué directement sur la colonne indexée jdoc :

-- Trouver les documents dans lesquels la clé "tags" contient une clé ou un élément tableau "qui"
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc -> 'tags' ? 'qui';
   

Toutefois, avec l'utilisation appropriée d'index sur expression, la requête ci-dessus peut utiliser un index. Si le requêtage d'éléments particuliers de la clé "tags" est fréquent, définir un index comme ceci pourrait être particulièrement bénéfique :

-- À noter que l'opérateur "jsonb -> text" ne peut être appelé que sur un
-- objet JSON, donc la conséquence de créer cet index est que le premier niveau de
-- chaque valeur "jdoc" doit être un objet. Ceci est vérifié lors de chaque insertion.
CREATE INDEX idxgintags ON api USING GIN ((jdoc -> 'tags'));
   

Dorénavant, la clause WHERE jdoc -> 'tags' ? 'qui' sera reconnue comme une application de l'opérateur indexable ? pour l'expression indexée jdoc -> 'tags'. (Plus d'informations sur les index sur expression peuvent être trouvées dans Section 11.7.)

Une autre approche pour le requêtage et l'exploitation de l'inclusion, par exemple :

-- Trouver les documents dans lesquels la clé "tags" inclut l'élément tableau "qui"
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qui"]}';
   

Un simple index GIN sur la colonne jdoc peut répondre à cette requête. Mais il faut noter qu'un tel index stockera des copies de chaque clé et chaque valeur de la colonne jdoc, alors que l'index sur expression de l'exemple précédent ne stockera que les données trouvées pour la clé tags. Alors que l'approche d'index simple est bien plus souple (puisqu'elle supporte les requêtes sur n'importe quelle clé), les index sur des expressions ciblées ont bien plus de chances d'être plus petits et plus rapides pour la recherche qu'un simple index.

Les index GIN acceptent aussi les opérateurs @? et @@, qui réalisent la correspondance jsonpathmatching. Voici deux exemples :

SELECT jdoc->'guid', jdo->'name' FROM api WHERE jdoc @? '$.tags[*] ? (@ == "qui")';

SELECT jdoc->'guid', jdo->'name' FROM api WHERE jdoc @@ '$.tags[*] == "qui"';

Pour ces opérateurs, l'index GIN extrait les clauses de la forme accessors_chain = constat en dehors du modèle jsonpath, et réalise la recherche dans l'index en se basant sur les clés et valeurs mentionnées dans ces clauses. La chaîne d'accessoires peut inclure les accessoires .key, [*], et [index]. La classe d'opérateur jsonb_ops accepte aussi les accessoires .* et .**, mais ce n'est pas le cas de la classe d'opérateur jsonb_path_ops.

Bien que la classe d'opérateur jsonb_path_ops ne supporte que les requêtes avec les opérateurs @>, @? et @@, elle a des avantages de performances notables par rapport à la classe d'opérateur par défaut jsonb_ops. Un index jsonb_path_ops est généralement bien plus petit qu'un index jsonb_ops pour les mêmes données, et la spécificité de la recherche est meilleure, particulièrement quand les requêtes contiennent des clés qui apparaissent fréquemment dans les données. Par conséquent, les opérations de recherche sont généralement plus performantes qu'avec la classe d'opérateur par défaut.

La différence technique entre des index GIN jsonb_ops et jsonb_path_ops est que le premier crée des éléments d'index indépendants pour chaque clé et valeur dans les données, alors que le second crée des éléments d'index uniquement pour chaque valeur dans les données. ^[7] Fondamentalement, chaque élément d'index jsonb_path_ops est un hachage de la valeur et de la ou des clés y menant ; par exemple pour indexer {"foo": {"bar": "baz"}}, un seul élément dans l'index sera créé, incorporant les trois foo, bar et baz dans une valeur hachée. Ainsi, une requête d'inclusion cherchant cette structure résulterait en une recherche d'index extrêmement spécifique, mais il n'y a pas d'autre moyen de savoir si foo apparaît en tant que clé. D'un autre côté, un index jsonb_ops créerait trois éléments d'index représentant foo, bar et baz séparément ; ainsi, pour faire la requête d'inclusion, il faudrait rechercher les lignes contenant chacun des trois éléments. Bien que les index GIN puissent effectuer de telles recherches et de manière tout à fait efficace, cela sera toujours moins spécifique et plus lent que la recherche équivalente jsonb_path_ops, surtout s'il y a un très grand nombre de lignes contenant n'importe lequel des trois éléments d'index.

Un désavantage de l'approche jsonb_path_ops est qu'elle ne produit d'entrées d'index que pour les structures JSON ne contenant aucune valeur, comme {"a": {}}. Si une recherche pour des documents contenant une telle structure est demandée, elle nécessitera un parcours de la totalité de l'index, ce qui peut être assez long. jsonb_path_ops est donc mal adapté pour des applications qui effectuent souvent de telles recherches.

jsonb supporte également les index btree et hash. Ceux-ci ne sont généralement utiles que s'il est important de vérifier l'égalité de documents JSON entiers. Le tri btree pour des données jsonb est rarement d'un grand intérêt, mais afin d'être exhaustif, il est :

Objet > Tableau > Booléen > Nombre > Chaîne > Null

Objet avec n paires > objet avec n - 1 paires

Tableau avec n éléments > tableau avec n - 1 éléments
   

Les objets avec le même nombre de paires sont comparés dans cet ordre :

clé-1, valeur-1, clé-2 ...
   

À noter que les clés d'objet sont comparées dans leur ordre de stockage ; en particulier, puisque les clés les plus courtes sont stockées avant les clés les plus longues, cela peut amener à des résultats contre-intuitifs, tels que :

{ "aa": 1, "c": 1} > {"b": 1, "d": 1}
   

De la même manière, les tableaux avec le même nombre d'éléments sont comparés dans l'ordre :

élément-1, élément-2 ...
   

Les valeurs JSON primitives sont comparées en utilisant les mêmes règles de comparaison que pour les types de données PostgreSQL sous-jacents. Les chaînes sont comparées en utilisant la collation par défaut de la base de données.

Le type de données jsonb accepte les expressions avec indice, du style tableau, pour extraire et modifier les éléments. Les valeurs imbriquées peuvent être indiquées en chaînant les expressions à indice, en suivant les mêmes règles que l'argument path dans la fonction jsonb_set. Si une valeur jsonb est un tableau, les indices numériques commencent à zéro, et les nombres négatifs comptent àpartir du dernier élément du tableau. Les expressions d'intervalle ne sont pas acceptées. Le résultat d'une expression par indice est toujours du type de données jsonb.

Les requêtes UPDATE peuvent utiliser l'indiçage dans la clause SET pour modifier les valeurs jsonb. Les chemins d'indice doivent être parcourables pour toutes les valeurs affectées. Par exemple, le chemin val['a'] ['b']['c'] peut être entièrement traversé jusqu'à c si val, val ['a'] et val['a']['b'] sont des objets. Si val['a'] ou val['a']['b'] ne sont pas définis, il sera créé comme un objet vide et rempli comme nécessaire. Néanoins, si val lui-même ou une des valeurs intermédiaires est défini comme un non-objet (une chaîne, un nombre ou le null jsonb), la traversée ne peut pas continuer, donc une erreur est levée et la transaction est annulée.

Un exemple de syntaxe avec indiçage :

-- Extraire la valeur d'un objet par sa clé
SELECT ('{"a": 1}'::jsonb)['a'];

-- Extraire une valeur d'objet imbriqué par le chemin de la clé
SELECT ('{"a": {"b": {"c": 1}}}'::jsonb)['a']['b']['c'];

-- Extraire l'élément du tableau par indice
SELECT ('[1, "2", null]'::jsonb)[1];

-- Mise à jour de la valeur de l'objet par sa clé.
-- Notez les guillemets simples autour de '1' : la valeur affectée
-- doit aussi être du type jsonb
UPDATE table_name SET jsonb_field['key'] = '1';

-- Ceci lèvera une erreur si un enregistrement de jsonb_field['a']['b'] se révèle
-- être autre chose qu'un objet. Par exemple, la valeur {"a": 1} a une valeur numérique
-- de la clé 'a'.
UPDATE table_name SET jsonb_field['a']['b']['c'] = '1';

-- Filtrer lesenregistrements en utilisant une clause WHERE avec des indices.
-- Comme le résultat de l'indiçage est jsonb, la valeur à laquelle nous la comparons
-- doit aussi être jsonb.
-- Les guillemets doubles rendent valide "value" comme chaîne jsonb.
SELECT * FROM table_name WHERE jsonb_field['key'] = '"value"';

L'affectation de jsonb via l'indiçage gère quelques cas particuliers différemment de jsonb_set. Quand une valeur source jsonb vaut NULL, l'affectation via l'indiçage continuera comme s'il s'agissait d'une valeur JSON vide (objet ou tableau) impliquée par la clé d'indiçage :

-- Là où jsonb_field valait NULL, il vaut maintenant {"a": 1}
UPDATE table_name SET jsonb_field['a'] = '1';

-- Là où jsonb_field valait NULL, il vaut maintenant [1]
UPDATE table_name SET jsonb_field[0] = '1';

Si un index est indiqué pour un tableau contenant trop peu d'éléments, les éléments NULL seront ajoutés jusqu'à ce que l'index soit atteignable et que la valeur puisse être initialisée.

-- Là où jsonb_field valait [], il vaut maintenant [null, null, 2];
-- Là où jsonb_field valait [0], il vaut maintenant [0, null, 2]
UPDATE table_name SET jsonb_field[2] = '2';

Une valeur de type jsonb acceptera des affectations sur des chemins indicés non existants car le dernier élément existant à être traversé est un objet ou un tableau, comme impliqué par l'indice correspondant (l'élément indiqué par le dernier indice dans le chemin n'est pas traversé et pourrait être n'improte quoi). Le tableau imbriqué et les structures d'objet seront créés et, dans le premier cas, aligné avec des null, comme spécifié par le chemin d'indice jusqu'à ce que la valeur affectée puisse être placée.

-- Là où jsonb_field valait {}, il vaut maintenant {"a": [{"b": 1}]}
UPDATE table_name SET jsonb_field['a'][0]['b'] = '1';

-- Là où jsonb_field valait [], il vaut maintenant [null, {"a": 1}]
UPDATE table_name SET jsonb_field[1]['a'] = '1';

Des extensions supplémentaires sont disponibles pour implémenter des transformations pour le type jsonb pour différents langages de procédure stockée.

Les extensions pour PL/Perl sont appelées jsonb_plperl et jsonb_plperlu. Si vous les utilisez, les valeurs jsonb sont transformées en tableaux, hachages et scalaires Perl, suivant le cas.

Les extensions pour PL/Python sont appelées jsonb_plpythonu, jsonb_plpython2u et jsonb_plpython3u (voir Section 46.1 pour la convention de nommage PL/Python). Si vous les utilisez, les valeurs jsonb sont transformées en dictionnaires, listes et scalaires Python, suivant le cas.

Pour ces extensions, jsonb_plperl est considéré de confiance (« trusted »), autrement dit il peut être installé par des utilisateurs qui n'ont pas l'attribut SUPERUSER mais qui ont le droit CREATE sur la base actuelle. Le reste nécessite d'être super-utilisateur pour être installé.

Le type jsonpath implémente le support du langage SQL/JSONpath dans PostgreSQL de façon à requêter correctement dans les données JSON. Cela fournit une représentation binaire de l'expression SQL/JSONpath analysée qui spécifie les valeurs qui doivent être récupérées par le moteur de chemin depuis les données JSON pour être traités par la suite avec les fonctions de requêtage SQL/JSON.

La sémantique des prédicats et opérateurs suit généralement celle du SQL. Dans le même temps, il propose une manière naturelle de travailler avec les données JSON. En effet, la syntaxe SQL/JSONpath utilise certaines conventions JavaScript :

Une expression de chemin SQL/JSON est typiquement écrite dans une requête SQL comme une constante chaîne de caractères SQL, donc elle doit être entourée par des guillemets simples, et tout guillemet simple souhaité dans la valeur doit être doublé (voir Section 4.1.2.1). Certaines formes d'expressions de chemin requièrent des constantes de chaîne à l'intérieur. Ces constantes de chaînes intégrées suivent les conventions JavaScript/ECMAScript : elles doivent être entourées par des guillemets doubles et les échappements doivent être utilisées pour représenter les caractères difficiles à saisir. En particulier, la façon d'écrire un guillemet double dans une constante de type chaîne est \", et pour écrire un antislash, vous devez écrire \\. Les autres séquences spéciales d'échappement incluent celles reconnues dans les chaînes JSON : \b, \f, \n, \r, \t, \v pour les différents caractères de contrôle ASCII, et \uNNNN pur un caractère Unicode identifié par son code sur 4 chiffres hexadécimaux. La syntaxe antislash inclut aussi deux cas non autorisés par JSON : \xNN pour un code caractère écrit avec deux chiffres hexadécimaux, et \u{N...} pour un code caractère écrit avec 1 à 6 chiffres hexadécimaux.

Une expression path consiste en une séquence d'éléments path, qui peuvent être une des suivants :

Pour plus de détails concernant l'utilisation des expressions jsonpath avec des fonctions de requête SQL/JSON, voir Section 9.16.2.