Unterabfragen – Abfragesprache in Cosmos DB (in Azure und Fabric)

Eine Unterabfrage ist eine Abfrage, die in einer anderen Abfrage innerhalb der Abfragesprache geschachtelt ist. Eine Unterabfrage wird auch als innere Abfrage oder innere SELECTAbfrage bezeichnet. Die Anweisung, die eine Unterabfrage enthält, wird in der Regel 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 einmal für jede Zeile ausgewertet, die die äußere Abfrage verarbeitet.
• Nicht korreliert: Eine Von der äußeren Abfrage unabhängige Unterabfrage. Sie kann eigenständig ausgeführt werden, ohne sich auf die äußere Abfrage zu verlassen.

Unterabfragen können basierend auf der Anzahl der zurückgegebenen Zeilen und Spalten weiter klassifiziert werden. Es gibt drei Typen:

• Tabelle: Gibt mehrere Zeilen und mehrere Spalten zurück.
• Mehrfachwert: Gibt mehrere Zeilen und eine einzelne Spalte zurück.
• Skalar: Gibt eine einzelne Zeile und eine einzelne Spalte zurück.

Abfragen in der Abfragesprache geben immer eine einzelne Spalte (entweder einen einfachen Wert oder ein komplexes Element) zurück. Daher gelten nur mehrwertige und skalare Unterabfragen. Sie können eine mehrwertige Unterabfrage nur in der FROM Klausel als relationalen Ausdruck verwenden. Sie können eine skalare Unterabfrage als Skalarausdruck in der SELECT Klausel oder WHERE als relationaler Ausdruck in der FROM Klausel verwenden.

Mehrwertige Unterabfragen

Mehrwertige Unterabfragen geben einen Satz von Elementen zurück und werden immer innerhalb der FROM Klausel verwendet. Sie werden für Folgendes verwendet:

• Optimieren von JOIN Ausdrücken (Self-Join).
• Bewerten sie teure Ausdrücke einmal, und verweisen Sie mehrmals auf sie.

Optimieren von Self-Join-Ausdrücken

Mehrwertige Unterabfragen können Ausdrücke optimieren JOIN , indem Prädikate nach jedem Auswahl-n-Ausdruck und nicht nach allen Kreuzverknungen in der WHERE Klausel verschoben werden.

Betrachten Sie die folgende Abfrage:

SELECT VALUE
  COUNT(1)
FROM
  products p
JOIN 
  t in p.tags
JOIN
  s in p.sizes
JOIN
  c in p.colors
WHERE
  t.key IN ("fabric", "material") AND
  s["order"] >= 3 AND
  c LIKE "%gray%"

Bei dieser Abfrage stimmt der Index mit einem Element überein, das ein Tag mit einer key oder fabricmaterial, mindestens einer Größe mit einem order Wert größer als *3 und mindestens einer Farbe mit gray einer Teilzeichenfolge enthält. Der JOIN Ausdruck führt hier das Produkt aller Elemente von tags, sizesund colors Arrays für jedes übereinstimmende Element aus, bevor ein Filter angewendet wird.

Die WHERE Klausel wendet dann das Filterprädikat auf jeden $<c, t, n, s>$ Tupel an. Wenn beispielsweise ein übereinstimmende Element zehn Elemente in jedem der drei Arrays enthält, wird es mit dieser Formel auf 1.000 Tupel erweitert:

$1 x 10 x 10 x 10$$

Die Verwendung von Unterabfragen hier kann beim Filtern verknüpfter Arrayelemente helfen, bevor sie mit dem nächsten Ausdruck verknüpft werden.

Diese Abfrage entspricht der vorherigen Abfrage, verwendet jedoch Unterabfragen:

SELECT VALUE
  COUNT(1)
FROM
  products p
JOIN 
  (SELECT VALUE t FROM t IN p.tags WHERE t.key IN ("fabric", "material"))
JOIN 
  (SELECT VALUE s FROM s IN p.sizes WHERE s["order"] >= 3)
JOIN 
  (SELECT VALUE c FROM c in p.colors WHERE c LIKE "%gray%")

Gehen Sie davon aus, dass nur ein Element im Kategorienarray dem Filter entspricht, und es gibt fünf Elemente für Mengen- und Bestandsarrays. Der JOIN Ausdruck wird dann mit dieser Formel auf 25 Tupel erweitert, im Gegensatz zu 1.000 Elementen in der ersten Abfrage:

