Join-Tabellen mit einem Wert innerhalb einer Spalte JSONB

Gibt es zwei Tabellen:

Autorisierte Ansprechpartner (auth_contacts):

(
userid varchar
contacts jsonb
)

contacts enthält ein array von Kontakten mit Attributen {contact_id, type}

discussion:

(
contact_id varchar
discussion_id varchar
discussion_details jsonb
)

Den Tisch auth_contacts hat mindestens 100k Datensätze, so dass es nicht JSONB-Typ ist nicht angemessen nach, wie es wäre, die doppelte oder dreifache Menge der Datensätze.

Beispieldaten für auth_contacts:

userid  | contacts
'11111' | '{"contact": [{"type": "type_a", "contact_id": "1-A-12"}
                      , {"type": "type_b", "contact_id": "1-A-13"}]}'

discussion Tabelle hat 5 Millionen sonderbare Aufzeichnungen.

Ich joinen will auf discussion.contact_id (relationale Spalte) mit contact id, welche ein json-Objekt in array von json-Objekten in auth_contacts.contacts.

Eine sehr grobe Art und Weise ist:

SELECT *
FROM discussion d 
JOIN (SELECT userid, JSONB_OBJECT_KEYS(a.contacts) AS auth_contact
      FROM auth_contacts a) AS contacts
      ON (d.contact_id = contacts.auth_contact::text)

