PostgreSQLLa base de données la plus sophistiquée au monde.
Documentation PostgreSQL 12.18 » Langage SQL » Fonctions et opérateurs » Fonctions et opérateurs JSON

9.15. Fonctions et opérateurs JSON

Cette section décrit :

  • les fonctions et opérateurs de traitement et création de données JSON ;

  • le langage de chemins SQL/JSON.

Pour en savoir plus sur le standard SQL/JSON, voir [sqltr-19075-6]. Pour les détails sur les types JSON supportés dans PostgreSQL, voir Section 8.14.

9.15.1. Traiter et créer des données JSON

Tableau 9.44 montre les opérateurs disponibles pour traiter des données de type JSON (voir Section 8.14).

Tableau 9.44. Opérateurs json et jsonb

OpérateurType de l'opérande droitType en retourDescriptionExempleRésultat de l'exemple
->intjson ou jsonbObtient l'élément du tableau JSON (indexé à partir de zéro, les nombres négatifs comptent à partir de la fin)'[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2{"c":"baz"}
->textjson ou jsonbObtient un champ objet JSON par sa clé'{"a": {"b":"foo"}}'::json->'a'{"b":"foo"}
->>inttextObtient un élément du tableau JSON sous le type text'[1,2,3]'::json->>23
->>texttextObtient un champ objet JSON en tant que text'{"a":1,"b":2}'::json->>'b'2
#>text[]json ou jsonbObtient l'objet JSON sur le chemin spécifié'{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}'{"c": "foo"}
#>>text[]textObtient un objet JSON sur le chemin spécifié en tant que text'{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'3

Note

Il existe des variantes parallélisées de ces opérateurs pour les types json et jsonb. Les opérateurs d'extraction champ/élément/chemin renvoient le même type que l'entrée côté gauche (soit json soit jsonb), sauf pour celles spécifiées comme renvoyant text, qui force la valeur dans le type text. Les opérateurs d'extraction champ/élément/chemin renvoient NULL, plutôt que d'échouer, si l'entrée JSON n'a pas la bonne structure correspondant à la requête ; par exemple, si aucun élément de ce type n'existe. Les opérateurs d'extraction champ/élément/chemin qui acceptent les indices de tableau JSON supportent tous les indices négatifs (pour commencer le décompte à la fin du tableau).

Les opérateurs de comparaison standards montrés dans Tableau 9.1 sont disponibles pour jsonb, mais pas pour json. Ils suivent les règles de tri pour les opérations B-tree soulignées sur Section 8.14.4. Voir aussi Section 9.20 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.

Certains opérateurs supplémentaires existent aussi seulement pour jsonb, comme indiqué dans Tableau 9.45. Un grand nombre de ces opérateurs peut être indexé avec les classes d'opérateur jsonb. Pour une description complète de la sémantique de contenance et d'existence jsonb, voir Section 8.14.3. Section 8.14.4 décrit comment ces opérateurs peuvent être utilisés pour indexer avec efficacité les données jsonb.

Tableau 9.45. Opérateurs jsonb supplémentaires

OpérateurType de l'opérande droitDescriptionExemple
@>jsonbEst-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
<@jsonbLes 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
?textEst-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'au moins une 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']
||jsonbEffectue la concaténation de deux valeurs de type jsonb dans une nouvelle valeur jsonb'["a", "b"]'::jsonb || '["c", "d"]'::jsonb
-textSupprime 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[]
-integerSupprime 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}'
@?jsonpathEst-ce que le chemin JSON renvoie des éléments de la valeur JSON indiquée ?'{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] ? (@ > 2)'
@@jsonpathRenvoie le résultat de la vérification du prédicat JSON pour la valeur JSON spécifiée. Seul le premier élément du résultat est pris en compte. Si le résultat n'est pas booléen, alors NULL est renvoyé.'{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] > 2'

Note

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

Note

Les opérateurs @? et @@ suppriment les erreurs suivantes : manque d'un champ d'objet ou d'un élément du tableau, type d'élément JSON inattendu, et erreurs numériques. Ce comportement peut se révéler utile lors de la recherche dans une collection de documents JSON de différentes structures.

