Ich verwende PostgreSQL 9.4 mit einer Tabelle teams , die eine jsonb -Spalte namens json enthält. Ich suche nach einer Abfrage, bei der ich alle Teams mit den Spielern 3 , 4 und 7 in ihrem Spieler-Array bekommen kann.
Die Tabelle enthält zwei Zeilen mit den folgenden json Daten:
Erste Reihe:
%Vor%zweite Reihe:
%Vor% Wie muss die Abfrage aussehen, um die gewünschte Liste von Teams zu erhalten? Ich habe eine Abfrage ausprobiert, wo ich ein Array von den Mitgliedsspielern jsonb_array_elements(json -> 'members' -> 'players')->'id' erstellen und vergleichen würde, aber alles, was ich erreichen konnte, war ein Ergebnis, bei dem eine der verglichenen Spieler-IDs in einem Team verfügbar war, nicht alle sie.
Sie stehen vor zwei nicht-trivialen Aufgaben gleichzeitig. Ich bin fasziniert.
jsonb mit einer komplexen verschachtelten Struktur. Registrieren Sie zuerst einen Zeilentyp für jsonb_populate_recordset() . Sie können entweder einen Typ dauerhaft mit CREATE TYPE erstellen oder eine temporäre Tabelle für die Ad-hoc-Verwendung erstellen (die automatisch am Ende der Sitzung gelöscht wird):
Wir brauchen nur id , also nicht name . Pro Dokumentation:
JSON-Felder, die nicht im Zielzeilentyp erscheinen, werden in der Ausgabe weggelassen
SQL Fiddle für json in Postgres 9.3 (S. 9,4 nicht verfügbar noch).
Extrahiert das JSON-Array mit Player-Datensätzen:
%Vor% Von diesen enenne ich Zeilen mit nur dem id mit:
... und diese sofort zu einem Postgres-Array zusammenfassen, sodass wir eine Zeile pro Zeile in der Basistabelle behalten:
%Vor%All dies geschieht in einem lateralen Join:
%Vor% Filtern Sie die resultierenden Arrays sofort, um nur diejenigen zu behalten, nach denen wir suchen - mit "enthält " array operator @> :
Voilá.
Wenn Sie diese Abfrage in einer großen Tabelle häufig ausführen, können Sie eine falsche IMMUTABLE -Funktion erstellen, die das Array wie oben extrahiert und basierend auf dieser Funktion einen funktionalen GIN-Index erstellt schnell.
"Fake", weil die Funktion vom zugrunde liegenden Zeilentyp abhängt, d. H. Bei einer Katalogsuche, und würde sich ändern, wenn sich das ändert. (So stellen Sie sicher, dass es sich nicht ändert.) Ähnlich zu diesem:
Beiseite:
Verwenden Sie keine Typnamen wie json als Spaltennamen (selbst wenn das erlaubt ist), was zu komplizierten Syntaxfehlern und verwirrenden Fehlermeldungen führt.
Ich wollte das Gleiche tun wie oben. Einzige Bedingung war, dass ich Teilstring-Matching durchführen musste und keine exakte Übereinstimmung.
Dies ist, was ich getan habe (inspiriert von der obigen Antwort natürlich)
%Vor%Veröffentlichen Sie es hier, wenn es hilfreich ist.
Tags und Links sql postgresql postgresql-9.4 jsonb relational-division