Après le
comité de 2019-03 , le gel des fonctionnalités a eu lieu. Nous avons ici une chronique presque traditionnelle: nous avons
déjà écrit sur le gel de l’année dernière. Maintenant, les résultats de 2019: lesquels des nouveaux seront inclus dans PostgreSQL 12. Dans cette partie de la revue JSONPath, des exemples et des fragments du rapport "Postgres 12 in Etudes", qu'Oleg Bartunov a lu à
Saint Highload ++ à Saint-Pétersbourg le 9 avril de cette année, sont utilisés.
Jsonpath
Tout ce qui concerne JSON (B) est pertinent, en demande dans le monde, en Russie, et c'est l'un des domaines de développement les plus importants de Postgres Professional. Le type jsonb, les fonctions et les opérateurs pour travailler avec JSON / JSONB sont apparus dans PostgreSQL version 9.4, ils ont été créés par une équipe dirigée par Oleg Bartunov.
La norme SQL / 2016 prévoit de travailler avec JSON: JSONPath y est mentionné - un ensemble d'outils d'adressage de données à l'intérieur de JSON; JSONTABLE - moyen de convertir JSON en tables régulières; Une grande famille de fonctions et d'opérateurs. Malgré le fait que JSON à Postgres soit soutenu depuis longtemps, en 2017 Oleg Bartunov et ses collègues ont commencé à travailler sur le soutien de la norme. Se conformer à la norme est toujours bon. De tout ce qui est décrit dans la norme, un seul mais le correctif le plus important est JSONPath dans la version 12, nous en parlerons donc en premier lieu.
Dans les temps anciens, les gens utilisaient JSON, le stockant dans des champs de texte. Dans 9.3, un type de données spécial pour JSON est apparu, mais la fonctionnalité qui lui était associée n'était pas riche, et les demandes avec ce type fonctionnaient lentement en raison du temps passé à analyser la représentation textuelle de JSON. Cela a empêché de nombreux utilisateurs Postgres potentiels qui préféraient les bases de données NoSQL. La productivité de Postgres a augmenté à 9,4 lorsque, grâce à O. Bartunov, A. Korotkov et F. Sigaev, Postgres a introduit une version binaire de JSON - le type jsonb.
jsonb n'a pas besoin d'être analysé à chaque fois, donc travailler avec lui est beaucoup plus rapide. Parmi les nouvelles fonctions et opérateurs qui sont apparus en même temps, certains ne fonctionnent qu'avec un nouveau type binaire, tel que l'opérateur important d'occurrence
@> , qui vérifie si un élément ou un tableau est inclus dans un JSONB donné:
SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb;
donne VRAI, puisque le tableau de droite entre dans le tableau de gauche. Mais
SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb;
donnera FAUX, puisque le niveau d'imbrication est différent, il doit être défini explicitement. Un opérateur d'existence est-il introduit pour le type jsonb
? (un point d'interrogation) qui vérifie si une chaîne est une clé d'objet ou un élément d'un tableau au niveau supérieur des valeurs JSONB, ainsi que deux autres opérateurs similaires (détails
ici ). Ils sont pris en charge par les index GIN avec deux classes d'opérateurs GIN. L'opérateur
-> (flèche) vous permet de "naviguer" dans JSONB, il retourne une valeur par clé ou, s'il s'agit d'un tableau, par index. Il
existe plusieurs autres opérateurs pour se déplacer. Mais il n'y a aucun moyen d'organiser des filtres qui fonctionnent de manière similaire à WHERE. C'était une percée: grâce à jsonb, Postgres a commencé à gagner en popularité en tant que SGBDR avec des fonctionnalités NoSQL.
En 2014, A. Korotkov, O. Bartunov et F. Sigaev ont développé l'extension jsquery, qui a été incluse à la suite dans Postgres Pro Standard 9.5 (et dans les versions ultérieures de Standard et Enterprise). Il fournit des fonctionnalités supplémentaires très larges pour travailler avec json (b). Cette extension définit le langage de requête pour extraire les données de json (b) et les index pour accélérer ces requêtes. Cette fonctionnalité était requise par les utilisateurs, ils n'étaient pas prêts à attendre la norme et l'inclusion de nouvelles fonctionnalités dans la version vanille. Le fait que le développement ait été parrainé par Wargaming.net témoigne de la valeur pratique. L'extension implémente un type spécial - jsquery.
Une requête dans ce langage est compacte et ressemble, par exemple, à ceci:
SELECT '{"apt":[{"no": 1, "rooms":2}, {"no": 2, "rooms":3}, {"no": 3, "rooms":2}]}'::jsonb @@ 'apt.#.rooms=3'::jsquery;
Nous demandons ici s'il y a «trois roubles» dans l'immeuble. Le type jsquery doit être spécifié car l'opérateur @@ est désormais également dans le type jsonb. La description est
ici , et la présentation avec de nombreux exemples est
ici .
Total: Postgres avait déjà tout pour travailler avec JSON, puis la norme SQL: 2016 est apparue. Il s'est avéré que sa sémantique n'est pas si différente de la nôtre dans l'extension jsquery. Il est possible que les auteurs de la norme aient jeté un coup d'œil à jsquery, inventant JSONPath. Notre équipe a dû mettre en œuvre un peu différemment ce que nous avions déjà et, bien sûr, beaucoup de nouvelles choses aussi.
Il y a plus d'un an, lors du commitfest de mars, les fruits de nos efforts de programmation ont été offerts à la communauté sous forme de 3 gros correctifs avec prise en charge de la
norme SQL: 2016 :
SQL / JSON: JSONPath;
SQL / JSON: fonctions;
SQL / JSON: JSON_TABLE.
Mais développer un correctif n'est pas l'affaire de tous, leur promotion n'est pas non plus facile, surtout si les correctifs sont volumineux et affectent de nombreux modules. De nombreuses itérations de révision de révision sont nécessaires, le patch doit être promu, comme le font les sociétés commerciales, en investissant beaucoup de ressources (heures / homme). L'architecte en chef de Postgres Professional, Alexander Korotkov, l'a pris sur lui (puisqu'il a maintenant le statut de committer) et a obtenu l'adoption du patch JSONPath - le principal de cette série de patchs. Les deuxième et troisième sont maintenant au stade de l'examen des besoins. Le JSONPath ciblé vous permet de travailler avec la structure JSON (B) et est suffisamment flexible pour mettre en évidence ses fragments. Sur les 15 points prescrits dans la norme, 14 sont mis en œuvre, ce qui est plus que dans Oracle, MySQL et MS SQL.
La notation JSONPath diffère des instructions Postgres pour l'utilisation de la notation JSON et JSQuery. La hiérarchie est indiquée par des points:
$ .abc (en notation postgres 11, je devrais écrire 'a' -> 'b' -> 'c');
$ - le contexte actuel de l'élément - en fait, l'expression avec $ définit la région json (b) à traiter, y compris celle du filtre, le reste n'est pas disponible pour le travail;
@ - le contexte actuel dans l'expression de filtre - itère sur les chemins disponibles dans l'expression avec $;
[*] - un tableau;
* - caractère générique, dans l'expression avec $ ou @ signifie n'importe quelle valeur du segment de chemin, mais en tenant compte de la hiérarchie;
** - dans le cadre de l'expression avec $ ou @ peut signifier n'importe quelle valeur du segment de chemin sans tenir compte de la hiérarchie - il est pratique de l'utiliser si vous ne connaissez pas le niveau d'imbrication des éléments;
opérateur "?" vous permet d'organiser un filtre similaire à OERE:
$ .abc? (@ .x> 10);
$ .abcxtype (), ainsi que size (), double (), plafond (), floor (), abs (), datetime (), keyvalue () sont des méthodes.
Une requête avec la fonction jsonb_path_query (sur les fonctions ci-dessous) pourrait ressembler à ceci:
SELECT jsonb_path_query_array('[1,2,3,4,5]', '$[*] ? (@ > 3)'); jsonb_path_query_array
Bien qu'un patch spécial avec des fonctions ne soit pas validé, le patch JSONPath a déjà des fonctions clés pour travailler avec JSON (B):
jsonb_path_exists('{"a": 1}', '$.a') true ( "?") jsonb_path_exists('{"a": 1}', '$.b') false jsonb_path_match('{"a": 1}', '$.a == 1') true ( "@>") jsonb_path_match('{"a": 1}', '$.a >= 2') false jsonb_path_query('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 2)') 3, 4, 5 jsonb_path_query('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 5)') 0 jsonb_path_query_array('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 2)') [3, 4, 5] jsonb_path_query_array('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 5)') [] jsonb_path_query_first('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 2)') 3 jsonb_path_query_first('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 5)') NULL
Notez que l'égalité dans les expressions JSONPath est un simple "=", tandis que dans jsquery, elle est double: "==".
Pour des illustrations plus élégantes, nous allons générer JSONB dans une plaque de maison à colonne unique:
CREATE TABLE house(js jsonb); INSERT INTO house VALUES ('{ "address": { "city":"Moscow", "street": "Ulyanova, 7A" }, "lift": false, "floor": [ { "level": 1, "apt": [ {"no": 1, "area": 40, "rooms": 1}, {"no": 2, "area": 80, "rooms": 3}, {"no": 3, "area": 50, "rooms": 2} ] }, { "level": 2, "apt": [ {"no": 4, "area": 100, "rooms": 3}, {"no": 5, "area": 60, "rooms": 2} ] } ] }');
Fig. 1 Arbre JSON de logement avec appartements à feuilles attribuées.C'est un JSON étrange: il a une hiérarchie confuse, mais il est tiré de la vie, et dans la vie, il est souvent nécessaire de travailler avec ce qui est et non avec ce qui devrait être. Armés des capacités de la nouvelle version, nous trouverons des appartements au 1er et au 2ème étage, mais pas le premier dans la liste des appartements de l'étage (sur l'arborescence ils sont surlignés en vert):
SELECT jsonb_path_query_array(js, '$.floor[0, 1].apt[1 to last]') FROM house;
Dans PostgreSQL 11, vous devez demander ceci:
SELECT jsonb_agg(apt) FROM ( SELECT apt->generate_series(1, jsonb_array_length(apt) - 1) FROM ( SELECT js->'floor'->unnest(array[0, 1])->'apt' FROM house ) apts(apt) ) apts(apt);
Maintenant, une question très simple: y a-t-il des lignes contenant (n'importe où) la valeur "Moscou"? Vraiment simple:
SELECT jsonb_path_exists(js, '$.** ? (@ == "Moscow")') FROM house;
Dans la version 11, vous devrez écrire un énorme script:
WITH RECURSIVE t(value) AS ( SELECT * FROM house UNION ALL ( SELECT COALESCE(kv.value, e.value) AS value FROM t LEFT JOIN LATERAL jsonb_each ( CASE WHEN jsonb_typeof(t.value) = 'object' THEN t.value ELSE NULL END ) kv ON true LEFT JOIN LATERAL jsonb_array_elements ( CASE WHEN jsonb_typeof(t.value) = 'array' THEN t.value ELSE NULL END ) e ON true WHERE kv.value IS NOT NULL OR e.value IS NOT NULL ) ) SELECT EXISTS (SELECT 1 FROM t WHERE value = '"Moscow"');
Fig. 2 Arbre du logement JSON, Moscou a été trouvé!Nous recherchons n'importe quel appartement à n'importe quel étage d'une superficie de 40 à 90 m²:
select jsonb_path_query(js, '$.floor[*].apt[*] ? (@.area > 40 && @.area < 90)') FROM house; jsonb_path_query ----------------------------------- {"no": 2, "area": 80, "rooms": 3} {"no": 3, "area": 50, "rooms": 2} {"no": 5, "area": 60, "rooms": 2} (3 rows)
Nous recherchons des appartements avec chambres après le 3, en utilisant notre logement jason:
SELECT jsonb_path_query(js, '$.floor.apt.no ? (@>3)') FROM house; jsonb_path_query
Et voici comment fonctionne jsonb_path_query_first:
SELECT jsonb_path_query_first(js, '$.floor.apt.no ? (@>3)') FROM house; jsonb_path_query_first
Nous voyons que seule la première valeur est sélectionnée qui satisfait la condition de filtre.
L'opérateur booléen JSONPath pour JSONB @@ est appelé l'opérateur correspondant. Il calcule le prédicat JSONPath en appelant la fonction jsonb_path_match_opr.
Un autre opérateur booléen est @? - ceci est un test d'existence, répond à la question de savoir si l'expression JSONPath retournera des objets SQL / JSON, il appelle la fonction jsonb_path_exists_opr:
'[1,2,3]' @@ '$[*] == 3' true; '[1,2,3]' @? '$[*] @? (@ == 3)' - true
Le même résultat peut être obtenu en utilisant différents opérateurs:
js @? '$.a' js @@ 'exists($.a)' js @@ '$.a == 1' js @? '$ ? ($.a == 1)'
La beauté des opérateurs booléens JSONPath est qu'ils sont pris en charge, accélérés par les indices GIN. jsonb_ops et jsonb_path_ops sont les classes d'opérateurs correspondantes. Dans l'exemple, nous désactivons SEQSCAN, car nous avons un microtable, sur les grandes tables, l'optimiseur lui-même sélectionnera le Bitmap Index:
SET ENABLE_SEQSCAN TO OFF; CREATE INDEX ON house USING gin (js); EXPLAIN (COSTS OFF) SELECT * FROM house WHERE js @? '$.floor[*].apt[*] ? (@.rooms == 3)'; QUERY PLAN
Toutes les fonctions de la forme jsonb_path_xxx () ont la même signature:
jsonb_path_xxx( js jsonb, jsp jsonpath, vars jsonb DEFAULT '{}', silent boolean DEFAULT false )
vars est un objet JSONB pour transmettre des variables JSONPath:
SELECT jsonb_path_query_array('[1,2,3,4,5]', '$[*] ? (@ > $x)', vars => '{"x": 2}'); jsonb_path_query_array
Il est difficile de se passer de vars lorsque nous effectuons une jointure impliquant un champ de type jsonb dans l'une des tables. Disons que nous faisons une demande qui recherche des appartements appropriés pour les employés de cette même maison qui ont écrit leurs exigences pour la superficie minimale dans le questionnaire:
CREATE TABLE demands(name text, position text, demand int); INSERT INTO demands VALUES ('','', 85), ('',' ', 45); SELECT jsonb_path_query(js, '$.floor[*].apt[*] ? (@.area >= $min)', vars => jsonb_build_object('min', demands.demand)) FROM house, demands WHERE name = ''; -[ RECORD 1 ]
Lucky Pacha peut choisir parmi 4 appartements. Mais cela vaut la peine de changer 1 lettre dans la demande - de "P" à "C", et il n'y aura pas le choix! Un seul appartement suffira.
Un mot clé de plus reste: silent est un drapeau qui supprime la gestion des erreurs; ils sont sur la conscience du programmeur.
SELECT jsonb_path_query('[]', 'strict $.a'); ERROR: SQL/JSON member not found DETAIL: jsonpath member accessor can only be applied to an object
L'erreur. Mais ce ne sera pas une erreur:
SELECT jsonb_path_query('[]', 'strict $.a', silent => true); jsonb_path_query
Soit dit en passant, sur les erreurs: conformément à la norme, les erreurs arithmétiques dans les expressions ne donnent pas de messages d'erreur, elles sont sur la conscience du programmeur:
SELECT jsonb_path_query('[1,0,2]', '$[*] ? (1/ @ >= 1)'); jsonb_path_query
Lors du calcul de l'expression dans le filtre, les valeurs du tableau sont recherchées, parmi lesquelles il y a 0, mais la division par 0 ne génère pas d'erreur.
Les fonctions fonctionneront différemment selon le mode sélectionné: Strict ou Lax (dans la traduction «non strict» ou même «lâche», il est sélectionné par défaut). Supposons que nous recherchons une clé en mode Lax en JSON, où elle n'est évidemment pas:
SELECT jsonb '{"a":1}' @? 'lax $.b ? (@ > 1)'; ?column?
Maintenant en mode strict:
SELECT jsonb '{"a":1}' @? 'strict $.b ? (@ > 1)'; ?column?
Autrement dit, où en mode libéral, nous avons reçu FAUX, avec strict nous avons obtenu NULL.
En mode Lax, un tableau avec une hiérarchie complexe [1,2, [3,4,5]] se développe toujours en [1,2,3,4,5]:
SELECT jsonb '[1,2,[3,4,5]]' @? 'lax $[*] ? (@ == 5)'; ?column?
En mode Strict, le nombre «5» ne sera pas trouvé, car il n'est pas au bas de la hiérarchie. Pour le trouver, vous devez modifier la requête, en remplaçant "@" par "@ [*]":
SELECT jsonb '[1,2,[3,4,5]]' @? 'strict $[*] ? (@[*] == 5)'; ?column?
Dans PostgreSQL 12, JSONPath est un type de données. La norme ne dit rien sur la nécessité d'un nouveau type, c'est une propriété d'implémentation. Avec le nouveau type, nous obtenons un travail à part entière avec jsonpath avec l'aide d'opérateurs et d'index accélérant leur travail, qui existent déjà pour JSONB. Sinon, JSONPath devrait être intégré au niveau du code exécuteur et optimiseur.
Vous pouvez lire sur la syntaxe SQL / JSON, par exemple,
ici .
Le billet de blog d'Oleg Bartunov traite de la conformité à la
norme SQL / JSON-2016 pour PostgreSQL, Oracle, SQL Server et MySQL.
Voici une
présentation sur SQL / JSON.
Et voici une
introduction à SQL / JSON.