Unterabfragen in Azure Cosmos DB for NoSQL
GILT FÜR: NoSQL
Eine Unterabfrage ist eine Abfrage, die in Azure Cosmos DB for NoSQL innerhalb einer anderen Abfrage geschachtelt ist. Eine Unterabfrage wird auch als innere Abfrage oder innere SELECT
(Auswahl) bezeichnet. Die Anweisung, die eine Unterabfrage enthält, wird normalerweise als äußere Abfrage bezeichnet.
Es gibt zwei Haupttypen von Unterabfragen:
- Korreliert: eine Unterabfrage, die auf Werte aus der äußeren Abfrage verweist. Die Unterabfrage wird für jede Zeile, die die äußere Abfrage verarbeitet, einmal ausgewertet.
- Nicht korreliert: eine Unterabfrage, die von der äußeren Abfrage unabhängig ist. Sie kann ohne Abhängigkeit von der äußeren Abfrage eigenständig ausgeführt werden.
Hinweis
Azure Cosmos DB unterstützt nur korrelierte Unterabfragen.
Unterabfragen können basierend auf der Anzahl der Zeilen und Spalten, die sie zurückgeben, weiter klassifiziert werden. Es gibt drei Typen:
- Tabelle: gibt mehrere Zeilen und mehrere Spalten zurück.
- Mehrwertig: gibt mehrere Zeilen und eine einzelne Spalte zurück.
- Skalar: gibt eine einzelne Zeile und eine einzelne Spalte zurück.
Abfragen in Azure Cosmos DB for NoSQL geben immer eine einzelne Spalte (entweder einen einfachen Wert oder ein komplexes Element) zurück. Daher sind nur mehrwertige und skalare Unterabfragen anwendbar. Sie können eine mehrwertige Unterabfrage nur in der FROM
-Klausel als relationalen Ausdruck verwenden. Sie können eine skalare Unterabfrage in der SELECT
- oder WHERE
-Klausel als Skalarausdruck oder aber in der FROM
-Klausel als relationalen Ausdruck verwenden.
Mehrwertige Unterabfragen geben eine Reihe von Elementen zurück und werden immer in der FROM
-Klausel verwendet. Sie werden verwendet für:
- Optimieren von
JOIN
(Self-Join)-Ausdrücken. - Einmaliges Auswerten von ressourcenintensiven Ausdrücken und vielfaches Verweisen darauf.
Mehrwertige Unterabfragen können JOIN
-Ausdrücke optimieren, indem Prädikate nach jedem select-many-Ausdruck statt nach allen cross-joins in der WHERE
-Klausel gepusht werden.
Betrachten Sie die folgende Abfrage:
SELECT VALUE
COUNT(1)
FROM
products p
JOIN
t in p.tags
JOIN
q in p.onHandQuantities
JOIN
s in p.warehouseStock
WHERE
t.name IN ("winter", "fall") AND
(q.quantity BETWEEN 0 AND 10) AND
NOT s.backstock
Bei dieser Abfrage gleicht der Index jedes Element ab, das ein Tag mit dem name
(Namen) „winter“ (Winter) oder „fall“ (Herbst), mindestens einer quantity
(Menge) zwischen zero (null) und ten (zehn) und mindestens einem Lager hat, bei dem der Wert für backstock
gleich false
lautet. Der JOIN
-Ausdruck ermittelt hier das Kreuzprodukt aller Elemente von tags
-, onHandQuantities
- und warehouseStock
-Arrays für jedes übereinstimmende Element, bevor ein Filter angewendet wird.
Die WHERE
-Klausel wendet dann das Filterprädikat auf jedes <c, t, n, s>
-Tupel an. Wenn beispielsweise ein übereinstimmendes Element in jedem der drei Arrays 10 Elemente enthält, wird es auf 1 x 10 x 10 x 10
(d. h. 1.000) Tupel erweitert. Die Verwendung von Unterabfragen kann hier helfen, indem verknüpfte Arrayelemente herausgefiltert werden, bevor der Joinvorgang mit dem nächsten Ausdruck ausgeführt wird.
Diese Abfrage entspricht der vorherigen, verwendet aber Unterabfragen:
SELECT VALUE
COUNT(1)
FROM
products p
JOIN
(SELECT VALUE t FROM t IN p.tags WHERE t.name IN ("winter", "fall"))
JOIN
(SELECT VALUE q FROM q IN p.onHandQuantities WHERE q.quantity BETWEEN 0 AND 10)
JOIN
(SELECT VALUE s FROM s IN p.warehouseStock WHERE NOT s.backstock)
Gehen Sie davon aus, dass nur ein Element im Kategorienarray dem Filter entspricht, und es gibt fünf Elemente für Mengen- und Bestandsarrays. Die JOIN
-Ausdrücke werden dann – im Gegensatz zu 1.000 Elementen in der ersten Abfrage – auf 1 x 1 x 5 x 5
(25) Elemente erweitert.
Unterabfragen können helfen, Abfragen mit ressourcenintensiven Ausdrücken wie z.B. benutzerdefinierte Funktionen (User-Defined Functions, UDFs), komplexe Zeichenfolgen oder arithmetische Ausdrücke zu optimieren. Sie können eine Unterabfrage zusammen mit einem JOIN
-Ausdruck verwenden, um den Ausdruck einmal auszuwerten, aber oft darauf zu verweisen.
Angenommen, Sie haben die folgende benutzerdefinierte Funktion (User-Defined Function, UDF) definiert: getTotalWithTax
.
function getTotalWithTax(subTotal){
return subTotal * 1.25;
}
Die folgende Abfrage führt die benutzerdefinierte Funktion getTotalWithTax
mehrmals aus:
SELECT VALUE {
subtotal: p.price,
total: udf.getTotalWithTax(p.price)
}
FROM
products p
WHERE
udf.getTotalWithTax(p.price) < 22.25
Hier ist eine entsprechende Abfrage, die die benutzerdefinierte Funktion nur einmal ausführt:
SELECT VALUE {
subtotal: p.price,
total: totalPrice
}
FROM
products p
JOIN
(SELECT VALUE udf.getTotalWithTax(p.price)) totalPrice
WHERE
totalPrice < 22.25
Tipp
Beachten Sie das Kreuzproduktverhalten von JOIN
-Ausdrücken. Wenn der UDF-Ausdruck als undefined
(nicht definiert) ausgewertet werden kann, sollten Sie sicherstellen, dass der JOIN
-Ausdruck immer eine einzelne Zeile generiert, indem ein Objekt aus der Unterabfrage statt direkt vom Wert zurückgegeben wird.
Möglicherweise müssen Sie oft auf statische Daten, die sich nur selten ändern, verweisen, z. B. Maßeinheiten. Es ist ideal, statische Daten für jedes Element in einer Abfrage nicht zu duplizieren. Das Vermeiden dieser Duplizierung spart Speicher und verbessert die Schreibleistung, indem die einzelne Elementgröße kleiner gehalten wird. Sie können mithilfe einer Unterabfrage die Semantik eines inneren Joins mit einer Auflistung von statischen Verweisdaten imitieren.
Betrachten Sie beispielsweise diesen Satz von Messungen:
Name | Multiplikator | Basiseinheit | |
---|---|---|---|
ng |
Nanogram | 1.00E-09 |
Gram |
µg |
Microgram | 1.00E-06 |
Gram |
mg |
Milligram | 1.00E-03 |
Gram |
g |
Gram | 1.00E+00 |
Gram |
kg |
Kilogram | 1.00E+03 |
Gram |
Mg |
Megagram | 1.00E+06 |
Gram |
Gg |
Gigagram | 1.00E+09 |
Gram |
Die folgende Abfrage imitiert das Verknüpfen mit diesen Daten, sodass Sie den Namen der Einheit der Ausgabe hinzufügen:
SELECT
s.id,
(s.weight.quantity * m.multiplier) AS calculatedWeight,
m.unit AS unitOfWeight
FROM
shipments s
JOIN m IN (
SELECT VALUE [
{unit: 'ng', name: 'nanogram', multiplier: 0.000000001, baseUnit: 'gram'},
{unit: 'µg', name: 'microgram', multiplier: 0.000001, baseUnit: 'gram'},
{unit: 'mg', name: 'milligram', multiplier: 0.001, baseUnit: 'gram'},
{unit: 'g', name: 'gram', multiplier: 1, baseUnit: 'gram'},
{unit: 'kg', name: 'kilogram', multiplier: 1000, baseUnit: 'gram'},
{unit: 'Mg', name: 'megagram', multiplier: 1000000, baseUnit: 'gram'},
{unit: 'Gg', name: 'gigagram', multiplier: 1000000000, baseUnit: 'gram'}
]
)
WHERE
s.weight.units = m.unit
Ein skalarer Unterabfragenausdruck ist eine Unterabfrage, die eine Auswertung für einen einzelnen Wert ausführt. Der Wert des skalaren Unterabfragenausdrucks ist der Wert der Projektion (SELECT
-Klausel) der Unterabfrage. Sie können einen skalaren Unterabfragenausdruck vielfach dort verwenden, wo ein skalarer Ausdruck gültig ist. Beispielsweise können Sie eine skalare Unterabfrage in einem beliebigen Ausdruck in SELECT
- und WHERE
-Klauseln verwenden.
Die Verwendung einer skalaren Unterabfrage ist jedoch nicht immer hilfreich zum Optimieren Ihrer Abfrage. Beispielsweise bietet die Übergabe einer skalaren Unterabfrage als Argument an system- oder benutzerdefinierte Funktionen keinen Vorteil bei der Reduzierung des Verbrauchs von Anforderungseinheiten (Request Units, RUs) oder der Latenz.
Skalare Unterabfragen können weiter klassifiziert werden als:
- Skalare Unterabfragen mit einfachem Ausdruck
- Aggregierte skalare Unterabfragen
Eine skalare Unterabfrage mit einfachem Ausdruck ist eine korrelierte Unterabfrage mit einer SELECT
-Klausel, die keine Aggregatausdrücke enthält. Diese Unterabfragen bieten keine Optimierungsvorteile, da der Compiler sie in einen größeren einfachen Ausdruck konvertiert. Es gibt keinen korrelierten Kontext zwischen den inneren und äußeren Abfragen.
Betrachten Sie als erstes Beispiel diese triviale Abfrage.
SELECT
1 AS a,
2 AS b
Sie können diese Abfrage mithilfe einer skalaren Unterabfrage mit einfachem Ausdruck umschreiben.
SELECT
(SELECT VALUE 1) AS a,
(SELECT VALUE 2) AS b
Beide Abfragen erzeugen dieselbe Ausgabe.
[
{
"a": 1,
"b": 2
}
]
In dieser nächsten Beispielabfrage wird der eindeutige Bezeichner mit einem Präfix als skalare Unterabfrage mit einfachem Ausdruck verkettet.
SELECT
(SELECT VALUE Concat('ID-', p.id)) AS internalId
FROM
products p
In diesem Beispiel wird eine skalare Unterabfrage mit einfachem Ausdruck verwendet, um nur die relevanten Felder für jedes Element zurückzugeben. Die Abfrage gibt für jedes Element etwas aus, enthält das projizierte Feld aber nur, wenn es dem Filter innerhalb der Unterabfrage entspricht.
SELECT
p.id,
(SELECT p.name WHERE CONTAINS(p.name, "glove")).name
FROM
products p
[
{
"id": "aaaaaaaa-0000-1111-2222-bbbbbbbbbbbb",
"name": "Winter glove"
},
{
"id": "bbbbbbbb-1111-2222-3333-cccccccccccc"
},
{
"id": "cccccccc-2222-3333-4444-dddddddddddd"
}
]
Eine aggregierte skalare Unterabfrage ist eine Unterabfrage, die eine Aggregatfunktion in Projektion oder Filter aufweist, die eine Auswertung für einen einzelnen Wert ausführt.
Betrachten Sie als erstes Beispiel ein Element mit den folgenden Feldern.
{
"name": "Snow coat",
"inventory": [
{
"location": "Redmond, WA",
"quantity": 50
},
{
"location": "Seattle, WA",
"quantity": 30
},
{
"location": "Washington, DC",
"quantity": 25
}
]
}
Hier ist eine Unterabfrage mit einem einzelnen Aggregatfunktionsausdruck in ihrer Projektion. Diese Abfrage zählt alle Tags für jedes Element.
SELECT
p.name,
(SELECT VALUE COUNT(1) FROM i IN p.inventory) AS locationCount
FROM
products p
[
{
"name": "Snow coat",
"locationCount": 3
}
]
Hier ist die gleiche Unterabfrage mit einem Filter.
SELECT
p.name,
(SELECT VALUE COUNT(1) FROM i IN p.inventory WHERE ENDSWITH(i.location, "WA")) AS washingtonLocationCount
FROM
products p
[
{
"name": "Snow coat",
"washingtonLocationCount": 2
}
]
Hier ist eine weitere Unterabfrage mit mehreren Aggregatfunktionsausdrücken:
SELECT
p.name,
(SELECT
COUNT(1) AS locationCount,
SUM(i.quantity) AS totalQuantity
FROM i IN p.inventory) AS inventoryData
FROM
products p
[
{
"name": "Snow coat",
"inventoryData": {
"locationCount": 2,
"totalQuantity": 75
}
}
]
Und schließlich ist hier eine Abfrage mit einer aggregierten Unterabfrage in der Projektion und dem Filter:
SELECT
p.name,
(SELECT VALUE AVG(q.quantity) FROM q IN p.inventory WHERE q.quantity > 10) AS averageInventory
FROM
products p
WHERE
(SELECT VALUE COUNT(1) FROM i IN p.inventory WHERE i.quantity > 10) >= 1
[
{
"name": "Snow coat",
"averageInventory": 35
}
]
Ein wesentlich besserer Ansatz zum Schreiben dieser Abfrage ist, die Unterabfrage in die JOIN-Anweisung zu integrieren und sowohl in der SELECT- als auch WHERE-Klausel auf den Unterabfragenalias zu verweisen. Diese Abfrage ist effizienter, da Sie die Unterabfrage nur in der JOIN-Anweisung und nicht in Projektion und Filter ausführen müssen.
SELECT
p.name,
inventoryData.inventoryAverage
FROM
products p
JOIN
(SELECT
COUNT(1) AS inventoryCount,
AVG(i.quantity) as inventoryAverage
FROM i IN p.inventory
WHERE i.quantity > 10) AS inventoryData
WHERE
inventoryData.inventoryCount >= 1
Die Abfrage-Engine von Azure Cosmos DB for NoSQL unterstützt EXISTS
-Ausdrücke. Dieser Ausdruck ist eine aggregierte skalare Unterabfrage, die in Azure Cosmos DB for NoSQL integriert ist. EXISTS
verwendet einen Unterabfragenausdruck und gibt true
zurück, wenn die Unterabfrage Zeilen zurückgibt. Andernfalls wird false
zurückgegeben.
Weil die Abfrage-Engine nicht zwischen booleschen Ausdrücken und anderen skalaren Ausdrücken unterscheidet, können Sie EXISTS
in SELECT
- und WHERE
-Klauseln verwenden. Dieses Verhalten unterscheidet sich von T-SQL, bei der ein boolescher Ausdruck nur auf Filter beschränkt wird.
Wenn die EXISTS
-Unterabfrage einen einzelnen Wert zurückgibt, der undefined
(nicht definiert) lautet, ergibt die Auswertung durch EXISTS
„false“. Betrachten Sie beispielsweise die folgende Abfrage, die nichts zurückgibt.
SELECT VALUE
undefined
Wenn Sie den Ausdruck EXISTS
und die vorhergehende Abfrage als Unterabfrage verwenden, gibt der Ausdruck false
zurück.
SELECT
EXISTS (SELECT VALUE undefined)
[
{
"$1": false
}
]
Wenn das Schlüsselwort „VALUE“ (Wert) in der vorhergehenden Unterabfrage ausgelassen wird, wird die Unterabfrage in ein Array mit einem einzelnen leeren Objekt ausgewertet.
SELECT
undefined
[
{}
]
An diesem Punkt wird der Ausdruck EXISTS
als true
ausgewertet, da das Objekt ({}
) technisch beendet wird.
SELECT
EXISTS (SELECT undefined)
[
{
"$1": true
}
]
Ein häufiger Anwendungsfall von ARRAY_CONTAINS
ist das Filtern eines Elements nach dem Vorhandensein eines Elements in einem Array. In diesem Fall prüfen wir, ob das Array tags
ein Element mit dem Namen „outerwear“ (Oberbekleidung) enthält.
SELECT
p.name,
p.tags
FROM
products p
WHERE
ARRAY_CONTAINS(p.tags, "outerwear")
Dieselbe Abfrage kann EXISTS
als alternative Option verwenden.
SELECT
p.name,
p.tags
FROM
products p
WHERE
EXISTS (SELECT VALUE t FROM t IN p.tags WHERE t = "outerwear")
Darüber hinaus kann ARRAY_CONTAINS
nur überprüfen, ob ein Wert einem beliebigen Element innerhalb eines Arrays entspricht. Wenn Sie komplexere Filter für Arrayeigenschaften benötigen, verwenden Sie stattdessen JOIN
.
Betrachten Sie dieses Beispielelement in einer Gruppe mit mehreren Elementen, die jeweils ein accessories
-Array enthalten.
{
"name": "Unobtani road bike",
"accessories": [
{
"name": "Front/rear tire",
"type": "tire",
"quantityOnHand": 5
},
{
"name": "9-speed chain",
"type": "chains",
"quantityOnHand": 25
},
{
"name": "Clip-in pedals",
"type": "pedals",
"quantityOnHand": 15
}
]
}
Betrachten Sie jetzt die folgende Abfrage, die innerhalb jedes Elements basierend auf den Eigenschaften type
und quantityOnHand
im Array filtert.
SELECT
p.name,
a.name AS accessoryName
FROM
products p
JOIN
a IN p.accessories
WHERE
a.type = "chains" AND
a.quantityOnHand >= 10
[
{
"name": "Unobtani road bike",
"accessoryName": "9-speed chain"
}
]
Für jedes der Elemente in der Auflistung wird ein Kreuzprodukt mit seinen Arrayelementen ausgeführt. Dieser JOIN
-Vorgang ermöglicht es, innerhalb des Arrays nach Eigenschaften zu filtern. Der Verbrauch von Anforderungseinheiten (RUs) bei dieser Abfrage ist jedoch erheblich. Wenn beispielsweise 1.000 Elemente in jedem Array 100 Elemente enthalten, wird es auf 1,000 x 100
(d. h. 100.000) Tupel erweitert.
Mithilfe von EXISTS
kann dieses ressourcenintensive Kreuzprodukt vermieden werden. In diesem nächsten Beispiel filtert die Abfrage nach Arrayelementen innerhalb der Unterabfrage EXISTS
. Wenn ein Arrayelement dem Filter entspricht, projizieren Sie es, und die Auswertung durch EXISTS
ergibt „true“.
SELECT VALUE
p.name
FROM
products p
WHERE
EXISTS (SELECT VALUE
a
FROM
a IN p.accessories
WHERE
a.type = "chains" AND
a.quantityOnHand >= 10)
[
"Unobtani road bike"
]
Abfragen können auch den Alias EXISTS
erstellen und auf den Alias in der Projektion verweisen:
SELECT
p.name,
EXISTS (SELECT VALUE
a
FROM
a IN p.accessories
WHERE
a.type = "chains" AND
a.quantityOnHand >= 10) AS chainAccessoryAvailable
FROM
products p
[
{
"name": "Unobtani road bike",
"chainAccessoryAvailable": true
}
]
Sie können mit dem ARRAY
-Ausdruck die Ergebnisse einer Abfrage als Array projizieren. Sie können diesen Ausdruck nur in der SELECT
-Klausel der Abfrage verwenden.
Nehmen wir für diese Beispiele an, dass es einen Container mit mindestens diesem Element gibt.
{
"name": "Radimer mountain bike",
"tags": [
{
"name": "road"
},
{
"name": "bike"
},
{
"name": "competitive"
}
]
}
In diesem ersten Beispiel wird der Ausdruck innerhalb der SELECT
-Klausel verwendet.
SELECT
p.name,
ARRAY (SELECT VALUE t.name FROM t in p.tags) AS tagNames
FROM
products p
[
{
"name": "Radimer mountain bike",
"tagNames": [
"road",
"bike",
"competitive"
]
}
]
Wie bei anderen Unterabfragen können beim ARRAY
-Ausdruck Filter verwendet werden.
SELECT
p.name,
ARRAY (SELECT VALUE t.name FROM t in p.tags) AS tagNames,
ARRAY (SELECT VALUE t.name FROM t in p.tags WHERE CONTAINS(t.name, "bike")) AS bikeTagNames
FROM
products p
[
{
"name": "Radimer mountain bike",
"tagNames": [
"road",
"bike",
"competitive"
],
"bikeTagNames": [
"bike"
]
}
]
Arrayausdrücke können in Unterabfragen auch nach der FROM
-Klausel stehen.
SELECT
p.name,
n.t.name AS nonBikeTagName
FROM
products p
JOIN
n IN (SELECT VALUE ARRAY(SELECT t FROM t in p.tags WHERE t.name NOT LIKE "%bike%"))
[
{
"name": "Radimer mountain bike",
"nonBikeTagName": "road"
},
{
"name": "Radimer mountain bike",
"nonBikeTagName": "competitive"
}
]