$1 x 1 x 5 x 5$$

Einmal auswerten und mehrfach referenzieren

Unterabfragen können dazu beitragen, Abfragen mit teuren Ausdrücken wie benutzerdefinierten Funktionen (USER-Defined Functions, UDFs), komplexen Zeichenfolgen oder arithmetischen Ausdrücken zu optimieren. Sie können eine Unterabfrage zusammen mit einem JOIN Ausdruck verwenden, um den Ausdruck einmal auszuwerten, aber mehrmals darauf zu verweisen.

Diese Beispielabfrage berechnet den Preis mit einer Ergänzung von 25% mehrfach in der Abfrage.

SELECT VALUE {
  subtotal: p.price,
  total: (p.price * 1.25)
}
FROM
  products p
WHERE
  (p.price * 1.25) < 22.25

Hier ist eine entsprechende Abfrage, die die Berechnung nur einmal ausführt:

SELECT VALUE {
  subtotal: p.price,
  total: totalPrice
}
FROM
  products p
JOIN
  (SELECT VALUE p.price * 1.25) totalPrice
WHERE
  totalPrice < 22.25

[
  {
    "subtotal": 15,
    "total": 18.75
  },
  {
    "subtotal": 10,
    "total": 12.5
  },
  ...
]

Tipp

Beachten Sie das produktübergreifende Verhalten von JOIN Ausdrücken. Wenn der Ausdruck ausgewertet werden undefinedkann, sollten Sie sicherstellen, dass der JOIN Ausdruck immer eine einzelne Zeile erzeugt, indem sie ein Objekt aus der Unterabfrage und nicht direkt den Wert zurückgibt.

Relationale Verknüpfung mit externen Referenzdaten imitieren

Möglicherweise müssen Sie häufig auf statische Daten verweisen, die sich selten ändern, z. B. Maßeinheiten. Es ist ideal, statische Daten für jedes Element in einer Abfrage nicht zu duplizieren. Vermeiden Sie diese Duplizierungsspeicherung und verbessern Sie die Schreibleistung, indem sie die größe der einzelnen Elemente verkleinert. Sie können eine Unterabfrage verwenden, um die Inner-Join-Semantik mit einer Sammlung statischer Verweisdaten nachzuahmen.

Betrachten Sie beispielsweise diesen Satz von Maßen, die die Länge eines Kleidungsstücks darstellen:

Größe	Length	Einheiten
xs	63.5	cm
s	64.5	cm
m	66.0	cm
l	67.5	cm
xl	69.0	cm
xxl	70.5	cm

Die folgende Abfrage imitiert die Verknüpfung mit diesen Daten, sodass Sie den Namen der Einheit zur Ausgabe hinzufügen:

SELECT
  p.name,
  p.subCategory,
  s.description AS size,
  m.length,
  m.unit
FROM
  products p
JOIN
  s IN p.sizes
JOIN m IN (
  SELECT VALUE [
    {size: 'xs', length: 63.5, unit: 'cm'},
    {size: 's', length: 64.5, unit: 'cm'},
    {size: 'm', length: 66, unit: 'cm'},
    {size: 'l', length: 67.5, unit: 'cm'},
    {size: 'xl', length: 69, unit: 'cm'},
    {size: 'xxl', length: 70.5, unit: 'cm'}
  ]
)
WHERE
  s.key = m.size

Skalare Unterabfragen

Ein skalarer Unterabfrageausdruck ist eine Unterabfrage, die zu einem einzelnen Wert ausgewertet wird. Der Wert des skalaren Unterabfrageausdrucks ist der Wert der Projektion (SELECT Klausel) der Unterabfrage. Sie können an vielen Stellen einen skalaren Unterabfrageausdruck verwenden, an dem ein skalarer Ausdruck gültig ist. Sie können z. B. eine skalare Unterabfrage in jedem Ausdruck in den SELECT beiden Ausdrücken und WHERE Klauseln verwenden.

Die Verwendung einer skalaren Unterabfrage trägt nicht immer dazu bei, Ihre Abfrage zu optimieren. Beispielsweise bietet das Übergeben einer skalaren Unterabfrage als Argument an ein System oder benutzerdefinierte Funktionen keinen Vorteil beim Reduzieren des Ressourceneinheitsverbrauchs (RESOURCE Unit, RU) oder der Latenz.

Skalare Unterabfragen können weiter klassifiziert werden als:

