Subquery's - Querytaal in Cosmos DB (in Azure en Fabric)

Een subquery is een query die is genest binnen een andere query in de querytaal. Een subquery wordt ook wel een binnenste query of binnenste SELECTquery genoemd. De instructie die een subquery bevat, wordt meestal een buitenste query genoemd.

Typen subquery's

Er zijn twee hoofdtypen subquery's:

• Gecorreleerd: een subquery die verwijst naar waarden uit de buitenste query. De subquery wordt eenmaal geëvalueerd voor elke rij die door de buitenste query wordt verwerkt.
• Niet-gecorreleerd: een subquery die onafhankelijk is van de buitenste query. Deze kan zelfstandig worden uitgevoerd zonder dat deze afhankelijk is van de buitenste query.

Subquery's kunnen verder worden geclassificeerd op basis van het aantal rijen en kolommen dat ze retourneren. Er zijn drie typen:

• Tabel: Retourneert meerdere rijen en meerdere kolommen.
• Meerdere waarden: retourneert meerdere rijen en één kolom.
• Scalar: retourneert één rij en één kolom.

Query's in de querytaal retourneren altijd één kolom (een eenvoudige waarde of een complex item). Daarom zijn alleen subquery's met meerdere waarden en scalaire subquery's van toepassing. U kunt een subquery met meerdere waarden alleen in de FROM component gebruiken als een relationele expressie. U kunt een scalaire subquery gebruiken als een scalaire expressie in de SELECT of WHERE component, of als een relationele expressie in de FROM component.

Subquery's met meerdere waarden

Subquery's met meerdere waarden retourneren een set items en worden altijd binnen de FROM component gebruikt. Ze worden gebruikt voor:

• JOIN Expressies (self-join) optimaliseren.
• Dure expressies één keer evalueren en meerdere keren verwijzen.

Self-join-expressies optimaliseren

Subquery's met meerdere waarden kunnen expressies optimaliseren JOIN door predicaten na elke select-many-expressie te pushen in plaats van na alle cross-joins in de WHERE component.

Houd rekening met de volgende query:

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%"

Voor deze query komt de index overeen met elk item met een tag met een van een key ofmaterialfabric, ten minste één grootte met een order waarde groter dan *drie en ten minste één kleur met gray een subtekenreeks. De JOIN expressie hier voert het kruisproduct uit van alle items van tags, sizesen colors matrices voor elk overeenkomend item voordat een filter wordt toegepast.

De WHERE component past vervolgens het filterpredicaat toe op elke $<c-, t-, n-s>$-tuple. Als een overeenkomend item bijvoorbeeld tien items in elk van de drie matrices bevat, wordt het uitgevouwen tot 1000 tuples met behulp van deze formule:

$$1 x 10 x 10 x 10$$

Het gebruik van subquery's hier kan helpen bij het filteren van gekoppelde matrixitems voordat u met de volgende expressie gaat samenvoegen.

Deze query is gelijk aan de voorgaande, maar gebruikt subquery's:

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%")

Stel dat slechts één item in de tagsmatrix overeenkomt met het filter en dat er vijf items zijn voor zowel hoeveelheid als voorraadmatrices. De JOIN expressie wordt vervolgens uitgebreid tot 25 tuples met behulp van deze formule in plaats van 1000 items in de eerste query:

$$1 x 1 x 5 x 5$$

Eenmaal evalueren en vaak verwijzen

Met subquery's kunt u query's optimaliseren met dure expressies, zoals door de gebruiker gedefinieerde functies (UDF's), complexe tekenreeksen of rekenkundige expressies. U kunt een subquery samen met een JOIN expressie gebruiken om de expressie eenmaal te evalueren, maar vaak ernaar te verwijzen.

Met deze voorbeeldquery wordt de prijs berekend met een aanvulling van 25% veelvouden in de query.

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

Hier volgt een equivalente query waarmee de berekening slechts eenmaal wordt uitgevoerd:

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
  },
  ...
]

