In PostgreSQL 9.3 speichere ich ziemlich komplexe JSON-Objekte mit Arrays, die in Arrays verschachtelt sind. Dieser Ausschnitt ist nicht die echten Daten, sondern veranschaulicht das gleiche Konzept:
%Vor% Ich möchte, dass SQL-Abfragen auf lineItem -Objekten ... nicht nur innerhalb dieser einzelnen JSON-Struktur, sondern über alle JSON-Objekte in dieser Tabellenspalte hinweg ausgeführt werden können. Zum Beispiel eine SQL-Abfrage, die alle eindeutigen productId und ihre gesamten verkauften qty summen zurückgibt. Um zu verhindern, dass eine solche Abfrage den ganzen Tag dauert, würde ich wahrscheinlich einen Index für lineItem oder seine untergeordneten Felder benötigen.
Unter Verwendung dieser StackOverflow-Frage habe ich herausgefunden, wie um eine funktionierende Abfrage zu schreiben:
%Vor% Diese ursprüngliche StackOverflow-Frage behandelte jedoch Daten, die nur auf einer Ebene und nicht auf zwei Ebenen verschachtelt waren. Ich habe dasselbe Konzept erweitert (d. H. Einen "lateralen Join" innerhalb der FROM -Klausel), indem ich einen zusätzlichen lateralen Join hinzufüge, um eine Ebene tiefer zu tauchen. Ich bin mir jedoch nicht sicher, ob dies der beste Ansatz ist, also lautet der erste Teil meiner Frage: Was ist der beste Ansatz für die Abfrage von JSON-Daten, die eine beliebige Anzahl von Ebenen tief in den JSON-Objekten sind ?
Für den zweiten Teil, erstellen Sie einen Index für solche verschachtelten Daten, Diese StackOverflow-Frage behandelt wieder Daten, die nur eine Ebene tief verschachtelt sind. Ich bin jedoch völlig verloren, da mein Kopf schwimmt und ich versuche, darüber nachzudenken, wie ich das auf eine größere Anzahl von Ebenen anwenden würde. Kann jemand bitte einen klaren Ansatz für die Indexierung von Daten bieten, die mindestens zwei Ebenen tief sind, wie bei lineItems oben?
Um mit dem Problem der unendlichen Rekursion umzugehen, müssen Sie einen rekursiven CTE verwenden um jedes einzelne json-Element in jeder Tabellenzeile zu bearbeiten:
%Vor%Dann können Sie entweder "Menge" mit einer beliebigen ID summieren ...
%Vor%Oder Sie können die "lineItem" -Objekte abrufen und sie beliebig manipulieren:
%Vor%Wie bei der Indizierung könnten Sie die rekursive Abfrage in eine Funktion anpassen, die die eindeutigen Schlüssel für jedes json-Objekt in jeder Zeile Ihrer ursprünglichen Tabelle zurückgibt und dann einen funktionalen Index für Ihre json-Spalte erstellt:
%Vor%Tags und Links sql json postgresql