• Skalare Unterabfragen für einfache Ausdrücke
• Aggregieren von skalaren Unterabfragen

Skalare Unterabfragen für einfache Ausdrücke

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 diese triviale Abfrage als erstes Beispiel.

SELECT
  1 AS a,
  2 AS b

Sie können diese Abfrage neu schreiben, indem Sie eine skalare Unterabfrage mit einfachen Ausdrücken verwenden.

SELECT
  (SELECT VALUE 1) AS a, 
  (SELECT VALUE 2) AS b

Beide Abfragen erzeugen dieselbe Ausgabe.

[
  {
    "a": 1,
    "b": 2
  }
]

Diese nächste Beispielabfrage verkettet den eindeutigen Bezeichner mit einem Präfix als skalare Unterabfrage mit einfachem Ausdruck.

SELECT 
  (SELECT VALUE CONCAT('ID-', p.id)) AS internalId
FROM
  products p

In diesem Beispiel wird eine skalare Unterabfrage für einfache Ausdrücke verwendet, um nur die relevanten Felder für jedes Element zurückzugeben. Die Abfrage gibt etwas für jedes Element aus, enthält aber nur das projizierte Feld, wenn sie den Filter innerhalb der Unterabfrage erfüllt.

SELECT
  p.id,
  (SELECT p.name WHERE CONTAINS(p.name, "Shoes")).name
FROM
  products p

[
  {
    "id": "00000000-0000-0000-0000-000000004041",
    "name": "Remdriel Shoes"
  },
  {
    "id": "00000000-0000-0000-0000-000000004322"
  },
  {
    "id": "00000000-0000-0000-0000-000000004055"
  }
]

Aggregieren von skalaren Unterabfragen

Eine Aggregatskalierungsunterabfrage ist eine Unterabfrage, die eine Aggregatfunktion in der Projektion oder dem Filter enthält, die zu einem einzelnen Wert ausgewertet wird.

Betrachten Sie als erstes Beispiel ein Element mit den folgenden Feldern.

[
  {
    "name": "Blators Snowboard Boots",
    "colors": [
      "turquoise",
      "cobalt",
      "jam",
      "galliano",
      "violet"
    ],
    "sizes": [ ... ],
    "tags": [ ... ]
  }
]

Hier ist eine Unterabfrage mit einem einzelnen Aggregatfunktionsausdruck in der Projektion. Diese Abfrage zählt alle Tags für jedes Element.

SELECT
  p.name,
  (SELECT VALUE COUNT(1) FROM c IN p.colors) AS colorsCount
FROM
  products p
WHERE
  p.id = "00000000-0000-0000-0000-000000004389"

[
  {
    "name": "Blators Snowboard Boots",
    "colorsCount": 5
  }
]

Dies ist die gleiche Unterabfrage mit einem Filter.

SELECT
  p.name,
  (SELECT VALUE COUNT(1) FROM c IN p.colors) AS colorsCount,
  (SELECT VALUE COUNT(1) FROM c IN p.colors WHERE c LIKE "%t") AS colorsEndsWithTCount
FROM
  products p

[
  {
    "name": "Blators Snowboard Boots",
    "colorsCount": 5,
    "colorsEndsWithTCount": 2
  }
]

Hier ist eine weitere Unterabfrage mit mehreren Aggregatfunktionsausdrücken:

SELECT
  p.name,
  (SELECT VALUE COUNT(1) FROM c IN p.colors) AS colorsCount,
  (SELECT VALUE COUNT(1) FROM s in p.sizes) AS sizesCount,
  (SELECT VALUE COUNT(1) FROM t IN p.tags) AS tagsCount
FROM
  products p

[
  {
    "name": "Blators Snowboard Boots",
    "colorsCount": 5,
    "sizesCount": 7,
    "tagsCount": 2
  }
]

Schließlich ist hier eine Abfrage mit einer aggregierten Unterabfrage sowohl in der Projektion als auch im Filter:

SELECT
  p.name,
  (SELECT VALUE COUNT(1) FROM s in p.sizes WHERE s.description LIKE "%Small") AS smallSizesCount,
  (SELECT VALUE COUNT(1) FROM s in p.sizes WHERE s.description LIKE "%Large") AS largeSizesCount
FROM
  products p
WHERE
  (SELECT VALUE COUNT(1) FROM c IN p.colors) >= 5