Aanbeveling

Houd rekening met het gedrag van JOIN expressies voor meerdere producten. Als de expressie kan evalueren undefined, moet u ervoor zorgen dat de JOIN expressie altijd één rij produceert door een object uit de subquery te retourneren in plaats van de waarde rechtstreeks.

Relationele join nabootsen met externe referentiegegevens

Mogelijk moet u vaak verwijzen naar statische gegevens die zelden veranderen, zoals maateenheden. Het is ideaal om statische gegevens niet te dupliceren voor elk item in een query. Als u deze duplicatie vermijdt, bespaart u opslag en verbetert u de schrijfprestaties door de afzonderlijke itemgrootte kleiner te houden. U kunt een subquery gebruiken om semantiek van inner-joins na te bootsen met een verzameling statische referentiegegevens.

Denk bijvoorbeeld aan deze reeks metingen die de lengte van een kledingstuk vertegenwoordigen:

Grootte	Length	Units
xs	63.5	cm
s	64.5	cm
m	66.0	cm
l	67.5	cm
xl	69.0	cm
xxl	70.5	cm

Met de volgende query wordt het samenvoegen van deze gegevens nagebootst, zodat u de naam van de eenheid toevoegt aan de uitvoer:

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

Scalaire subquery's

Een scalaire subquery-expressie is een subquery die één waarde oplevert. De waarde van de scalaire subquery-expressie is de waarde van de projectie (SELECT component) van de subquery. U kunt een scalaire subquery-expressie gebruiken op veel plaatsen waar een scalaire expressie geldig is. U kunt bijvoorbeeld een scalaire subquery gebruiken in elke expressie in zowel de SELECT als WHERE de component.

Het gebruik van een scalaire subquery helpt niet altijd om uw query te optimaliseren. Het doorgeven van een scalaire subquery als argument aan een systeem of door de gebruiker gedefinieerde functies biedt bijvoorbeeld geen voordeel bij het verminderen van ru-verbruik (Resource Unit) of latentie.

Scalaire subquery's kunnen verder worden geclassificeerd als:

• Scalaire scalaire subquery's voor eenvoudige expressies
• Scalaire subquery's aggregeren

Scalaire scalaire subquery's voor eenvoudige expressies

Een scalaire scalaire subquery voor eenvoudige expressies is een gecorreleerde subquery met een SELECT component die geen statistische expressies bevat. Deze subquery's bieden geen optimalisatievoordelen omdat de compiler deze converteert naar een grotere eenvoudige expressie. Er is geen gecorreleerde context tussen de binnenste en buitenste query's.

Bekijk deze triviale query als eerste voorbeeld.

SELECT
  1 AS a,
  2 AS b

U kunt deze query opnieuw schrijven met behulp van een scalaire scalaire subquery voor eenvoudige expressies.

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

Beide query's produceren dezelfde uitvoer.

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

Met deze volgende voorbeeldquery wordt de unieke id samengevoegd met een voorvoegsel als scalaire scalaire subquery voor eenvoudige expressies.

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

In dit voorbeeld wordt een scalaire scalaire scalaire subquery gebruikt om alleen de relevante velden voor elk item te retourneren. De query voert iets uit voor elk item, maar bevat alleen het verwachte veld als het voldoet aan het filter in de subquery.

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

Scalaire subquery's aggregeren

Een geaggregeerde scalaire subquery is een subquery met een statistische functie in de projectie of het filter die resulteert in één waarde.

Bekijk als eerste voorbeeld een item met de volgende velden.

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

Hier volgt een subquery met één statistische functie-expressie in de projectie. Met deze query worden alle tags voor elk item geteld.

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

Hier volgt dezelfde subquery met een 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 volgt een andere subquery met meerdere expressies voor statistische functies:

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

Ten slotte ziet u hier een query met een geaggregeerde subquery in zowel de projectie als het 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

