Subquery's in Azure Cosmos DB for NoSQL
VAN TOEPASSING OP: NoSQL
Een subquery is een query die is genest in een andere query in Azure Cosmos DB for NoSQL. Een subquery wordt ook wel een binnenste query of binnenste SELECT
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. Het kan zelfstandig worden uitgevoerd zonder afhankelijk te zijn van de buitenste query.
Notitie
Azure Cosmos DB ondersteunt alleen gecorreleerde subquery's.
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: geeft als resultaat meerdere rijen en één kolom.
- Scalair: retourneert één rij en één kolom.
Query's in Azure Cosmos DB for NoSQL 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
component of WHERE
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:
- Expressies (self-join) optimaliseren
JOIN
. - Dure expressies één keer evalueren en meerdere keren verwijzen.
Expressies voor zelfdeelname 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.
Laten we nu eens naar deze query kijken:
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
Voor deze query komt de index overeen met name
elk item met een tag 'winter' of 'herfst', ten minste één quantity
tussen nul en tien, en ten minste één magazijn met de backstock
false
. Met JOIN
de expressie hier wordt het kruisproduct uitgevoerd van alle items van tags
, onHandQuantities
en warehouseStock
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 had, wordt het uitgebreid naar 1 x 10 x 10 x 10
(dat wil gezegd, 1000) tuples. Het gebruik van subquery's hier kan helpen bij het filteren van gekoppelde matrixitems voordat u deze met de volgende expressie samenvoegt.
Deze query is gelijk aan de voorgaande query, maar maakt gebruik van subquery's:
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)
Stel dat slechts één item in de tagsmatrix overeenkomt met het filter en dat er vijf items zijn voor zowel voedingsstoffen als porties matrices. De JOIN
expressies worden vervolgens uitgebreid naar 1 x 1 x 5 x 5
(25) items, in plaats van 1000 items in de eerste query.
Eén keer evalueren en meerdere keren verwijzen
Subquery's kunnen helpen bij het optimaliseren van query's 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 er meerdere keren naar verwijzen.
Stel dat u de volgende UDF (getTotalWithTax
) hebt gedefinieerd.
function getTotalWithTax(subTotal){
return subTotal * 1.25;
}
Met de volgende query wordt de UDF getTotalWithTax
meerdere keren uitgevoerd:
SELECT VALUE {
subtotal: p.price,
total: udf.getTotalWithTax(p.price)
}
FROM
products p
WHERE
udf.getTotalWithTax(p.price) < 22.25
Hier volgt een equivalente query die de UDF slechts één keer uitvoert:
SELECT VALUE {
subtotal: p.price,
total: totalPrice
}
FROM
products p
JOIN
(SELECT VALUE udf.getTotalWithTax(p.price)) totalPrice
WHERE
totalPrice < 22.25
Tip
Houd rekening met het gedrag van JOIN
expressies voor meerdere producten. Als de UDF-expressie kan worden geëvalueerd naar undefined
, moet u ervoor zorgen dat de JOIN
expressie altijd één rij produceert door een object uit de subquery te retourneren in plaats van rechtstreeks de waarde.
Join nabootsen met externe referentiegegevens
Mogelijk moet u vaak verwijzen naar statische gegevens die zelden veranderen, zoals maateenheden. Het is ideaal om geen statische gegevens 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 inner-join-semantiek na te bootsen met een verzameling statische referentiegegevens.
Neem bijvoorbeeld deze set metingen:
Naam | Multiplier | Basiseenheid | |
---|---|---|---|
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 |
De volgende query bootst het samenvoegen met deze gegevens na, zodat u de naam van de eenheid aan de uitvoer toevoegt:
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
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 - als - SELECT
WHERE
componenten.
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 het verbruik of de latentie van resource-eenheden (RU).
Scalaire subquery's kunnen verder worden geclassificeerd als:
- Scalaire subquery's voor eenvoudige expressies
- Scalaire subquery's aggregeren
Scalaire subquery's voor eenvoudige expressies
Een scalaire subquery voor eenvoudige expressies is een gecorreleerde subquery met een SELECT
component die geen aggregatieexpressies bevat. Deze subquery's bieden geen optimalisatievoordelen omdat de compiler ze converteert naar één grotere eenvoudige expressie. Er is geen gecorreleerde context tussen de binnenste en buitenste query's.
Als eerste voorbeeld kunt u deze triviale query gebruiken.
SELECT
1 AS a,
2 AS b
U kunt deze query herschrijven met behulp van een 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
}
]
In deze volgende voorbeeldquery wordt de unieke id samengevoegd met een voorvoegsel als een scalaire subquery met eenvoudige expressies.
SELECT
(SELECT VALUE Concat('ID-', p.id)) AS internalId
FROM
products p
In dit voorbeeld wordt een scalaire subquery simple-expression gebruikt om alleen de relevante velden voor elk item te retourneren. De query voert iets uit voor elk item, maar bevat alleen het geprojecteerde veld als het voldoet aan het filter in de subquery.
SELECT
p.id,
(SELECT p.name WHERE CONTAINS(p.name, "glove")).name
FROM
products p
[
{
"id": "03230",
"name": "Winter glove"
},
{
"id": "03238"
},
{
"id": "03229"
}
]
Scalaire subquery's aggregeren
Een statistische scalaire subquery is een subquery met een statistische functie in de projectie of het filter die resulteert in één waarde.
Als eerste voorbeeld kunt u een item met de volgende velden bekijken.
{
"name": "Snow coat",
"inventory": [
{
"location": "Redmond, WA",
"quantity": 50
},
{
"location": "Seattle, WA",
"quantity": 30
},
{
"location": "Washington, DC",
"quantity": 25
}
]
}
Hier volgt een subquery met één expressie voor een statistische functie in de projectie. Met deze query worden alle tags voor elk item geteld.
SELECT
p.name,
(SELECT VALUE COUNT(1) FROM i IN p.inventory) AS locationCount
FROM
products p
[
{
"name": "Snow coat",
"locationCount": 3
}
]
Hier ziet u dezelfde subquery met een 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 volgt nog een subquery met meerdere expressies voor statistische functies:
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
}
}
]
Ten slotte volgt hier een query met een geaggregeerde subquery in zowel de projectie als het 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
}
]
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-component. Deze query is efficiënter omdat u de subquery alleen hoeft uit te voeren binnen de join-instructie en niet in zowel de projectie als het filter.
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-expressie
De query-engine van Azure Cosmos DB for NoSQL ondersteunt EXISTS
expressies. Deze expressie is een statistische scalaire subquery die is ingebouwd in De Azure Cosmos DB for NoSQL. EXISTS
gebruikt een subquery-expressie en retourneert true
als de subquery rijen retourneert. Anders wordt geretourneerd false
.
Omdat de query-engine geen onderscheid maakt tussen booleaanse expressies en andere scalaire expressies, kunt u gebruiken EXISTS
in zowel -WHERE
componenten als SELECT
-componenten. Dit gedrag is in tegenstelling tot T-SQL, waarbij een Booleaanse expressie is beperkt tot alleen filters.
Als de EXISTS
subquery één waarde retourneert, namelijk undefined
, EXISTS
resulteert dit in onwaar. Neem bijvoorbeeld de volgende query die niets retourneert.
SELECT VALUE
undefined
Als u de EXISTS
expressie en de voorgaande query als subquery gebruikt, retourneert false
de expressie .
SELECT
EXISTS (SELECT VALUE undefined)
[
{
"$1": false
}
]
Als het trefwoord VALUE in de voorgaande subquery wordt weggelaten, evalueert de subquery naar een matrix met één leeg object.
SELECT
undefined
[
{}
]
Op dat moment wordt de EXISTS
expressie geëvalueerd omdat true
het object ({}
) technisch wordt afgesloten.
SELECT
EXISTS (SELECT undefined)
[
{
"$1": true
}
]
Een veelvoorkomend gebruiksvoorbeeld van ARRAY_CONTAINS
is om een item te filteren op het bestaan van een item in een matrix. In dit geval controleren we of de matrix een item met de tags
naam 'bovenkleding' bevat.
SELECT
p.name,
p.tags
FROM
products p
WHERE
ARRAY_CONTAINS(p.tags, "outerwear")
Dezelfde query kan als een alternatieve optie worden gebruikt EXISTS
.
SELECT
p.name,
p.tags
FROM
products p
WHERE
EXISTS (SELECT VALUE t FROM t IN p.tags WHERE t = "outerwear")
Bovendien ARRAY_CONTAINS
kan alleen worden gecontroleerd of een waarde gelijk is aan een willekeurig 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": "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
}
]
}
Overweeg nu de volgende query die filtert op basis van de type
eigenschappen en quantityOnHand
in de matrix binnen elk item.
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"
}
]
Voor elk van de items in de verzameling wordt een productoverschrijdend uitgevoerd met de matrixelementen. Met deze JOIN
bewerking kunt u filteren op eigenschappen in de matrix. Het RU-verbruik van deze query is echter aanzienlijk. Als er bijvoorbeeld 1.000 items in elke matrix 100 items bevatten, wordt deze uitgebreid naar 1,000 x 100
(dat wil gezegd, 100.000) tuples.
Het gebruik EXISTS
kan helpen 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
a
FROM
a IN p.accessories
WHERE
a.type = "chains" AND
a.quantityOnHand >= 10)
[
"Unobtani road bike"
]
Query's kunnen ook een alias EXISTS
gebruiken en verwijzen naar de alias in de projectie:
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
}
]
MATRIX-expressie
U kunt de ARRAY
expressie gebruiken om de resultaten van een query als een 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": "Radimer mountain bike",
"tags": [
{
"name": "road"
},
{
"name": "bike"
},
{
"name": "competitive"
}
]
}
In dit eerste voorbeeld wordt de expressie gebruikt binnen de SELECT
-component.
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"
]
}
]
Net als bij andere subquery's zijn filters met de ARRAY
expressie mogelijk.
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"
]
}
]
Matrixexpressies kunnen ook komen na de FROM
-component in subquery's.
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"
}
]