Eine optimale Möglichkeit zum Schreiben dieser Abfrage besteht darin, in der Unterabfrage zu verbinden und auf den Unterabfragealias in den SELECT- und WHERE-Klauseln zu verweisen. Diese Abfrage ist effizienter, da Sie die Unterabfrage nur innerhalb der Join-Anweisung ausführen müssen und nicht sowohl in der Projektion als auch im Filter.

SELECT
  p.name,
  colorCount,
  smallSizesCount,
  largeSizesCount
FROM
  products p
JOIN
  (SELECT VALUE COUNT(1) FROM c IN p.colors) AS colorCount
JOIN
  (SELECT VALUE COUNT(1) FROM s in p.sizes WHERE s.description LIKE "%Small") AS smallSizesCount
JOIN
  (SELECT VALUE COUNT(1) FROM s in p.sizes WHERE s.description LIKE "%Large") AS largeSizesCount
WHERE
  colorCount >= 5 AND
  largeSizesCount > 0 AND
  smallSizesCount > 0

EXISTS-Ausdruck

Die Abfragesprache unterstützt EXISTS Ausdrücke. Dieser Ausdruck ist eine aggregierte skalare Unterabfrage, die in die Abfragesprache integriert ist. EXISTS verwendet einen Unterabfrageausdruck und gibt zurück, wenn die Unterabfrage Zeilen zurückgibt true . Andernfalls wird falsezurückgegeben.

Da das Abfragemodul nicht zwischen booleschen Ausdrücken und anderen skalaren Ausdrücken unterscheidet, können Sie sowohl SELECT in Beiden als WHERE auch in Klauseln verwendenEXISTS. Dieses Verhalten unterscheidet sich im Gegensatz zu T-SQL, bei dem ein boolescher Ausdruck nur auf Filter beschränkt ist.

Wenn die EXISTS Unterabfrage einen einzelnen Wert zurückgibt, undefinedEXISTS der als falsch ausgewertet wird. Betrachten Sie beispielsweise die folgende Abfrage, die nichts zurückgibt.

SELECT VALUE
  undefined

Wenn Sie den EXISTS Ausdruck und die vorangehende Abfrage als Unterabfrage verwenden, wird der Ausdruck zurückgegeben false.

SELECT VALUE
  EXISTS (SELECT VALUE undefined)

[
  false
]

Wenn das SCHLÜSSELwort VALUE in der vorhergehenden Unterabfrage weggelassen wird, wird die Unterabfrage mit einem einzigen leeren Objekt zu einem Array ausgewertet.

SELECT
  undefined

[
  {}
]

Zu diesem Zeitpunkt wird der EXISTS Ausdruck ausgewertet, da true das Objekt ({}) technisch beendet wird.

SELECT VALUE
  EXISTS (SELECT undefined)

[
  true
]

Ein gängiger Anwendungsfall ARRAY_CONTAINS besteht darin, ein Element nach dem Vorhandensein eines Elements in einem Array zu filtern. In diesem Fall überprüfen wir, ob das tags Array ein Element mit dem Namen "Outerwear" enthält.

SELECT
  p.name,
  p.colors
FROM
  products p
WHERE
  ARRAY_CONTAINS(p.colors, "cobalt")

Dieselbe Abfrage kann als alternative Option verwendet werden EXISTS .

SELECT
  p.name,
  p.colors
FROM
  products p
WHERE
  EXISTS (SELECT VALUE c FROM c IN p.colors WHERE c = "cobalt")

Darüber hinaus kann nur überprüft werden, ARRAY_CONTAINS ob ein Wert einem beliebigen Element innerhalb eines Arrays entspricht. Wenn Sie komplexere Filter für Arrayeigenschaften benötigen, verwenden Sie JOIN stattdessen.

Betrachten Sie dieses Beispielelement in einem Satz mit mehreren Elementen, die jeweils ein accessories Array enthalten.

[
  {
    "name": "Cosmoxy Pack",
    "tags": [
      {
        "key": "fabric",
        "value": "leather",
        "description": "Leather"
      },
      {
        "key": "volume",
        "value": "68-gal",
        "description": "6.8 Gal"
      }
    ]
  }
]

Betrachten Sie nun die folgende Abfrage, die basierend auf den type Und quantityOnHand Eigenschaften im Array innerhalb jedes Elements filtert.

SELECT
  p.name,
  t.description AS tag
FROM
  products p
JOIN
  t in p.tags
WHERE
  t.key = "fabric" AND
  t["value"] = "leather"

