Tableau 9.40, « Opérateurs json et jsonb » montre les opérateurs disponibles avec des données des deux types JSON (voir Section 8.14, « Types JSON »).
Tableau 9.40. Opérateurs json et jsonb
| Opérateur | Type de l'opérande droit | Description | Exemple | Résultat de l'exemple |
|---|---|---|---|---|
| -> | int | Obtient un élément du tableau JSON (indexé à partir de zéro, un entier négatif compte à partir de la fin) | '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2 | {"c":"baz"} |
| -> | text | Obtient un champ de l'objet JSON par sa clé | '{"a": {"b":"foo"}}'::json->'a' | {"b":"foo"} |
| ->> | int | Obtient un élément du tableau JSON en tant que text | '[1,2,3]'::json->>2 | 3 |
| ->> | text | Obtient un champ de l'objet JSON en tant que text | '{"a":1,"b":2}'::json->>'b' | 2 |
| #> | text[] | Obtient un objet JSON à partir du chemin spécifié | '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}' | {"c": "foo"} |
| #>> | text[] | Obtient un objet JSON à partir du chemin spécifié en tant que text | '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}' | 3 |
![[Note]](../images/note.png)
Il existe des variantes de ces opérateurs pour les types json et jsonb. Les opérateurs d'extraction de champ/élément/chemin renvoient le même type de données que l'élément à gauche (soit json, soit jsonb), sauf pour ceux indiquant renvoyer du text, qui forcera la conversion vers le type text. Les opérateurs d'extraction de champ/élément/chemin renvoient NULL, plutôt que d'échouer, si la valeur JSON en entrée n'a pas la structure correspondant à la demande ; par exemple si un tel élément n'existe pas. Les opérateurs d'extraction de champ/élément/chemin qui acceptent un index d'un tableau JSON supportent tous un index négatif qui décompte à partir de la fin.
Les opérateurs de comparaison standards montrés dans Tableau 9.1, « Opérateurs de comparaison » sont disponibles pour le type jsonb, mais pas pour le type json. Ils suivent les règles de tri des opérations B-tree soulignées dans Section 8.14.4, « Indexation jsonb ».
Des opérateurs supplémentaires existent seulement pour le type jsonb, comme indiqué dans Tableau 9.41, « Opérateurs jsonb supplémentaires ». Plusieurs de ces opérateurs peuvent être indexés par les classes d'opérateur jsonb. Pour une description complète du contenant jsonb et des sémantiques, voir Section 8.14.3, « Existence et inclusion jsonb ». Section 8.14.4, « Indexation jsonb » décrit comment ces opérateurs peuvent être utilisés pour indexer efficacement les données de type jsonb.
Tableau 9.41. Opérateurs jsonb supplémentaires
| Opérateur | Type de l'opérande droit | Description | Exemple |
|---|---|---|---|
| @> | jsonb | Est-ce que la valeur JSON contient au premier niveau les entrées clefs/valeurs de la valeur JSON à sa droite ? | '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb |
| <@ | jsonb | Les entrées clefs/valeurs de la valeur JSON sont-elles contenues au premier niveau de la valeur JSON de droite ? | '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb |
| ? | text | Est-ce que la chaîne existe comme clef de premier niveau dans la valeur JSON ? | '{"a":1, "b":2}'::jsonb ? 'b' |
| ?| | text[] | Est-ce qu'une au moins des chaînes contenues dans le tableau existe comme clef de premier niveau ? | '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c'] |
| ?& | text[] | Est-ce que toutes les chaînes du tableau existent comme clef de premier niveau ? | '["a", "b"]'::jsonb ?& array['a', 'b'] |
| || | jsonb | Effectue la concaténation de deux valeurs de type jsonb dans une nouvelle valeur jsonb | '["a", "b"]'::jsonb || '["c", "d"]'::jsonb |
| - | text | Supprime la paire clef/valeur ou l'élément de type chaîne de l'opérande de gauche. Les paires clefs/valeurs sont sélectionnées selon la valeur de leur clef. | '{"a": "b"}'::jsonb - 'a' |
| - | integer | Supprime l'élément du tableau ayant l'index indiqué (les nombres négatifs décomptent à partir de la fin du tableau). Lève une erreur si le conteneur de premier niveau n'est pas un tableau | '["a", "b"]'::jsonb - 1 |
| #- | text[] | Supprime le champ ou l'élément ayant le chemin indiqué (pour les tableaux JSON, les chiffres négatifs décomptent à partir de la fin) | '["a", {"b":1}]'::jsonb #- '{1,b}' |
L'opérateur || concatène deux objets JSON en générant un objet contenant l'union de leurs clés, en prenant la valeur du deuxième objet quand les clés sont dupliquées. Tous les autres cas produisent un tableau JSON : tout d'acord, tout entrée qui n'est pas un tableau est convertie en un tableau à un seul élément, puis les deux tableaux sont concaténés. Il ne travaille pas récursivement. Seul le tableau ou la structure objet de haut niveau est assemblé.
Tableau 9.42, « Fonctions de création de données JSON » montre les fonctions disponibles pour la création de valeurs json and jsonb values. (Il n'y a pas de fonctions équivalentes pour le type jsonb des fonctions row_to_json et array_to_json. Cependant, la fonction to_jsonb fournit la plupart des fonctionnalités que ces fonctions fourniraient.)
Tableau 9.42. Fonctions de création de données JSON
| Fonction | Description | Exemple | Exemple du résultat | |
|---|---|---|---|---|
|
to_json(anyelement) to_jsonb(anyelement) |
Renvoie la valeur en tant que type json ou jsonb. Les tableaux et valeurs composites sont convertis (récursivement) en tableaux et objets. Dans le cas contraire, s'il existe une conversion de ce type vers le type json, la fonction de conversion sera utilisée pour réaliser la conversion. Dans les autres cas, une valeur scalaire est produite. Pour tout type scalaire autre qu'un nombre, un booléen ou une valeur NULL, la représentation textuelle sera utilisée, de telle manière que cela soit une valeur valide pour les types json ou jsonb. | to_json('Fred said "Hi."'::text) | "Fred said \"Hi.\"" | |
| array_to_json(anyarray [, pretty_bool]) | json | Renvoie le tableau sous la forme d'un tableau JSON. Un tableau PostgreSQL multidimensionnel devient un tableau JSON de tableaux. Des retours à la ligne seront ajoutés entre les éléments de la première dimension si pretty_bool vaut true. | array_to_json('{{1,5},{99,100}}'::int[]) | [[1,5],[99,100]] |
| row_to_json(record [, pretty_bool]) | json | Renvoie la ligne sous la forme d'un objet JSON. Des retours à la ligne seront ajoutés entre les éléments du niveau 1 si pretty_bool vaut true. | row_to_json(row(1,'foo')) | {"f1":1,"f2":"foo"} |
|
json_build_array(VARIADIC "any") jsonb_build_array(VARIADIC "any") |
json | Construit un tableau JSON de type possiblement hétérogène à partir d'une liste d'arguments variables. | json_build_array(1,2,'3',4,5) | [1, 2, "3", 4, 5] |
| json_build_object(VARIADIC "any") | int | Construit un objet JSON à partir d'une liste d'arguments variables. Par convention, la liste d'arguments consiste en des clés et valeurs en alternance. | json_build_object('foo',1,'bar',2) | {"foo": 1, "bar": 2} |
|
json_object(text[]) jsonb_object(text[]) |
Construit un objet JSON à partir d'un tableau de textes. Le tableau doit avoir soit exactement une dimension avec un nombre pair de membres, auquel cas ils sont pros comme des paires clé/valeur en alternance, soit deux dimensions, de telle façon que chaque tableau interne contienne exactement deux éléments, qui sont pris sous la forme d'une paire clé/valeur. |
json_object('{a, 1, b, "def", c, 3.5}') json_object('{{a, 1},{b, "def"},{c, 3.5}}') |
{"a": "1", "b": "def", "c": "3.5"} | |
|
json_object(keys text[], values text[]) jsonb_object(keys text[], values text[]) |
Cette forme de json_object prend des clés et valeurs sous forme de paires à partir de deux tableaux séparés. Tous les autres aspects sont identiques à la fonction avec un seul argument. | json_object('{a, b}', '{1,2}') | {"a": "1", "b": "2"} |
array_to_json et row_to_json ont le même comportement que to_json, en dehors du fait qu'elles ne proposent pas d'option d'affichage propre. Le comportement décrit pour to_json s'applique à chaque valeur individuelle convertie par les autres fonctions de création JSON.
L'extension hstore dispose d'une conversion du type hstore vers le type json, pour que les valeurs hstore converties via les fonctions de création JSON soient représentées en tant qu'objets JSON et non pas en tant que les valeurs des chaînes de caractères habituelles.
Tableau 9.43, « Fonctions de traitement du JSON » montre les fonctions disponibles pour le traitement des valeurs json et jsonb.
Tableau 9.43. Fonctions de traitement du JSON
| Fonction | Type renvoyé | Description | Exemple | Exemple de résultat | |
|---|---|---|---|---|---|
|
json_array_length(json) jsonb_array_length(jsonb) |
int | Renvoie le nombre d'éléments dans le tableau JSON externe. | json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]') | 5 | |
|
json_each(json) jsonb_each(jsonb) |
setof key text, value json setof key text, value jsonb |
SETOF key text, value json | Étend l'objet JSON extérieur en un ensemble de paires clé/valeur. | select * from json_each('{"a":"foo", "b":"bar"}') |
key | value
-----+-------
a | "foo"
b | "bar"
|
|
json_each_text(json) jsonb_each_text(jsonb) |
setof key text, value text | Étend l'objet JSON externe en un ensemble de paires clé/valeur. La valeur renvoyée est de type text. | select * from json_each_text('{"a":"foo", "b":"bar"}') |
key | value
-----+-------
a | foo
b | bar
|
|
|
json_extract_path(from_json json, VARIADIC path_elems text[]) jsonb_extract_path(from_json jsonb, VARIADIC path_elems text[]) |
json jsonb |
json | Renvoie l'objet JSON pointé par path_elems (équivalent à l'opérateur #>). | json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4') | {"f5":99,"f6":"foo"} |
|
json_extract_path_text(from_json json, VARIADIC path_elems text[]) jsonb_extract_path_text(from_json jsonb, VARIADIC path_elems text[]) |
text | Renvoie l'objet JSON pointé par path_elems as text (équivalent à l'opérateur #>>). | json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6') | foo | |
|
json_object_keys(json) jsonb_object_keys(jsonb) |
setof text | Renvoie l'ensemble de clés de l'objet externe JSON. | json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}') |
json_object_keys
------------------
f1
f2
|
|
|
json_populate_record(base anyelement, from_json json) jsonb_populate_record(base anyelement, from_json jsonb) |
anyelement | Étend l'objet dans from_json en une ligne dont les colonnes correspondent au type d'enregistrement défini par base (voir la note ci-dessous). | select * from json_populate_record(null::myrowtype, '{"a":1,"b":2}') |
a | b
---+---
1 | 2
|
|
|
json_populate_recordset(base anyelement, from_json json) jsonb_populate_recordset(base anyelement, from_json jsonb) |
setof anyelement | Étend le tableau externe d'objets dans from_json en un ensemble de lignes dont les colonnes correspondent au type d'enregistrement défini par base (voir la note ci-dessous). | select * from json_populate_recordset(null::myrowtype, '[{"a":1,"b":2},{"a":3,"b":4}]') |
a | b
---+---
1 | 2
3 | 4
|
|
|
json_array_elements(json) jsonb_array_elements(jsonb) |
setof json setof jsonb |
Étend un tableau JSON en un ensemble de valeurs JSON. | select * from json_array_elements('[1,true, [2,false]]') |
value
-----------
1
true
[2,false]
|
|
|
json_array_elements_text(json) jsonb_array_elements_text(jsonb) |
setof text | Étend un tableau JSON en un ensemble de valeurs text. | select * from json_array_elements_text('["foo", "bar"]') |
value
-----------
foo
bar
|
|
|
json_typeof(json) jsonb_typeof(jsonb) |
text | Renvoie le type de la valeur externe du JSON en tant que chaîne de type text. Les types possibles sont object, array, string, number, boolean et null. | json_typeof('-123.4') | number | |
|
json_to_record(json) jsonb_to_record(jsonb) |
record | Construit un enregistrement arbitraire à partir d'un objet JSON (voir la note ci-dessous). Comme avec toutes les fonctions renvoyant le type record, l'appelant doit définir explicitement la structure du type record avec une clause AS. | select * from json_to_record('{"a":1,"b":[1,2,3],"c":"bar"}') as x(a int, b text, d text) |
a | b | d
---+---------+---
1 | [1,2,3] |
|
|
|
json_to_recordset(json) jsonb_to_recordset(jsonb) |
setof record | Construit un ensemble arbitraire d'enregistrements à partir d'un tableau JSON d'objets (voir la note ci-dessous). Comme avec toutes les fonctions renvoyant le type record, l'appelant doit définir explicitement la structure du type record avec une clause AS. | select * from json_to_recordset('[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]') as x(a int, b text); |
a | b
---+-----
1 | foo
2 |
|
|
|
json_strip_nulls(from_json json) jsonb_strip_nulls(from_json jsonb) |
json jsonb |
Renvoie from_json en omettant tous les champs des objets qui ont des valeurs NULL. Les autres valeurs NULL ne sont pas omises. | json_strip_nulls('[{"f1":1,"f2":null},2,null,3]') | [{"f1":1},2,null,3] | |
|
jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean]) |
jsonb |
Renvoie target avec la section dont le chemin est désigné par path remplacée par new_value, ou avec new_value ajoutée si create_missing est true (ce qui est la valeur par défaut) et l'élément désigné par le chemin path n'existe pas. De la même manière qu'avec les opérateurs désignant des chemins, les nombres négatifs qui apparaissent dans path décomptent à partir de la fin des tableaux JSON. |
jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]', false) jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}','[2,3,4]') |
[{"f1":[2,3,4],"f2":null},2,null,3] [{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2] |
|
|
jsonb_pretty(from_json jsonb) |
text |
Renvoie from_json comme texte JSON indenté. | jsonb_pretty('[{"f1":1,"f2":null},2,null,3]') |
[
{
"f1": 1,
"f2": null
},
2,
null,
3
]
|
Un grand nombre de ces fonctions et opérateurs convertiront les échappements Unicode en chaînes JSON avec le caractère approprié. Ce n'est pas un problème si la valeur en entrée est de type jsonb parce que la conversion est déjà faite. Par contre, pour une valeur de type json, cela pourrait résulter par le renvoi d'une erreur, comme indiqué dans Section 8.14, « Types JSON ».
Bien que les exemples des fonctions json_populate_record, json_populate_recordset, json_to_record et json_to_recordset utilisent des constantes, l'utilisation typique sera de référencer une table dans la clause FROM et d'utiliser une de ces colonnes json ou jsonb comme argument de la fonction. Les valeurs extraites des clés peuvent ensuite être référencées dans d'autres parties de la requête, comme les clauses WHERE et les listes cibles. Extraire plusieurs valeurs de cette façon peut améliorer les performances par rapport à les extraire séparément avec les opérateurs par clé.
Les clés JSON keys sont vérifiées pour une correspondance avec des noms identiques de colonnes dans le type ligne cible. La coercion du type JSON pour ces requêtes est fait « au mieux » et pourrait résulter en des valeurs non désirées pour certains types. Les champs JSON qui n'apparaissent pas dans le type de ligne cible seront omis de la sortie, et les colonnes cibles qui ne correspondent pas à un champ JSON seront simplement NULL.
Tous les éléments du chemin du paramètre path de la fonction jsonb_set doivent être présents dans target, sauf si create_missing est true, auquel cas tous les éléments sauf le dernier doivent être présents. Si ces conditions ne sont pas satisfaites, target est renvoyé inchangé.
Si le dernier élément d'un chemin est la clef d'un objet, il sera créé avec la nouvelle valeur si absent. Si le dernier élément d'un chemin est l'index d'un tableau, si il est positif, l'élément à positionner est trouvé en comptant à partir de la gauche. Si il est négatif, le décompte se fait à partir de la droite (par exemple, -1 désigne l'élément le plus à droite, et ainsi de suite). Si l'élément est en dehors de l'intervalle existant -longueur_tableau .. longeur_tableau - 1, et create_missing est true, la nouvelle valeur est ajoutée au début du tableau pour un élément négatif, et à la fin du tableau pour un élément positif.
La valeur de retour null de la fonction json_typeof ne doit pas être confondue avec la valeur SQL NULL. Bien qu'appeler json_typeof('null'::json) renverra null, appeler json_typeof(NULL::json) renverra un NULL au sens SQL.
Si l'argument de json_strip_nulls contient des noms de champs dupliqués dans les objets, le résultat pourrait être sémantiquement quelque peu différent, dépendant de l'ordre dans lequel ils apparaissent. Ce n'est pas un problème pour jsonb_strip_nulls car les valeurs de type jsonb n'ont jamais des noms de champs dupliqués.
Voir aussi Section 9.20, « Fonctions d'agrégat » pour la fonction d'agrégat json_agg qui agrège les valeurs en tant que JSON, et la fonction d'agrégat json_object_agg qui agrège les paires de valeurs en un objet JSON, et leurs équivalents jsonb, jsonb_agg et jsonb_object_agg.
Fonctions XML