Sottoquery in Azure Cosmos DB per NoSQL
SI APPLICA A: NoSQL
Una sottoquery è una query annidata all'interno di un'altra query all'interno di Azure Cosmos DB per NoSQL. Una sottoquery viene chiamata anche query interna o interna SELECT
. L'istruzione che contiene una sottoquery è in genere denominata query esterna.
Tipi di sottoquery
Esistono due tipi principali di sottoquery:
- Correlato: sottoquery che fa riferimento ai valori della query esterna. La sottoquery viene valutata una volta per ogni riga elaborata dalla query esterna.
- Non correlato: sottoquery indipendente dalla query esterna. Può essere eseguita autonomamente senza basarsi sulla query esterna.
Nota
Azure Cosmos DB supporta solo sottoquery correlate.
Le sottoquery possono essere ulteriormente classificate in base al numero di righe e colonne restituite. Esistono tre tipi:
- Tabella: restituisce più righe e più colonne.
- Multivalore: restituisce più righe e una singola colonna.
- Scalare: restituisce una singola riga e una singola colonna.
Le query in Azure Cosmos DB per NoSQL restituiscono sempre una singola colonna (un valore semplice o un elemento complesso). Pertanto, sono applicabili solo sottoquery multivalore e scalare. È possibile usare una sottoquery multivalore solo nella FROM
clausola come espressione relazionale. È possibile usare una sottoquery scalare come espressione scalare nella SELECT
clausola o WHERE
o come espressione relazionale nella FROM
clausola .
Sottoquery multivalore
Le sottoquery multivalore restituiscono un set di elementi e vengono sempre usate all'interno della FROM
clausola . Vengono usati per:
- Ottimizzazione delle
JOIN
espressioni (self-join). - Valutazione di espressioni costose una sola volta e riferimento più volte.
Ottimizzare le espressioni self-join
Le sottoquery multivalore possono ottimizzare JOIN
le espressioni eseguendo il push dei predicati dopo ogni espressione select-many anziché dopo tutti i cross join nella WHERE
clausola .
Si consideri la query seguente:
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
Per questa query, l'indice corrisponde a qualsiasi elemento con tag con valore name
"winter" o "fall", almeno uno quantity
compreso tra zero e dieci e almeno un magazzino in cui backstock
è false
. L'espressione JOIN
esegue il prodotto incrociato di tutti gli elementi di tags
, onHandQuantities
e warehouseStock
matrici per ogni elemento corrispondente prima dell'applicazione di qualsiasi filtro.
La WHERE
clausola applica quindi il predicato di filtro per ogni <c, t, n, s>
tupla. Ad esempio, se un elemento corrispondente ha dieci elementi in ognuna delle tre matrici, si espande a 1 x 10 x 10 x 10
(ovvero 1.000) tuple. L'uso di sottoquery qui può essere utile per filtrare gli elementi della matrice unita prima di creare un join con l'espressione successiva.
Questa query è equivalente alla precedente, ma usa le sottoquery:
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)
Si supponga che solo un elemento nella matrice di tag corrisponda al filtro e che siano presenti cinque elementi per le matrici di nutrienti e di porzioni. Le JOIN
espressioni si espandono quindi a 1 x 1 x 5 x 5
(25) elementi, anziché a 1.000 elementi nella prima query.
Valutare una volta e fare riferimento più volte
Le sottoquery consentono di ottimizzare le query con espressioni costose, ad esempio funzioni definite dall'utente (UDF), stringhe complesse o espressioni aritmetiche. È possibile usare una sottoquery insieme a un'espressione JOIN
per valutare l'espressione una sola volta, ma farvi riferimento molte volte.
Si supponga di avere definito la funzione definita dall'utente (getTotalWithTax
) seguente.
function getTotalWithTax(subTotal){
return subTotal * 1.25;
}
La query seguente esegue più volte la funzione definita dall'utente getTotalWithTax
:
SELECT VALUE {
subtotal: p.price,
total: udf.getTotalWithTax(p.price)
}
FROM
products p
WHERE
udf.getTotalWithTax(p.price) < 22.25
Ecco una query equivalente che esegue la funzione definita dall'utente una sola volta:
SELECT VALUE {
subtotal: p.price,
total: totalPrice
}
FROM
products p
JOIN
(SELECT VALUE udf.getTotalWithTax(p.price)) totalPrice
WHERE
totalPrice < 22.25
Suggerimento
Tenere presente il comportamento tra prodotti delle JOIN
espressioni. Se l'espressione definita dall'utente può restituire undefined
, è necessario assicurarsi che l'espressione JOIN
produci sempre una singola riga restituendo un oggetto dalla sottoquery anziché direttamente dal valore.
Simulare il join con dati di riferimento esterni
Spesso potrebbe essere necessario fare riferimento a dati statici che raramente cambiano, ad esempio unità di misura. È consigliabile non duplicare i dati statici per ogni elemento in una query. Evitare questa duplicazione consente di risparmiare spazio di archiviazione e migliorare le prestazioni di scrittura mantenendo le dimensioni dei singoli elementi più piccole. È possibile usare una sottoquery per simulare la semantica di inner join con una raccolta di dati di riferimento statici.
Si consideri ad esempio questo set di misurazioni:
Nome | Moltiplicatore | Unità di base | |
---|---|---|---|
ng |
Nanogrammo | 1.00E-09 |
Grammo |
µg |
Microgrammi | 1.00E-06 |
Grammo |
mg |
Milligrammo | 1.00E-03 |
Grammo |
g |
Grammo | 1.00E+00 |
Grammo |
kg |
Chilogrammo | 1.00E+03 |
Grammo |
Mg |
Megagramma | 1.00E+06 |
Grammo |
Gg |
Gigagrammi | 1.00E+09 |
Grammo |
La query seguente simula l'unione con questi dati in modo da aggiungere il nome dell'unità all'output:
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
Sottoquery scalari
Un'espressione di sottoquery scalare è una sottoquery che restituisce un singolo valore. Il valore dell'espressione della sottoquery scalare è il valore della proiezione (SELECT
clausola) della sottoquery. È possibile usare un'espressione di sottoquery scalare in molte posizioni in cui un'espressione scalare è valida. Ad esempio, è possibile usare una sottoquery scalare in qualsiasi espressione nelle SELECT
clausole e WHERE
.
L'uso di una sottoquery scalare non consente sempre di ottimizzare la query. Ad esempio, il passaggio di una sottoquery scalare come argomento a un sistema o a funzioni definite dall'utente non offre alcun vantaggio nella riduzione del consumo o della latenza delle unità di risorse.
Le sottoquery scalari possono essere ulteriormente classificate come:
- Sottoquery scalari con espressione semplice
- Sottoquery scalari aggregate
Sottoquery scalari con espressione semplice
Una sottoquery scalare con espressione semplice è una sottoquery correlata con una SELECT
clausola che non contiene espressioni di aggregazione. Queste sottoquery non offrono vantaggi di ottimizzazione perché il compilatore li converte in un'espressione semplice più grande. Non esiste alcun contesto correlato tra le query interne ed esterne.
Come primo esempio, considerare questa semplice query.
SELECT
1 AS a,
2 AS b
È possibile riscrivere questa query usando una sottoquery scalare di espressione semplice.
SELECT
(SELECT VALUE 1) AS a,
(SELECT VALUE 2) AS b
Entrambe le query producono lo stesso output.
[
{
"a": 1,
"b": 2
}
]
Questa query di esempio seguente concatena l'identificatore univoco con un prefisso come sottoquery scalare di espressione semplice.
SELECT
(SELECT VALUE Concat('ID-', p.id)) AS internalId
FROM
products p
In questo esempio viene utilizzata una sottoquery scalare di espressione semplice per restituire solo i campi pertinenti per ogni elemento. La query restituisce un elemento per ogni elemento, ma include solo il campo proiettato se soddisfa il filtro all'interno della sottoquery.
SELECT
p.id,
(SELECT p.name WHERE CONTAINS(p.name, "glove")).name
FROM
products p
[
{
"id": "03230",
"name": "Winter glove"
},
{
"id": "03238"
},
{
"id": "03229"
}
]
Sottoquery scalari aggregate
Una sottoquery scalare aggregata è una sottoquery con una funzione di aggregazione nella proiezione o nel filtro che restituisce un singolo valore.
Come primo esempio, si consideri un elemento con i campi seguenti.
{
"name": "Snow coat",
"inventory": [
{
"location": "Redmond, WA",
"quantity": 50
},
{
"location": "Seattle, WA",
"quantity": 30
},
{
"location": "Washington, DC",
"quantity": 25
}
]
}
Ecco una sottoquery con una singola espressione di funzione di aggregazione nella relativa proiezione. Questa query conta tutti i tag per ogni elemento.
SELECT
p.name,
(SELECT VALUE COUNT(1) FROM i IN p.inventory) AS locationCount
FROM
products p
[
{
"name": "Snow coat",
"locationCount": 3
}
]
Ecco la stessa sottoquery con un filtro.
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
}
]
Ecco un'altra sottoquery con più espressioni di funzione di aggregazione:
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
}
}
]
Infine, ecco una query con una sottoquery di aggregazione sia nella proiezione che nel filtro:
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
}
]
Un modo più ottimale per scrivere questa query consiste nel join nella sottoquery e fare riferimento all'alias della sottoquery nelle clausole SELECT e WHERE. Questa query è più efficiente perché è necessario eseguire la sottoquery solo all'interno dell'istruzione join e non nella proiezione e nel filtro.
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
Espressione EXISTS
Il motore di query di Azure Cosmos DB per NoSQL supporta EXISTS
le espressioni. Questa espressione è una sottoquery scalare aggregata incorporata in Azure Cosmos DB per NoSQL. EXISTS
accetta un'espressione sottoquery e restituisce se la sottoquery restituisce true
qualsiasi riga. In caso contrario, viene restituito false
.
Poiché il motore di query non distingue tra espressioni booleane e altre espressioni scalari, è possibile usare EXISTS
in SELECT
entrambe le clausole.WHERE
Questo comportamento è diverso da T-SQL, in cui un'espressione booleana è limitata solo ai filtri.
Se la EXISTS
sottoquery restituisce un singolo valore undefined
, EXISTS
restituisce false. Si consideri ad esempio la query seguente che restituisce nulla.
SELECT VALUE
undefined
Se si usa l'espressione e la query precedente come sottoquery, l'espressione EXISTS
restituisce false
.
SELECT
EXISTS (SELECT VALUE undefined)
[
{
"$1": false
}
]
Se la parola chiave VALUE nella sottoquery precedente viene omessa, la sottoquery restituisce una matrice con un singolo oggetto vuoto.
SELECT
undefined
[
{}
]
A quel punto, l'espressione EXISTS
restituisce true
dal momento che l'oggetto ({}
) esce tecnicamente.
SELECT
EXISTS (SELECT undefined)
[
{
"$1": true
}
]
Un caso d'uso comune di ARRAY_CONTAINS
consiste nel filtrare un elemento in base all'esistenza di un elemento in una matrice. In questo caso, si verifica se la tags
matrice contiene un elemento denominato "outerwear".
SELECT
p.name,
p.tags
FROM
products p
WHERE
ARRAY_CONTAINS(p.tags, "outerwear")
La stessa query può essere usata EXISTS
come opzione alternativa.
SELECT
p.name,
p.tags
FROM
products p
WHERE
EXISTS (SELECT VALUE t FROM t IN p.tags WHERE t = "outerwear")
Inoltre, ARRAY_CONTAINS
può controllare solo se un valore è uguale a qualsiasi elemento all'interno di una matrice. Se sono necessari filtri più complessi sulle proprietà della matrice, usare JOIN
invece.
Si consideri questo elemento di esempio in un set con più elementi ognuno contenente una accessories
matrice.
{
"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
}
]
}
Si consideri ora la query seguente che filtra in base alle type
proprietà e quantityOnHand
nella matrice all'interno di ogni elemento.
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"
}
]
Per ognuno degli elementi della raccolta, viene eseguito un prodotto incrociato con i relativi elementi di matrice. Questa JOIN
operazione consente di filtrare le proprietà all'interno della matrice. Tuttavia, il consumo di UR della query è significativo. Ad esempio, se 1.000 elementi hanno 100 elementi in ogni matrice, si espande su 1,000 x 100
(ovvero 100.000) tuple.
L'uso EXISTS
può aiutare a evitare questo costoso cross-product. In questo esempio successivo, i filtri di query sugli elementi della matrice all'interno della EXISTS
sottoquery. Se un elemento matrice corrisponde al filtro, lo si proietta e EXISTS
restituisce 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"
]
Le query possono anche aliasre EXISTS
e fare riferimento all'alias nella proiezione:
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
}
]
Espressione ARRAY
È possibile usare l'espressione ARRAY
per proiettare i risultati di una query come matrice. È possibile usare questa espressione solo all'interno SELECT
della clausola della query.
Per questi esempi, si supponga che sia presente un contenitore con almeno questo elemento.
{
"name": "Radimer mountain bike",
"tags": [
{
"name": "road"
},
{
"name": "bike"
},
{
"name": "competitive"
}
]
}
In questo primo esempio, l'espressione viene usata all'interno della SELECT
clausola .
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"
]
}
]
Come per altre sottoquerie, i filtri con l'espressione ARRAY
sono possibili.
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"
]
}
]
Le espressioni di matrice possono anche venire dopo la FROM
clausola nelle sottoquerie.
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"
}
]