PostgreSQLLa base de données la plus sophistiquée au monde.
Documentation PostgreSQL 10.23 » Langage SQL » Types de données » Types JSON

8.14. Types JSON

Les types de données JSON sont faits pour stocker des données JSON (JavaScript Object Notation), comme spécifié dans la RFC 7159. De telles données peuvent également être stockées comme text , mais les types de données JSON ont l'avantage d'assurer que chaque valeur stockée est valide d'après les règles JSON. Il y a également des fonctions et opérateurs spécifiques à JSON associés disponibles pour les données stockées dans ces types de données. Voir Section 9.15.

Il y a deux types de données JSON : json et jsonb. Ils acceptent quasiment des ensembles de valeurs identiques en entrée. La différence majeure réside dans l'efficacité. Le type de données json stocke une copie exacte du texte en entrée, que chaque fonction doit analyser à chaque exécution, alors que le type de données jsonb est stocké dans un format binaire décomposé qui rend l'insertion légèrement plus lente du fait du surcoût de la conversion, mais est significativement plus rapide pour traiter les données, puisqu'aucune analyse n'est nécessaire. jsonb gère également l'indexation, ce qui peut être un avantage significatif.

Puisque le type json stocke une copie exacte du texte en entrée, il conservera les espaces sémantiquement non significatifs entre les jetons, ainsi que l'ordre des clés au sein de l'objet JSON. De plus, si un objet JSON contient dans sa valeur la même clé plus d'une fois, toutes les paires clé/valeur sont conservées (les fonctions de traitement considèrent la dernière valeur comme celle significative). À l'inverse, jsonb ne conserve ni les espaces non significatifs, ni l'ordre des clés d'objet, ni ne conserve les clés d'objet dupliquées. Si des clés dupliquées sont présentées en entrée, seule la dernière valeur est conservée.

En général, la plupart des applications devraient préférer stocker les données JSON avec jsonb, à moins qu'il y ait des besoins spécifiques, comme la supposition légitime de l'ordre des clés d'objet.

PostgreSQL n'autorise qu'un seul encodage de caractères par base de données. Il n'est donc pas possible pour les types JSON de se conformer de manière rigoureuse à la spécification JSON, à moins que l'encodage de la base de données soit UTF8. Tenter d'inclure directement des caractères qui ne peuvent pas être représentés dans l'encodage de la base de données échouera ; inversement, des caractères qui peuvent être représentés dans l'encodage de la base de données, mais pas en UTF8, seront autorisés.

La RFC 7159 autorise les chaînes JSON à contenir des séquences Unicode échappées, indiquées avec \uXXXX. Dans la fonction d'entrée pour le type json, les échappements Unicode sont autorisés quel que soit l'encodage de la base de données, et sont vérifiés uniquement pour l'exactitude de la syntaxe (qui est quatre chiffres hexadécimaux précédés d'un \u). Toutefois, la fonction d'entrée pour jsonb est plus stricte : elle interdit les échappements Unicode pour les caractères autres que ASCII (ceux au-delà de U+007F) à moins que l'encodage de la base de données soit UTF8. Le type jsonb rejette aussi \u0000 (parce qu'il ne peut pas être représenté avec le type text de PostgreSQL), et il insiste pour que chaque utilisation de paires de substitution Unicode désignant des caractères en dehors du Unicode Basic Multilingual Plane soit correcte. Les échappements Unicode valides sont convertis en leur caractère ASCII ou UTF8 équivalent pour du stockage ; ceci inclut les « folding surrogate pairs » sur un seul caractère.

Note

De nombreuses fonctions de traitement JSON décrites dans Section 9.15 convertiront les échappements Unicode vers des caractères standards, et généreront donc le même type d'erreurs décrit juste avant si leur entrée est de type json et non jsonb. Le fait que la fonction d'entrée json ne fasse pas ces vérifications peut être considéré comme un artefact historique, bien qu'elle n'autorise pas un simple stockage (sans traitement) d'échappements Unicode JSON dans une base de données en encodage non UTF8. En général, il est préférable d'éviter de mélanger des échappements Unicode en JSON avec une base de données en encodage non UTF8 si possible.

Lors de la conversion de données texte JSON vers jsonb, les types primitifs décrits par la RFC 7159 sont transcrits efficacement vers des types PostgreSQL natifs, comme indiqué dans Tableau 8.23. Par conséquent, il y a quelques contraintes additionnelles mineures sur ce qui constitue des données jsonb valides qui ne s'appliquent ni au type json, ni à JSON en définitive, correspondant aux limites de ce qui peut être représenté par le type de données sous-jacent. Spécifiquement, jsonb rejettera les nombres qui sont en dehors de la portée du type de données numeric de PostgreSQL, alors que json les acceptera. De telles restrictions définies par l'implémentation sont permises par la RFC 7159. Cependant, en pratique, de tels problèmes ont beaucoup plus de chances de se produire dans d'autres implémentations, puisqu'il est habituel de représenter les types primitifs number JSON comme des nombres flottants à double précision (IEEE 754 double precision floating point), ce que la RFC 7159 anticipe explicitement et autorise. Lorsque JSON est utilisé comme format d'échange avec de tels systèmes, le risque de perte de précision pour les valeurs numériques comparées aux données stockées à l'origine par PostgreSQL devrait être considéré.

À l'inverse, comme indiqué dans le tableau, il y a quelques restrictions mineures sur le format d'entrée de types primitifs JSON qui ne s'appliquent pas aux types PostgreSQL correspondants.

Tableau 8.23. Types primitifs JSON et types PostgreSQL correspondants

Type primitif JSONType PostgreSQLNotes
stringtext\u0000 est interdit, tout comme les échappements Unicode non-ASCII si l'encodage de la base de données n'est pas UTF8
numbernumericLes valeurs NaN et infinity sont interdites
booleanbooleanSeules les versions en minuscule de true et false sont acceptées
null(none)NULL dans SQL est un concept différent

8.14.1. Syntaxe d'entrée et de sortie JSON

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é.

8.14.2. Concevoir des documents JSON efficacement

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.

8.14.3. Existence et inclusion jsonb

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.

Astuce

Comme les documents JSON sont imbriqués, une requête appropriée peut ignorer une sélection explicite de sous-objets. Par exemple, supposons que nous ayons une colonne doc contenant des objets au plus haut niveau, avec la plupart des objets contenant les champs tags qui contiennent eux-mêmes des tableaux de sous-objets. Cette requête trouve des entrées dans lesquelles les sous-objets contiennent à la fois "term":"paris" et "term":"food", tout en ignorant ces clés en dehors du tableau tags :

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

Cela pourrait s'accomplir aussi ainsi :

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

mais cette approche est moins flexible, et souvent bien moins efficace.

Mais l'opérateur JSON d'existence n'est pas imbriqué : il cherchera seulement pour la clé ou l'élément de tableau spécifié à la racine de la valeur JSON.

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

8.14.4. Indexation jsonb

Les index GIN peuvent être utilisés pour chercher efficacement des clés ou 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 de haut niveau clé-existe ?, ?& et des opérateurs ?| et l'opérateur chemin/valeur-existe @>. (Pour des détails sur la sémantique que ces opérateurs implémentent, voir Tableau 9.44.) 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 supporte l'indexation de l'opérateur @> seulement. 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.

Bien que la classe d'opérateur jsonb_path_ops ne supporte que les requêtes avec l'opérateur @>, 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. [6] 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.



[6] Dans ce contexte, le terme « valeur » inclut les éléments de tableau, bien que la terminologie JSON considère parfois que les éléments de tableaux soient distincts des valeurs dans les objets.