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.

Typen von Unterabfragen

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

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.

Optimieren von Self-Join-Ausdrücken

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.

Einmaliges Auswerten und häufiges Verweisen

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.

JOIN-Imitieren mit externen Verweisdaten

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

Skalare Unterabfragen

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

Skalare Unterabfragen mit einfachem Ausdruck

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"
  }
]

Aggregierte skalare Unterabfragen

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

EXISTS-Ausdruck

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 falsezurü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
  }
]

ARRAY-Ausdruck

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"
  }
]

Zugehöriger Inhalt

• JOIN-Klausel
• Konstanten
• Schlüsselwörter