Nach dem
Committeefit 2019-03 fand ein Feature Freeze statt. Wir haben hier eine fast traditionelle Kolumne: Wir haben
bereits über das Einfrieren im letzten Jahr geschrieben. Nun werden die Ergebnisse von 2019: Welche der neuen werden in PostgreSQL 12 enthalten sein? In diesem Teil der Überprüfung, die JSONPath gewidmet ist, werden Beispiele und Fragmente aus dem Bericht "Postgres 12 in Etudes" verwendet, den Oleg Bartunov am 9. April dieses Jahres bei
Saint Highload ++ in St. Petersburg gelesen hat.
Jsonpath
Alles, was mit JSON (B) zu tun hat, ist weltweit und in Russland gefragt, und dies ist einer der wichtigsten Entwicklungsbereiche bei Postgres Professional. Der jsonb-Typ, die Funktionen und Operatoren für die Arbeit mit JSON / JSONB wurden in PostgreSQL Version 9.4 veröffentlicht. Sie wurden von einem Team unter der Leitung von Oleg Bartunov erstellt.
Der SQL / 2016-Standard sieht die Arbeit mit JSON vor: JSONPath wird dort erwähnt - eine Reihe von Datenadressierungswerkzeugen in JSON; JSONTABLE - Mittel zum Konvertieren von JSON in reguläre Tabellen; Eine große Familie von Funktionen und Operatoren. Trotz der Tatsache, dass JSON in Postgres seit langem unterstützt wird, haben Oleg Bartunov und seine Kollegen 2017 begonnen, an der Unterstützung des Standards zu arbeiten. Die Einhaltung des Standards ist immer gut. Von allem, was im Standard beschrieben ist, ist JSONPath in Version 12 nur ein, aber der wichtigste Patch, also werden wir zuerst darüber sprechen.
In der Antike wurde JSON verwendet und in Textfeldern gespeichert. In 9.3 wurde ein spezieller Datentyp für JSON angezeigt, die damit verbundene Funktionalität war jedoch nicht umfangreich, und Anforderungen mit diesem Typ arbeiteten aufgrund der Zeit, die für das Parsen der Textdarstellung von JSON aufgewendet wurde, nur langsam. Dies stoppte viele potenzielle Postgres-Benutzer, die NoSQL-Datenbanken bevorzugten. Die Produktivität von Postgres stieg auf 9,4, als Postgres dank O. Bartunov, A. Korotkov und F. Sigaev eine binäre Version von JSON einführte - den jsonb-Typ.
jsonb muss nicht jedes Mal analysiert werden, daher ist die Arbeit damit viel schneller. Von den neuen Funktionen und Operatoren, die gleichzeitig entstanden sind, arbeiten einige nur mit einem neuen Binärtyp, z. B. dem wichtigen Operator des Auftretens
@> , der prüft, ob ein Element oder Array in einem bestimmten JSONB enthalten ist:
SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb;
gibt TRUE, da das Array auf der rechten Seite in das Array auf der linken Seite eintritt. Aber
SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb;
gibt FALSE, da die Verschachtelungsebene unterschiedlich ist, muss sie explizit festgelegt werden. Wird der Existenzoperator für den Typ jsonb eingeführt
? (ein Fragezeichen), das prüft, ob eine Zeichenfolge ein Objektschlüssel oder ein Element eines Arrays auf der obersten Ebene der JSONB-Werte ist, sowie zwei weitere ähnliche Operatoren (Details
hier ). Sie werden von GIN-Indizes mit zwei Klassen von GIN-Operatoren unterstützt. Mit dem Operator
-> (Pfeil) können Sie durch JSONB "navigieren". Er gibt einen Wert nach Schlüssel oder, wenn es sich um ein Array handelt, nach Index zurück. Es
gibt mehrere weitere Bediener zum Verschieben. Es gibt jedoch keine Möglichkeit, Filter zu organisieren, die ähnlich wie WHERE funktionieren. Es war ein Durchbruch: Dank jsonb wurde Postgres als RDBMS mit NoSQL-Funktionen immer beliebter.
2014 entwickelten A. Korotkov, O. Bartunov und F. Sigaev die Erweiterung jsquery, die als Ergebnis in Postgres Pro Standard 9.5 (und in späteren Versionen von Standard und Enterprise) enthalten war. Es bietet zusätzliche, sehr umfassende Funktionen für die Arbeit mit json (b). Diese Erweiterung definiert die Abfragesprache zum Extrahieren von Daten aus json (b) und Indizes, um diese Abfragen zu beschleunigen. Diese Funktionalität wurde von den Benutzern benötigt, sie waren nicht bereit, auf den Standard und die Aufnahme neuer Funktionen in die Vanille-Version zu warten. Der praktische Wert wird auch durch die Tatsache belegt, dass die Entwicklung von Wargaming.net gesponsert wurde. Die Erweiterung implementiert einen speziellen Typ - jsquery.
Eine Abfrage in dieser Sprache ist kompakt und sieht beispielsweise folgendermaßen aus:
SELECT '{"apt":[{"no": 1, "rooms":2}, {"no": 2, "rooms":3}, {"no": 3, "rooms":2}]}'::jsonb @@ 'apt.#.rooms=3'::jsquery;
Wir fragen hier, ob es im Wohnhaus „drei Rubel“ gibt. Der Typ jsquery muss angegeben werden, da sich der Operator @@ jetzt auch im Typ jsonb befindet. Die Beschreibung ist
hier und die Präsentation mit vielen Beispielen ist
hier .
Insgesamt: Postgres hatte bereits alles für die Arbeit mit JSON, und dann erschien der SQL: 2016-Standard. Es stellte sich heraus, dass sich seine Semantik nicht so sehr von unserer in der jsquery-Erweiterung unterscheidet. Es ist möglich, dass die Autoren des Standards sogar einen Blick auf jsquery geworfen haben und JSONPath erfunden haben. Unser Team musste das, was wir bereits hatten, etwas anders umsetzen und natürlich auch viele neue Dinge.
Vor mehr als einem Jahr, beim Commitfest im März, wurden der Community die Früchte unserer Programmierbemühungen in Form von drei großen Patches mit Unterstützung des
SQL: 2016- Standards angeboten:
SQL / JSON: JSONPath;
SQL / JSON: Funktionen;
SQL / JSON: JSON_TABLE.
Die Entwicklung eines Patches ist jedoch nicht das ganze Geschäft. Die Bewerbung ist auch nicht einfach, insbesondere wenn die Patches groß sind und viele Module betreffen. Es sind viele Iterationen der Revision erforderlich. Der Patch muss wie bei kommerziellen Unternehmen beworben werden und viele Ressourcen (Arbeitsstunden) investieren. Der Chefarchitekt von Postgres Professional, Alexander Korotkov, nahm es auf sich (da er jetzt den Status eines Committers hat) und erreichte die Übernahme des JSONPath-Patches - des Haupt-Patches in dieser Reihe von Patches. Die zweite und dritte befinden sich jetzt im Status der Bedarfsüberprüfung. Mit dem fokussierten JSONPath können Sie mit der JSON (B) -Struktur arbeiten und sind flexibel genug, um ihre Fragmente hervorzuheben. Von den 15 im Standard vorgeschriebenen Punkten sind 14 implementiert, und dies ist mehr als in Oracle, MySQL und MS SQL.
Die JSONPath-Notation unterscheidet sich von den Postgres-Anweisungen für die Arbeit mit der JSON- und JSQuery-Notation. Die Hierarchie wird durch Punkte angezeigt:
$ .abc (in der Postgres 11-Notation müsste ich 'a' -> 'b' -> 'c' schreiben);
$ - der aktuelle Kontext des Elements - tatsächlich definiert der Ausdruck mit $ die json (b) -Region, die verarbeitet werden soll, einschließlich der im Filter, der Rest davon ist nicht für die Arbeit verfügbar;
@ - der aktuelle Kontext im Filterausdruck - iteriert mit $ über die im Ausdruck verfügbaren Pfade;
[*] - ein Array;
* - Platzhalter bedeutet im Ausdruck mit $ oder @ einen beliebigen Wert des Pfadsegments, jedoch unter Berücksichtigung der Hierarchie;
** - als Teil des Ausdrucks mit $ oder @ kann jeder Wert des Pfadsegments ohne Berücksichtigung der Hierarchie bedeuten - es ist praktisch, ihn zu verwenden, wenn Sie die Verschachtelungsebene von Elementen nicht kennen;
Betreiber "?" Mit dieser Option können Sie einen Filter ähnlich dem von WHERE organisieren:
$ .abc? (@ .x> 10);
$ .abcxtype () sowie size (), double (), decken (), floor (), abs (), datetime (), keyvalue () sind Methoden.
Eine Abfrage mit der Funktion jsonb_path_query (zu den folgenden Funktionen) könnte folgendermaßen aussehen:
SELECT jsonb_path_query_array('[1,2,3,4,5]', '$[*] ? (@ > 3)'); jsonb_path_query_array
Obwohl ein spezieller Patch mit Funktionen nicht festgeschrieben ist, verfügt der JSONPath-Patch bereits über Schlüsselfunktionen für die Arbeit mit 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
Beachten Sie, dass die Gleichheit in JSONPath-Ausdrücken ein einzelnes "=" ist, während sie in jsquery doppelt ist: "==".
Für elegantere Illustrationen generieren wir JSONB in einer einspaltigen Hausplatte:
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} ] } ] }');
Abb. 1 Gehäuse des JSON-Baums mit zugewiesenen Blattwohnungen.Dies ist ein seltsamer JSON: Es hat eine verwirrte Hierarchie, aber es ist aus dem Leben genommen, und im Leben ist es oft notwendig, mit dem zu arbeiten, was ist, und nicht mit dem, was sein sollte. Ausgestattet mit den Funktionen der neuen Version finden wir Wohnungen im 1. und 2. Stock, aber nicht die erste in der Liste der Bodenwohnungen (auf dem Baum sind sie grün hervorgehoben):
SELECT jsonb_path_query_array(js, '$.floor[0, 1].apt[1 to last]') FROM house;
In PostgreSQL 11 müssen Sie Folgendes fragen:
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);
Nun eine sehr einfache Frage: Gibt es Zeilen, die (irgendwo) den Wert „Moskau“ enthalten? Ganz einfach:
SELECT jsonb_path_exists(js, '$.** ? (@ == "Moscow")') FROM house;
In Version 11 müssten Sie ein riesiges Skript schreiben:
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"');
Abb. 2 Wohnungsbaum JSON, Moskau wurde gefunden!Wir suchen eine Wohnung auf jeder Etage mit einer Fläche von 40 bis 90 qm:
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)
Wir suchen Wohnungen mit Zimmern nach dem 3. mit unserem Housing Jason:
SELECT jsonb_path_query(js, '$.floor.apt.no ? (@>3)') FROM house; jsonb_path_query
Und so funktioniert jsonb_path_query_first:
SELECT jsonb_path_query_first(js, '$.floor.apt.no ? (@>3)') FROM house; jsonb_path_query_first
Wir sehen, dass nur der erste Wert ausgewählt wird, der die Filterbedingung erfüllt.
Der boolesche JSONPath-Operator für JSONB @@ wird als Matching-Operator bezeichnet. Es berechnet das JSONPath-Prädikat durch Aufrufen der Funktion jsonb_path_match_opr.
Ein anderer boolescher Operator ist @? - Dies ist ein Test der Existenz, beantwortet die Frage, ob der JSONPath-Ausdruck SQL / JSON-Objekte zurückgibt, und ruft die Funktion jsonb_path_exists_opr auf:
'[1,2,3]' @@ '$[*] == 3' true; '[1,2,3]' @? '$[*] @? (@ == 3)' - true
Das gleiche Ergebnis kann mit verschiedenen Operatoren erzielt werden:
js @? '$.a' js @@ 'exists($.a)' js @@ '$.a == 1' js @? '$ ? ($.a == 1)'
Das Schöne an JSONPath Boolean-Operatoren ist, dass sie unterstützt und durch GIN-Indizes beschleunigt werden. jsonb_ops und jsonb_path_ops sind die entsprechenden Operatorklassen. In diesem Beispiel deaktivieren wir SEQSCAN, da wir eine Mikrotabelle haben. In großen Tabellen wählt der Optimierer selbst den Bitmap-Index aus:
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
Alle Funktionen der Form jsonb_path_xxx () haben dieselbe Signatur:
jsonb_path_xxx( js jsonb, jsp jsonpath, vars jsonb DEFAULT '{}', silent boolean DEFAULT false )
vars ist ein JSONB-Objekt zum Übergeben von JSONPath-Variablen:
SELECT jsonb_path_query_array('[1,2,3,4,5]', '$[*] ? (@ > $x)', vars => '{"x": 2}'); jsonb_path_query_array
Es ist schwierig, auf vars zu verzichten, wenn wir einen Join mit einem Feld vom Typ jsonb in einer der Tabellen erstellen. Angenommen, wir stellen einen Antrag, der nach geeigneten Wohnungen für Mitarbeiter in diesem Haus sucht, die ihre Anforderungen für die Mindestfläche im Fragebogen notiert haben:
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 Pasha kann aus 4 Apartments wählen. Es lohnt sich jedoch, 1 Buchstaben in der Anfrage zu ändern - von "P" in "C", und es wird keine Wahl geben! Nur 1 Wohnung reicht aus.
Ein weiteres Schlüsselwort bleibt: Silent ist ein Flag, das die Fehlerbehandlung unterdrückt, und sie liegen im Gewissen des Programmierers.
SELECT jsonb_path_query('[]', 'strict $.a'); ERROR: SQL/JSON member not found DETAIL: jsonpath member accessor can only be applied to an object
Der Fehler. Dies wird jedoch kein Fehler sein:
SELECT jsonb_path_query('[]', 'strict $.a', silent => true); jsonb_path_query
Übrigens über Fehler: Gemäß dem Standard geben arithmetische Fehler in Ausdrücken keine Fehlermeldungen aus, sie liegen im Gewissen des Programmierers:
SELECT jsonb_path_query('[1,0,2]', '$[*] ? (1/ @ >= 1)'); jsonb_path_query
Bei der Berechnung des Ausdrucks im Filter werden die Array-Werte durchsucht, darunter 0, aber das Teilen durch 0 erzeugt keinen Fehler.
Die Funktionen funktionieren je nach ausgewähltem Modus unterschiedlich: Streng oder Lax (in der Übersetzung „nicht streng“ oder sogar „locker“ ist sie standardmäßig ausgewählt). Angenommen, wir suchen in JSON nach einem Schlüssel im Lax-Modus, wo dies offensichtlich nicht der Fall ist:
SELECT jsonb '{"a":1}' @? 'lax $.b ? (@ > 1)'; ?column?
Jetzt im strengen Modus:
SELECT jsonb '{"a":1}' @? 'strict $.b ? (@ > 1)'; ?column?
Das heißt, wo wir im liberalen Modus FALSE erhalten haben, haben wir mit strikt NULL erhalten.
Im Lax-Modus wird ein Array mit einer komplexen Hierarchie [1,2, [3,4,5]] immer auf [1,2,3,4,5] erweitert:
SELECT jsonb '[1,2,[3,4,5]]' @? 'lax $[*] ? (@ == 5)'; ?column?
Im strengen Modus wird die Zahl „5“ nicht gefunden, da sie sich nicht am Ende der Hierarchie befindet. Um es zu finden, müssen Sie die Abfrage ändern und "@" durch "@ [*]" ersetzen:
SELECT jsonb '[1,2,[3,4,5]]' @? 'strict $[*] ? (@[*] == 5)'; ?column?
In PostgreSQL 12 ist JSONPath ein Datentyp. Der Standard sagt nichts über die Notwendigkeit eines neuen Typs aus, er ist eine Eigenschaft der Implementierung. Mit dem neuen Typ erhalten wir eine vollständige Arbeit mit jsonpath mithilfe von Operatoren und Indizes, die ihre Arbeit beschleunigen und bereits für JSONB existieren. Andernfalls müsste JSONPath auf der Ebene des Executor- und Optimizer-Codes integriert werden.
Hier können Sie beispielsweise Informationen zur SQL / JSON-Syntax lesen.
In Oleg Bartunovs Blogbeitrag geht es um die
Konformität von
SQL / JSON-Standard 2016 für PostgreSQL, Oracle, SQL Server und MySQL.
Hier ist eine
Präsentation zu SQL / JSON.
Und hier ist eine
Einführung in SQL / JSON.