Tableau 9.43 montre les opérateurs disponibles avec des données des deux types JSON (voir Section 8.14).
Tableau 9.43. 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 |
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 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. Voir aussi Section 9.20
pour la fonction d'agrégat json_agg
qui agrège les
valeurs d'enregistrement sous la forme d'un document JSON, la fonction
d'agrégat json_object_agg
qui agrège les paires de
valeurs dans un objet JSON, et leurs équivalents jsonb
, à savoir
jsonb_agg
et jsonb_object_agg
.
Des opérateurs supplémentaires existent seulement pour le type
jsonb
, comme indiqué dans Tableau 9.44. 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. Section 8.14.4 décrit
comment ces opérateurs peuvent être utilisés pour indexer efficacement les
données de type jsonb
.
Tableau 9.44. 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' |
- | text[] | Supprime plusieurs paires de clé/valeur ou d'éléments string de l'opérande de gauche. La correspondance des paires de clé/valeur est faite en fonction de la valeur de leur clé. | '{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[] |
- | 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.45 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.45. Fonctions de création de données JSON
Fonction | Description | Exemple | Exemple du résultat |
---|---|---|---|
|
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])
|
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])
|
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"} |
| 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")
| 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} |
| 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. |
| {"a": "1", "b": "def", "c": "3.5"} |
|
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.46 montre les fonctions
disponibles pour le traitement des valeurs json
et
jsonb
.
Tableau 9.46. Fonctions de traitement du JSON
Fonction | Type renvoyé | Description | Exemple | Exemple de résultat |
---|---|---|---|---|
| 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 |
|
| É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" |
| 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 |
|
|
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"} |
| 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 |
| 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 |
| 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"], "c": {"d": 4, "e": "a b c"}}') |
a | b | c ---+-----------+------------- 1 | {2,"a b"} | (4,"a b c") |
| 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 |
|
| Étend un tableau JSON en un ensemble de valeurs JSON. | select * from json_array_elements('[1,true, [2,false]]') |
value ----------- 1 true [2,false] |
| setof text |
Étend un tableau JSON en un ensemble de valeurs text .
| select * from json_array_elements_text('["foo", "bar"]') |
value ----------- foo bar |
| 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 |
| 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":[1,2,3],"e":"bar","r": {"a": 123, "b": "a b c"}}') as x(a int, b text, c int[], d text, r myrowtype) |
a | b | c | d | r ---+---------+---------+---+--------------- 1 | [1,2,3] | {1,2,3} | | (123,"a b c") |
| 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 | |
|
|
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] |
|
|
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.
|
|
|
|
|
Renvoie target avec
new_value insérée. Si la section
target désignée par
path est dans un tableau JSONB,
new_value sera insérée avant la cible ou
après la cible si insert_after vaut true
(la valeur par défaut est false ). Si la section
target désignée par
path est dans un objet JSONB,
new_value sera insérée seulement si
target n'existe pas. Tout comme avec les
opérateurs orientés chemin, les entiers négatifs qui apparaissent
dans path sont décomptés à partir de la
fin des tableaux JSON.
|
|
|
|
|
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.
Les fonctions json[b]_populate_record
,
json[b]_populate_recordset
,
json[b]_to_record
et
json[b]_to_recordset
opèrent sur un objet
JSON ou un tableau d'objets, et extraient les valeurs associées
aux clés dont le nom correspond au nom des clonnes dans le type
de ligne en sortie. Les champs de l'objet qui ne correspondent
pas à un nom de colonne en sortie sont ignorés, et les colonnes
en sortie qui ne correspondent pas à un champ de l'objet seront à
NULL. Pour convertir une valeur JSON vers le type SQL d'une
colonne en sortie, les règles suivantes sont appliquées
séquentiellement :
Une valeur JSON null est convertie en un NULL SQL dans tous les cas.
Si la colonne en sortie est de type json
ou
jsonb
, la valeur JSON est reproduite exactement.
Si la colonne en sortie est de type composite (ligne), et que la valeur JSON est un objet JSON, les champs de l'objets sont convertis en colonnes du type de ligne en sortie par application récursive de ces règles.
De la même façon, si la colonne en sortie est un type tableau et que la valeur JSON est un tableau JSON, les éléments du tableau JSON sont convertis en éléments du tableau en sortie par application récursive de ces règles.
Sinon, si la valeur JSON est une chaîne constante, le contenu de la chaîne est envoyée à la fonction de conversion en entrée pour le type de données de la colonne.
Sinon, la représentation textuelle de la valeur JSON est envoyée à la fonction de conversion en entrée pour le type de données de la colonne.
Bien que les exemples pour ces fonctions utilisent des
constantes, l'utilisation typique est de référencer une table
dans la clause FROM
et d'utiliser une de ses
colonnes json
ou jsonb
comme argument
de la fonction. Les valeurs clés extraites 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
sur leur extraction séparée avec des opérateurs par clé.
Tous les éléments du chemin du paramètre path
des fonctions jsonb_set
et
jsonb_insert
, sauf le dernier élément, doivent
être présents dans la target
. Si
create_missing
vaut false, tous les éléments
du paramètre path
de
jsonb_set
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.