Een optimale manier om deze query te schrijven, is door deel te nemen aan de subquery en te verwijzen naar de subqueryalias in zowel de SELECT- als WHERE-componenten. Deze query is efficiënter omdat u de subquery alleen binnen de join-instructie moet uitvoeren en niet in zowel de projectie als het 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-expressie

De querytaal ondersteunt EXISTS expressies. Deze expressie is een geaggregeerde scalaire subquery die is ingebouwd in de querytaal. EXISTS gebruikt een subquery-expressie en retourneert true als de subquery rijen retourneert. Anders wordt falsegeretourneerd.

Omdat de query-engine geen onderscheid maakt tussen booleaanse expressies en andere scalaire expressies, kunt u in beide SELECT en WHERE componenten gebruikenEXISTS. Dit gedrag is in tegenstelling tot T-SQL, waarbij een Boole-expressie wordt beperkt tot alleen filters.

Als de EXISTS subquery één waarde undefinedretourneert, EXISTS resulteert dit in onwaar. Denk bijvoorbeeld aan de volgende query die niets retourneert.

SELECT VALUE
  undefined

Als u de EXISTS expressie en de voorgaande query als subquery gebruikt, retourneert falsede expressie.

SELECT VALUE
  EXISTS (SELECT VALUE undefined)

[
  false
]

Als het trefwoord VALUE in de voorgaande subquery wordt weggelaten, resulteert de subquery in een matrix met één leeg object.

SELECT
  undefined

[
  {}
]

Op dat moment wordt de EXISTS expressie geëvalueerd true omdat het object ({}) technisch wordt afgesloten.

SELECT VALUE
  EXISTS (SELECT undefined)

[
  true
]

Een veelvoorkomend gebruiksvoorbeeld is het filteren van ARRAY_CONTAINS een item op basis van het bestaan van een item in een matrix. In dit geval controleren we of de matrix een item bevat met de tags naam 'outerwear'.

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

Dezelfde query kan worden gebruikt EXISTS als een alternatieve optie.

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

ARRAY_CONTAINS Daarnaast kan alleen worden gecontroleerd of een waarde gelijk is aan een element in een matrix. Als u complexere filters voor matrixeigenschappen nodig hebt, gebruikt JOIN u in plaats daarvan.

Bekijk dit voorbeelditem in een set met meerdere items die elk een accessories matrix bevatten.

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

Bekijk nu de volgende query die filtert op basis van de type en quantityOnHand eigenschappen in de matrix binnen elk item.

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

Voor elk van de items in de verzameling wordt een kruisproduct uitgevoerd met de bijbehorende matrixelementen. Met deze JOIN bewerking kunt u filteren op eigenschappen binnen de matrix. Het RU-verbruik van deze query is echter aanzienlijk. Als er bijvoorbeeld 1000 items in elke matrix 100 items bevatten, wordt deze uitgebreid tot 100.000 tuples met behulp van deze formule:

$$1.000 x 100$$

Het gebruik EXISTS helpt om dit dure cross-product te voorkomen. In dit volgende voorbeeld filtert de query op matrixelementen in de EXISTS subquery. Als een matrixelement overeenkomt met het filter, projecteert u het en EXISTS resulteert u in waar.

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

Query's mogen ook aliasen EXISTS en verwijzen naar de alias in de projectie:

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

MATRIX-expressie

U kunt de ARRAY expressie gebruiken om de resultaten van een query als matrix te projecteren. U kunt deze expressie alleen gebruiken binnen de SELECT component van de query.

Voor deze voorbeelden gaan we ervan uit dat er een container is met ten minste dit item.

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

In dit eerste voorbeeld wordt de expressie in de SELECT component gebruikt.

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

Net als bij andere subquery's zijn filters met de ARRAY expressie mogelijk.

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

Matrixexpressies kunnen ook na de FROM component in subquery's komen.

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

Verwante inhoud

• De querytaal controleren
• Aan de slag met JSON in de querytaal
• Self-joins uitvoeren
• Systeemfuncties verkennen