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

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

 key | value
-----+-------
 a   | "foo"
 b   | "bar"
           

 key | value
-----+-------
 a   | foo
 b   | bar
           

 json_object_keys
------------------
 f1
 f2
           

 a |   b       |      c
---+-----------+-------------
 1 | {2,"a b"} | (4,"a b c")
           

 a | b
---+---
 1 | 2
 3 | 4
           

   value
-----------
 1
 true
 [2,false]
           

   value
-----------
 foo
 bar
           

 a |    b    |    c    | d |       r
---+---------+---------+---+---------------
 1 | [1,2,3] | {1,2,3} |   | (123,"a b c")
           

 a |  b
---+-----
 1 | foo
 2 |
           

[
    {
        "f1": 1,
        "f2": null
    },
    2,
    null,
    3
]
           

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

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 :

'lax $.track.segments.location'
        

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

lax $.**.HR

strict $.**.HR

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

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