[
  {
    "name": "Cosmoxy Pack",
    "tag": "Leather"
  }
]

Für jedes element in der Sammlung wird ein produktübergreifendes Element mit seinen Arrayelementen ausgeführt. Dieser JOIN Vorgang ermöglicht das Filtern nach Eigenschaften innerhalb des Arrays. Der RU-Verbrauch dieser Abfrage ist jedoch erheblich. Wenn beispielsweise 1.000 Elemente 100 Elemente in jedem Array enthalten, wird sie mit dieser Formel auf 100.000 Tupel erweitert:

$1.000 x 100$$

Die Verwendung EXISTS hilft dabei, dieses teure produktübergreifende Produkt zu vermeiden. Im nächsten Beispiel filtert die Abfrage nach Arrayelementen innerhalb der EXISTS Unterabfrage. Wenn ein Arrayelement mit dem Filter übereinstimmt, projizieren Sie es und EXISTS ausgewertet auf "true".

SELECT VALUE
  p.name
FROM
  products p
WHERE
  EXISTS (
    SELECT VALUE
      t
    FROM
      t IN p.tags
    WHERE
      t.key = "fabric" AND
      t["value"] = "leather"
  )

[
  "Cosmoxy Pack"
]

Abfragen dürfen auch Aliase ausführen EXISTS und auf den Alias in der Projektion verweisen:

SELECT
  p.name,
  EXISTS (
    SELECT VALUE
      t
    FROM
      t IN p.tags
    WHERE
      t.key = "fabric" AND
      t["value"] = "leather"
  ) AS containsFabricLeatherTag
FROM
  products p

[
  {
    "name": "Cosmoxy Pack",
    "containsFabricLeatherTag": true
  }
]

ARRAY-Ausdruck

Sie können den ARRAY Ausdruck verwenden, um die Ergebnisse einer Abfrage als Array zu projizieren. Sie können diesen Ausdruck nur innerhalb der SELECT Klausel der Abfrage verwenden.

Nehmen wir für diese Beispiele an, dass es einen Container mit mindestens diesem Element gibt.

[
  {
    "name": "Menti Sandals",
    "sizes": [
      {
        "key": "5"
      },
      {
        "key": "6"
      },
      {
        "key": "7"
      },
      {
        "key": "8"
      },
      {
        "key": "9"
      }
    ]
  }
]

In diesem ersten Beispiel wird der Ausdruck innerhalb der SELECT Klausel verwendet.

SELECT
  p.name,
  ARRAY (
    SELECT VALUE
      s.key
    FROM
      s IN p.sizes
  ) AS sizes
FROM
  products p
WHERE
  p.name = "Menti Sandals"

[
  {
    "name": "Menti Sandals",
    "sizes": [
      "5",
      "6",
      "7",
      "8",
      "9"
    ]
  }
]

Wie bei anderen Unterabfragen sind Filter mit dem ARRAY Ausdruck möglich.

SELECT
  p.name,
  ARRAY (
    SELECT VALUE
      s.key
    FROM
      s IN p.sizes
    WHERE
      STRINGTONUMBER(s.key) <= 6
  ) AS smallSizes,
  ARRAY (
    SELECT VALUE
      s.key
    FROM
      s IN p.sizes
    WHERE
      STRINGTONUMBER(s.key) >= 9
  ) AS largeSizes
FROM
  products p
WHERE
  p.name = "Menti Sandals"

[
  {
    "name": "Menti Sandals",
    "smallSizes": [
      "5",
      "6"
    ],
    "largeSizes": [
      "9"
    ]
  }
]

Arrayausdrücke können auch nach der FROM Klausel in Unterabfragen kommen.

SELECT
  p.name,
  z.s.key AS sizes
FROM
  products p
JOIN
  z IN (
    SELECT VALUE
      ARRAY (
        SELECT
          s
        FROM
          s IN p.sizes
        WHERE
          STRINGTONUMBER(s.key) <= 8
      )
  )

[
  {
    "name": "Menti Sandals",
    "sizes": "5"
  },
  {
    "name": "Menti Sandals",
    "sizes": "6"
  },
  {
    "name": "Menti Sandals",
    "sizes": "7"
  },
  {
    "name": "Menti Sandals",
    "sizes": "8"
  }
]

Verwandte Inhalte

• Überprüfen der Abfragesprache
• Erste Schritte mit JSON in der Abfragesprache
• Durchführen von Selbstverknnungen
• Erkunden von Systemfunktionen