Was dieser tut, ist eigentlich zur Laufzeit erstellen (inner sql) userid vs Kontakt-id-Tabelle (was ich zu vermeiden, und damit ging für JSONB-Datentyp
Diese Abfrage für einen Benutzer mit großen Datensätzen dauert 26 Sekunden, das ist noch nicht alles gut.
Habe versucht ein paar andere Möglichkeiten: PostgreSQL 9.4: Aggregat /Join-Tabelle auf JSON-Feld-id im inneren array

Aber es sollte eine sauberere und bessere Weg wäre so einfach wie
JOIN d.contact_id = contacts -> contact -> contact_id?
Wenn ich versuche, es nicht Ertrag keine Ergebnisse.

Bei der Suche im Netz scheint dies eine ziemlich langwierige Aufgabe?

Wenn Sie versuchen, verwenden Sie contacts->'contact'->'contact_id' es werden nicht alle Werte da contact ist ein array. Technisch könnte man so etwas wie contacts->'contact'@>'[{"contact_id":"1-A-12"}]' aber für die Teilnahme basiert auf, ich bin nicht sicher, es wäre effizienter.
Kontakte->'Kontakt'->0->'contact_id', Kontakte->'Kontakt'->1->'contact_id' gibt nachfolgende Werte. Für '@>' es ist eigentlich für die überprüfung von Bedingung (ob Links enthält, im Recht) und kann nicht verwendet werden, für sich als pro sich meiner Kenntnis. Wäre es möglich ?
Es ist möglich, eine hässliche SELECT * FROM discussion d JOIN auth a ON a.contacts->'contact'@>('[{"contact_id":"'||d.contact_id||'"}]')::jsonb basiert auf einem Schnelltest, aber ich würde nicht erwarten, es zu sein, die performant. Vielleicht mit `jsonb_array_elements() wäre besser, aber es hat immer noch zu explodieren die Werte aus dem JSON-sowieso.
Vereinbart wurde ja. Aber wie Sie bereits erwähnt, Leistung klug, es ist ein killer. Ich denke es sollte ein schneller und sauberer Weg, wie ist es in MongoDB?
Der index wird verwendet, auf die auth_contacts Seite. Zum Beispiel nachschlagen auth_user für eine Diskussion gegeben. Für die andere Richtung, Sie würden unnest dann join unter Verwendung des PK-index auf discussion.contact_id. Aber das ist alles akademisches Gerede. Die wirkliche Lösung ist ein vernünftiger Daten-Modell.

InformationsquelleAutor Prachi Tripathi | 2015-07-09

9

Proof of concept

Ihre "grobe Art und Weise" nicht wirklich funktionieren. Hier ist eine grobe Art und Weise, dass gilt:
```
SELECT *
FROM  auth_contacts a
    , jsonb_to_recordset(a.contacts->'contact') AS c(contact_id text)
JOIN  discussion d USING (contact_id);
```
Wie schon kommentiert, kann man auch formulieren, die eine join-Bedingung mit der enthält der operator @>:
```
SELECT *
FROM   auth_contacts a
JOIN   discussion d ON a.contacts->'contact'
                    @> json_build_array(json_build_object('contact_id', d.contact_id))::jsonb
```
Sondern eher die JSON-Erstellung Funktionen als string-Verkettung. Sieht umständlich, aber tatsächlich sehr schnell, wenn unterstützt, mit einem funktionale jsonb_path_ops GIN index:
```
CREATE INDEX auth_contacts_contacts_gin_idx ON auth_contacts
USING  gin ((contacts->'contact') jsonb_path_ops);
```
Details:
- Index für die Suche nach einem element in ein JSON array
- Postgres 9.4 jsonb-array als Tabelle
Richtige Lösung

Dies ist alles faszinierend, mit zu spielen, aber das problem hier ist das relationale Modell. Ihre Forderung:

daher macht es nicht JSONB-Typ nicht geeignet ist, so wie es
würde die doppelte oder dreifache Menge der Datensätze.

ist das Gegenteil von dem, was richtig. Es ist Unsinn wrap-IDs, die Sie brauchen für das verknüpfen von Tabellen in eine JSON-Dokument-Typ. Normalisieren Sie Ihren Tisch mit einer viele-zu-viele-Beziehung und der Durchführung aller IDs, mit der Sie arbeiten innerhalb der DB als separate Spalten mit den entsprechenden Daten-Typ. Grundlagen:
- Wie zum ausführen von update-Operationen auf Spalten vom Datentyp JSONB in PostgreSQL 9.4
- Wie implementiert man eine viele-zu-viele-Beziehung in PostgreSQL?
macht Sinn für, die. Aber Sie haben auch einen einfachen syntax-Fehler. c.contacts sollte wohl a.contacts.
Entschuldigung-meine grobe Art und Weise funktioniert, wo ich es speichern, wie folgt: {"3-1LS-86": "type_a", "3-1IC-1432": "type_b"} und nicht als array,damit die Unterabfrage der Schlüssel funktioniert. Abt, die richtige Lösung vereinbart, es shud Wünsche, relational, sondern betrachten auth_contacts Tabelle : Anzahl der Nutzer, suchen wir am könnte ein hundert tausend (100000+ ) - und pro-Benutzer-autorisierte Kontakte konnten auf durchschnittlich 6000-und einige tausend Nutzer haben 60k+ autorisierte Ansprechpartner. Nun, die Zeilen auth Kontakte wäre 500million+ Datensätze, die ist, warum ich eine Verknüpfung auf diese mit der Zeitachse mit 1 Million Datensätze töten würde das system,würde es?
Auch seine eins-zu-viele-Beziehung zwischen Benutzer und authhorized Kontakte und nicht viele zu viele (Userid, auth_contacts)
Sieht aus wie eine n:m-Beziehung zwischen auth_contacts und discussion. Aber ich weiß nicht, die details. Wenn Sie erwarten, dass viele Zeilen, werden Sie sicher nicht verwenden-integer (oder bigint, wenn Sie müssen) für die ID-Spalten und nicht als string-Typen.
Ja mit der ID ist ok. Aber ich bin immer noch Fragen, wenn postgres JSONB, wenn online recherchiert zeigt, es ist schneller als MongoDB aber die Verknüpfung wie im Beispiel dauert 28 Sekunden für einen Benutzer, der mit 70k + Kontakte

InformationsquelleAutor Erwin Brandstetter

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.

Proof of concept

Richtige Lösung