Tableau 9.46 montre les fonctions disponibles pour la création de valeurs json et jsonb. (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.46. Fonctions de création de données JSON

FonctionDescriptionExempleExemple 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]) 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"}

json_build_array(VARIADIC "any")

jsonb_build_array(VARIADIC "any")

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}

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 pris 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"}

Note

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.

Note

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.47 montre les fonctions disponibles pour le traitement des valeurs json et jsonb.

Tableau 9.47. Fonctions de traitement du JSON

FonctionType renvoyéDescriptionExempleExemple 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

É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

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"], "c": {"d": 4, "e": "a b c"}}')
 a |   b       |      c
---+-----------+-------------
 1 | {2,"a b"} | (4,"a b c")
           

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":[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")
           

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_insert(target jsonb, path text[], new_value jsonb [, insert_after boolean])

jsonb

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.

jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"nouvelle_valeur"')

jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"nouvelle_valeur"', true)

{"a": [0, "nouvelle_valeur", 1, 2]}

{"a": [0, 1, "nouvelle_valeur", 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
]
           

jsonb_path_match(target jsonb, path jsonpath [, vars jsonb [, silent bool]])

boolean Vérifie si le chemin JSON renvoie un élément à partir de la valeur JSON spécifiée.

jsonb_path_exists('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2,"max":4}')

true

jsonb_path_match(target jsonb, path jsonpath [, vars jsonb, silent bool])

boolean Renvoie le résultat de la vérification du prédicat du chemin JSON pour la valeur JSON spécifiée. Seul le premier élément du résultat est pris en compte. Si le résultat n'est pas booléen, alors NULL est renvoyée.

jsonb_path_match('{"a":[1,2,3,4,5]}', 'exists($.a[*] ? (@ >= $min && @ <= $max))', '{"min":2,"max":4}')

true

jsonb_path_query(target jsonb, path jsonpath [, vars jsonb [, silent bool]])

setof jsonb Obtient tous les éléments JSON renvoyés par le chemin JSON pour la valeur JSON spécifiée.

select * from jsonb_path_query('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2,"max":4}');

 jsonb_path_query
------------------
 2
 3
 4
            

jsonb_path_query_array(target jsonb, path jsonpath [, vars jsonb [, silent bool]])

jsonb Obtient tous les éléments JSON renvoyés par le chemin JSON pour la valeur JSON spécifiée, et intègre le résultat dans un tableau.

jsonb_path_query_array('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2,"max":4}')

[2, 3, 4]

jsonb_path_query_first(target jsonb, path jsonpath [, vars jsonb [, silent bool]])

jsonb Obtient le premier élément JSON renvoyé par le chemin JSON pour la valeur JSON spécifiée. Renvoie NULL si aucun résultat.

jsonb_path_query_first('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2,"max":4}')

2


Note

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.

Note

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 colonnes 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'objet 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é à 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é.

Note

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.

Note

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.

Note

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.

Note

Les fonctions jsonb_path_exists, jsonb_path_match, jsonb_path_query, jsonb_path_query_array et jsonb_path_query_first ont des arguments vars et silent.

Si l'argument vars est indiqué, il fournit un objet contenant des variables nommées à substituer dans une expression jsonpath.

Si l'argument silent est indiqué et a la valeur true, ces fonctions suppriment les mêmes erreurs que les opérateurs @? et @@.

9.15.2. Langage de chemins SQL/JSON

Les expressions de chemin SQL/JSON indiquent les éléments à récupérer à partir de données JSON, un peu similaire aux expressions XPath utilisées pour l'accès SQL au XML. Dans PostgreSQL, les expressions de chemin sont implémentées comme le type de données jsonpath et peuvent utiliser tout élément décrit dans Section 8.14.6.

Les fonctions et opérateurs de requêtes JSON acceptent l'expression de chemin fourni au moteur de chemin pour son évaluation. Si l'expression correspond à la donnée JSON à requêter, l'élément SQL/JSON correspondant est renvoyé. Les expressions de chemins sont écrites dans le langage de chemin SQL/JSON et peuvent aussi inclure des expressions et des fonctions arithmétiques. Les fonctions de requête traitent l'expression fournie comme une chaîne de texte, donc elle doit être entourée de guillemets simples.

Une expression de chemin consiste en une séquence d'éléments autorisés par le type de données jsonpath. L'expression de chemin est évaluée de la gauche à la droite, mais vous pouvez utiliser des parenthèses pour modifier l'ordre des opérations. Si l'évaluation réussit, une séquence d'éléments SQL/JSON (SQL/JSON sequence) est produite, et le résultat de l'évaluation est renvoyé à la fonction de requête JSON qui termine les calculs demandés.

Pour faire référence à la donnée JSON en cours de requêtage (l'élément de contexte), utilisez le signe $ dans l'expression de chemin. Il peut être suivi d'un ou plusieurs opérateurs d'accès, qui descendent dans la structure JSON, niveau par niveau, pour récupérer le contenu de l'élément de contexte. Chaque opérateur qui suit gère le résultat de l'étape précédente d'évaluation.

Par exemple, supposons que vous ayez des données JSON provenant d'un traceur GPS que vous voulez analyser, par exemple :

{
  "track": {
    "segments": [
      {
        "location":   [ 47.763, 13.4034 ],
        "start time": "2018-10-14 10:05:14",
        "HR": 73
      },
      {
        "location":   [ 47.706, 13.2635 ],
        "start time": "2018-10-14 10:39:21",
        "HR": 135
      }
    ]
  }
}
       

Pour récupérer les segments de trace disponibles, vous devez utiliser l'opérateur d'accès .clé pour tous les objets JSON précédents :

'$.track.segments'
       

Si l'élément à récupérer est un élément d'un tableau, vous devez déballer ce tableau en utilisant l'opérateur [*]. Par exemple, le chemin suivant renvoie les coordonnées de tous les segments de trace disponibles :

'$.track.segments[*].location'
       

Pour renvoyer uniquement les coordonnées du premier segment, vous pouvez indiquer l'indice correspondant dans l'opérateur d'accès []. Notez que les indices des tableaux SQL/JSON commencent à 0 :

'$.track.segments[0].location'
       

Le résultat de chaque étape d'évaluation du chemin peut être traité par un ou plusieurs opérateurs ou méthodes jsonpath, listés dans Section 9.15.2.3. Chaque nom de méthode doit être précédé d'un point. Par exemple, vous pouvez obtenir la taille d'un tableau :

'$.track.segments.size()'
       

Pour plus d'exemples d'utilisation des opérateurs et méthodes jsonpath avec des expressions de chemin, voir Section 9.15.2.3.

Lors de la définition du chemin, vous pouvez aussi utiliser une ou plusieurs expressions de filtres qui fonctionnent de façon similaire à la clause WHERE en SQL. Une expression de filtre commence avec un point d'interrogation et fournit une condition entre parenthèses :

? (condition)
       

Les expressions de filtre doivent être indiquées tout de suite après l'étape d'évaluation du chemin auquel elles sont appliquées. Le résultat de cette étape est filtré pour inclure seulement les éléments qui satisfont la condition fournie. SQL/JSON définit une logique à trois valeurs, donc la condition peut être true, false ou unknown. La valeur unknown joue le même rôle que le NULL en SQL et peut être testée avec le prédicat is unknown. Les étapes suivantes d'évaluation du chemin utilisent seulement les éléments pour lesquels les expressions de filtre renvoient true.

Les fonctions et opérateurs pouvant être utilisés dans des expressions de filtre sont listés dans Tableau 9.49. Le résultat de l'évaluation du chemin à filtrer est dénoté par la variable @. Pour faire référence à un élément JSON enregistré à un niveau inférieur, ajouter un ou plusieurs opérateurs d'accès après @.

Supposez que vous vouliez récupérer toutes les valeurs de fréquence cardiaque supérieures à 130. Vous pouvez le faire en utilisant l'expression suivante :

'$.track.segments[*].HR ? (@ > 130)'
       

Pour récupérer l'heure de début des segments pour ces valeurs, vous devez filtrer les segments inintéressants avant de renvoyer l'heure de début, donc l'expression de filtre est appliquée à l'étape précédente, et le chemin utilisé dans la condition est différent :

'$.track.segments[*] ? (@.HR > 130)."start time"'
       

Vous pouvez utiliser plusieurs expressions de filtre sur le même niveau, si nécessaire. Par exemple, l'expression suivante sélectionne tous les segments contenant les emplacements aux coordonnées adéquates avec les bonnes valeurs de fréquence cardiaque :

'$.track.segments[*] ? (@.location[1] < 13.4) ? (@.HR > 130)."start time"'
       

L'utilisation d'expressions de filtre à des niveaux différents est aussi autorisée. L'exemple suivant commence par filtrer tous les segments par emplacement, et renvoie les valeurs hautes de fréquence cardiaque pour tous ces segments, si disponible :

'$.track.segments[*] ? (@.location[1] < 13.4).HR ? (@ > 130)'
       

Vous pouvez aussi imbriquer des expressions de filtre :

'$.track ? (exists(@.segments[*] ? (@.HR > 130))).segments.size()'
       

Cette expression renvoie la taille de la trace si elle contient des segments avec des valeurs hautes de fréquence cardiaque. Dans le cas contraire, elle renvoie une séquence vide.

L'implémentation PostgreSQL du langage de chemin SQL/JSON propose les déviations suivantes du standard SQL/JSON :

  • La méthode d'élément .datetime() n'est pas encore implémentée, principalement parce que les fonctions et opérateurs immuables jsonpath ne peuvent pas référencer le fuseau horaire de la session, fuseau qui est utilisé dans certaines opérations datetime. Le support de datetime sera ajouté à jsonpath dans les prochaines versions de PostgreSQL.

  • Une expression de chemin peut être un prédicat booléen, bien que le standard SQL/JSON autorise les prédicats seulement dans les filtres. Ceci est nécessaire pour l'implémentation de l'opérateur @@. Par exemple, l'expression jsonpath suivante est valide dans PostgreSQL :

    '$.track.segments[*].HR < 70'
             

  • Il existe des différences mineures dans l'interprétation des motifs d'expression rationnelle utilisés dans les filtres like_regex, comme décrit dans Section 9.15.2.2.

9.15.2.1. Modes strict et laxiste

Quand vous requêtez des données JSON, les expressions de chemin pourraient ne pas correspondre à la structure de données JSON. Une tentative d'accès à un membre inexistant d'un objet ou d'un élément d'un tableau résulte en une erreur structurelle. Les expressions de chemin SQL/JSON ont deux modes de gestion des erreurs structurelles :

  • laxiste (par défaut) -- le moteur de chemin adapte implicitement les données requêtées sur le chemin spécifié. Toutes les erreurs structurelles restantes sont supprimées et converties en des séquences SQL/JSON vides.

  • strict -- si une erreur structurelle survient, une erreur est renvoyée.

Le mode laxiste facilite la correspondance entre une structure de document JSON et une expression de chemin si les données JSON ne se conforment pas au schéma attendu. Si un opérande ne correspond pas aux prérequis d'une opération particulière, il peut être automatiquement englobé dans un tableau SQL/JSON ou déballé en convertissant ses éléments en une séquence SQL/JSON avant de réaliser cette opération. De plus, les opérateurs de comparaison déroulent automatiquement leurs opérandes dans le mode laxiste, donc vous pouvez comparer directement les tableaux SQL/JSON. Un tableau de taille 1 est considéré comme égal à son seul élément. Ce déroulage automatique n'est pas réalisé seulement si :

  • L'expression du chemin contient les méthodes type() ou size() qui renvoient respectivement le type et le nombre d'éléments dans le tableau.

  • Les données JSON requêtées contiennent des tableaux imbriqués. Dans ce cas, seul le tableau externe est déballé alors que tous les tableaux internes restent inchangés. De ce fait, un déballage implicite peut seulement accéder au premier niveau pour chaque étape de l'évaluation du chemin.

Par exemple, lors du requêtage des données GPS listées ci-dessus, vous pouvez faire l'abstraction du fait qu'il enregistre un tableau de segments en utilisant le mode laxiste :

'lax $.track.segments.location'
        

Dans le mode strict, le chemin spécifié doit correspondre exactement à la structure du document JSON traité pour renvoyer un élément SQL/JSON, donc utiliser cette expression de chemin causera une erreur. Pour obtenir le même résultat que dans le mode laxiste, vous devez déballer explicitement le tableau de segments :

'strict $.track.segments[*].location'
        

L'accesseur .** peut apporter des résultats surprenants lors de l'utilisation du moe non strict. Par exemple, la requête suivante sélectionne chaque valeur HR deux fois :

lax $.**.HR

Ceci survient parce que l'accesseur .** sélectionne à la fois le tableau de segments et chacun de ses éléments, alors que l'accesseur .HR déballe automatiquement les tableaux lors de l'utilisation du mode non strict. Pour éviter des résultats surprenants, nous recommendons d'utiliser l'accesseur .** uniquement dans le mode strict. la requête suivant sélectionne chaque valeur HR une seule fois :

strict $.**.HR

9.15.2.2. Expressions rationnelles

Les expressions de chemin SQL/JSON autorisent la correspondance de texte sur une expression rationnelle avec le filtre like_regex. Par exemple, la requête de chemin SQL/JSON suivante pourrait correspondre à toutes les chaînes d'un tableau commençant avec une voyelle anglaise, sans casse spécifique :

'$[*] ? (@ like_regex "^[aeiou]" flag "i")'
        

La chaîne flag optionnelle pourrait inclure un ou plusieurs caractères comme i pour les correspondances quelle que soit la casse, m pour autoriser ^ et $ à correspondre aux retours à la ligne, s pour permettre à . de correspondre à une nouvelle ligne, et q pour correspondre au motif complet (réduisant le comportement à une simple correspondance de sous-chaîne).

Le standard SQL/JSON emprunte sa définition pour les expressions rationnelles à l'opérateur LIKE_REGEX, qui lui-même utilise le standard XQuery. PostgreSQL n'accepte pas actuellement l'opérateur LIKE_REGEX. De ce fait, le filtre like_regex est implémenté en utilisant le moteur d'expression rationnelle POSIX décrit dans Section 9.7.3. Ceci amène à plusieurs différences mineures avec le comportement du SQL/JSON standard, cataloguées dans Section 9.7.3.8. Néanmoins, notez que les incompatibilités sur les lettres drapeaux décrites ici ne s'appliquent pas au SQL/JSON, car il traduit les lettres de drapeau XQuery pour correspondre à ce que le moteur POSIX attend.

Conservez à l'esprit que l'argument du motif de like_regex est une constante de type chaîne du chemin JSON, écrite suivant les règles données dans Section 8.14.6. Ceci signifie en particulier que tout antislash que vous voulez utiliser dans l'expression rationnelle sera doublé. Par exemple, pour établir une correspondance avec les valeurs du document racine contenant seulement des chiffres :

$.* ? (@ like_regex "^\\d+$")
        

9.15.2.3. Opérateurs et méthodes pour les chemins SQL/JSON

Tableau 9.48 montre les opérateurs et méthodes disponibles dans jsonpath. Tableau 9.49 montre les éléments d'expression de filtre disponibles.

Tableau 9.48. Opérateurs et méthodes sur jsonpath

Opérateur/MéthodeDescriptionExemple JSONExemple SQLRésultat
+ (unary)Opérateur plus qui itère sur la séquence SQL/JSON{"x": [2.85, -14.7, -9.4]}+ $.x.floor()2, -15, -10
- (unary)Opérateur moins qui itère sur la séquence SQL/JSON{"x": [2.85, -14.7, -9.4]}- $.x.floor()-2, 15, 10
+ (binary)Addition[2]2 + $[0]4
- (binary)Soustraction[2]4 - $[0]2
*Multiplication[4]2 * $[0]8
/Division[8]$[0] / 24
%Module[32]$[0] % 102
type()Type de l'élément SQL/JSON[1, "2", {}]$[*].type()"number", "string", "object"
size()Taille de l'élément SQL/JSON{"m": [11, 15]}$.m.size()2
double()Nombre flottant approximatif converti à partir d'un nombre SQL/JSON ou d'une chaîne{"len": "1.9"}$.len.double() * 23.8
ceiling()Entier le plus proche plus grand ou égal au nombre SQL/JSON{"h": 1.3}$.h.ceiling()2
floor()Entier le plus proche plus petit ou égal au nombre SQL/JSON{"h": 1.3}$.h.floor()1
abs()Valeur absolue du nombre SQL/JSON{"z": -0.3}$.z.abs()0.3
keyvalue() Séquence de paires clé/valeur de l'objet, représentée sous la forme d'un tableau d'éléments contenant trois champs ("key", "value" et "id"). "id" est un identifiant unique d'appartenance à la paire clé/valeur de l'objet. {"x": "20", "y": 32}$.keyvalue(){"key": "x", "value": "20", "id": 0}, {"key": "y", "value": 32, "id": 0}

Tableau 9.49. Éléments d'expression de filtre jsonpath

Valeur/PrédicatDescriptionExemple JSONExemple QueryRésultat
==Opérateur d'égalité[1, 2, 1, 3]$[*] ? (@ == 1)1, 1
!=Opérateur de différence[1, 2, 1, 3]$[*] ? (@ != 1)2, 3
<>Opérateur de différence (identique à !=)[1, 2, 1, 3]$[*] ? (@ <> 1)2, 3
<Opérateur plus-petit-que[1, 2, 3]$[*] ? (@ < 2)1
<=Opérateur plus-petit-ou-égal[1, 2, 3]$[*] ? (@ <= 2)1, 2
>Opérateur plus-grand-que[1, 2, 3]$[*] ? (@ > 2)3
>=Opérateur plus-petit-ou-égal[1, 2, 3]$[*] ? (@ >= 2)2, 3
trueValeur utilisée pour réaliser des comparaisons avec la constante JSON true[{"name": "John", "parent": false}, {"name": "Chris", "parent": true}]$[*] ? (@.parent == true){"name": "Chris", "parent": true}
falseValeur utilisée pour réaliser des comparaisons avec la constante JSON false[{"name": "John", "parent": false}, {"name": "Chris", "parent": true}]$[*] ? (@.parent == false){"name": "John", "parent": false}
nullValeur utilisée pour réaliser des comparaisons avec la constante JSON null[{"name": "Mary", "job": null}, {"name": "Michael", "job": "driver"}]$[*] ? (@.job == null) .name"Mary"
&&AND booléen[1, 3, 7]$[*] ? (@ > 1 && @ < 5)3
||OR booléen[1, 3, 7]$[*] ? (@ < 1 || @ > 5)7
!NOT booléen[1, 3, 7]$[*] ? (!(@ < 5))7
like_regex Teste si le premier opérande correspond à l'expression rationnelle donnée dans le second opérande, optionnellement avec les modifications décrites par une chaîne de caractères flag (voir Section 9.15.2.2) ["abc", "abd", "aBdC", "abdacb", "babc"]$[*] ? (@ like_regex "^ab.*c" flag "i")"abc", "aBdC", "abdacb"
starts withTeste si le second opérande est une sous-chaîne initiale du premier opérande["John Smith", "Mary Stone", "Bob Johnson"]$[*] ? (@ starts with "John")"John Smith"
existsTeste si une expression de chemin correspond à au moins un élément SQL/JSON{"x": [1, 2], "y": [2, 4]}strict $.* ? (exists (@ ? (@[*] > 2)))2, 4
is unknownTeste si la condition booléenne est unknown[-1, 2, 7, "infinity"]$[*] ? ((@ > 0) is unknown)"infinity"