Esempi di sottoquery SQL per Azure Cosmos DB

SI APPLICA A: API SQL

Una sottoquery è una query annidata all'interno di un'altra query. Una sottoquery viene chiamata anche una query interna o una selezione interna. L'istruzione contenente una sottoquery è in genere denominata query esterna.

Questo articolo descrive le sottoquery SQL e i relativi casi d'uso comuni in Azure Cosmos DB. Tutte le query di esempio in questo documento possono essere eseguite su un set di dati di nutrizione di esempio.

Tipi di sottoquery

Esistono due tipi principali di sottoquery:

  • Correlato: sottoquery che fa riferimento ai valori dalla query esterna. La sottoquery viene valutata una volta per ogni riga elaborata dalla query esterna.
  • Non correlato: sottoquery indipendente dalla query esterna. Può essere eseguito autonomamente senza basarsi sulla query esterna.

Nota

Azure Cosmos DB supporta solo sottoquerie 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 SQL in Azure Cosmos DB restituiscono sempre una singola colonna (un valore semplice o un documento complesso). Pertanto, solo le sottoquerie multivalore e scalari sono applicabili in Azure Cosmos DB. È possibile usare una sottoquery multivalore solo nella clausola FROM come espressione relazionale. È possibile usare una sottoquery scalare come espressione scalare nella clausola SELECT o WHERE o come espressione relazionale nella clausola FROM.

Sottoquery multivalore

Le sottoquerie multivalore restituiscono un set di documenti e vengono sempre usate all'interno della clausola FROM. Vengono usati per:

  • Ottimizzazione delle espressioni JOIN.
  • Valutazione di espressioni costose una volta e di riferimento più volte.

Ottimizzare le espressioni JOIN

Le sottoquerie multivalore possono ottimizzare le espressioni JOIN eseguendo il push dei predicati dopo ogni espressione select-molti anziché dopo tutti i join incrociati nella clausola WHERE.

Si consideri la query seguente:

SELECT Count(1) AS Count
FROM c
JOIN t IN c.tags
JOIN n IN c.nutrients
JOIN s IN c.servings
WHERE t.name = 'infant formula' AND (n.nutritionValue > 0 
AND n.nutritionValue < 10) AND s.amount > 1

Per questa query, l'indice corrisponderà a qualsiasi documento con un tag con il nome "formula infantile". È un elemento nutriente con un valore compreso tra 0 e 10 e un elemento di servizio con una quantità maggiore di 1. L'espressione JOIN eseguirà qui il prodotto incrociato di tutti gli elementi di tag, nutrienti e matrici di servizi per ogni documento corrispondente prima che venga applicato qualsiasi filtro.

La clausola WHERE applicherà quindi il predicato di filtro in ogni <tupla c, t,> n. Ad esempio, se un documento corrispondente aveva 10 elementi in ognuna delle tre matrici, si espanderà fino a 1 x 10 x 10 x 10 (ovvero 1.000) tuple. L'uso delle sottoquerie può essere utile per filtrare gli elementi della matrice aggiunti prima di partecipare all'espressione successiva.

Questa query è equivalente alla precedente, ma usa le sottoquery:

SELECT Count(1) AS Count
FROM c
JOIN (SELECT VALUE t FROM t IN c.tags WHERE t.name = 'infant formula')
JOIN (SELECT VALUE n FROM n IN c.nutrients WHERE n.nutritionValue > 0 AND n.nutritionValue < 10)
JOIN (SELECT VALUE s FROM s IN c.servings WHERE s.amount > 1)

Si supponga che solo un elemento nella matrice di tag corrisponda al filtro e che siano presenti cinque elementi sia per i nutrienti che per le matrici di servizio. Le espressioni JOIN si espanderanno quindi su 1 x 1 x 5 x 5 = 25 elementi, anziché 1.000 elementi nella prima query.

Valutare una volta e fare riferimento molte volte

Le sottoquery consentono di ottimizzare le query con espressioni costose, ad esempio funzioni definite dall'utente (UDFs), stringhe complesse o espressioni aritmetiche. È possibile usare una sottoquery insieme a un'espressione JOIN per valutare l'espressione una volta, ma fare riferimento a esso molte volte.

La query seguente esegue due volte la funzione UDF GetMaxNutritionValue :

SELECT c.id, udf.GetMaxNutritionValue(c.nutrients) AS MaxNutritionValue
FROM c
WHERE udf.GetMaxNutritionValue(c.nutrients) > 100

Ecco una query equivalente che esegue la funzione UDF una sola volta:

SELECT TOP 1000 c.id, MaxNutritionValue
FROM c
JOIN (SELECT VALUE udf.GetMaxNutritionValue(c.nutrients)) MaxNutritionValue
WHERE MaxNutritionValue > 100

Nota

Tenere presente il comportamento incrociato delle espressioni JOIN. Se l'espressione UDF può valutare indefinita, è necessario assicurarsi che l'espressione JOIN produsca sempre una singola riga restituendo un oggetto dalla sottoquery anziché direttamente dal valore.

Ecco un esempio simile che restituisce un oggetto anziché un valore:

SELECT TOP 1000 c.id, m.MaxNutritionValue
FROM c
JOIN (SELECT udf.GetMaxNutritionValue(c.nutrients) AS MaxNutritionValue) m
WHERE m.MaxNutritionValue > 100

L'approccio non è limitato alle UDF. Si applica a qualsiasi espressione potenzialmente costosa. Ad esempio, è possibile adottare lo stesso approccio con la funzione avgmatematica :

SELECT TOP 1000 c.id, AvgNutritionValue
FROM c
JOIN (SELECT VALUE avg(n.nutritionValue) FROM n IN c.nutrients) AvgNutritionValue
WHERE AvgNutritionValue > 80

Simulare l'aggiunta ai dati di riferimento esterni

Potrebbe spesso essere necessario fare riferimento a dati statici che raramente cambiano, ad esempio unità di misura o codici paese. È preferibile non duplicare tali dati per ogni documento. Evitando questa duplicazione, l'archiviazione consente di risparmiare e migliorare le prestazioni di scrittura mantenendo le dimensioni del documento più piccole. È possibile usare una sottoquery per simulare la semantica inner-join con una raccolta di dati di riferimento.

Si consideri, ad esempio, questo set di dati di riferimento:

Unità 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 Megagrammi 1.00E+06 Grammo
Gg Gigagrammi 1.00E+09 Grammo
Nj Nanojoule 1.00E-09 Joule
μJ Microjoule 1.00E-06 Joule
Mj Millijoule 1.00E-03 Joule
J Joule 1.00E+00 Joule
Kj Kilojoule 1.00E+03 Joule
MJ Megajoule 1.00E+06 Joule
GJ Gigajoule 1.00E+09 Joule
Cal Calorie 1.00E+00 calorie
Kcal Calorie 1.00E+03 calorie
UI Unità internazionali

La query seguente simula l'aggiunta a questi dati in modo da aggiungere il nome dell'unità all'output:

SELECT TOP 10 n.id, n.description, n.nutritionValue, n.units, r.name
FROM food
JOIN n IN food.nutrients
JOIN r 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'},
        {unit: 'nJ', name: 'nanojoule', multiplier: 0.000000001, baseUnit: 'joule'},
        {unit: 'µJ', name: 'microjoule', multiplier: 0.000001, baseUnit: 'joule'},
        {unit: 'mJ', name: 'millijoule', multiplier: 0.001, baseUnit: 'joule'},
        {unit: 'J', name: 'joule', multiplier: 1, baseUnit: 'joule'},
        {unit: 'kJ', name: 'kilojoule', multiplier: 1000, baseUnit: 'joule'},
        {unit: 'MJ', name: 'megajoule', multiplier: 1000000, baseUnit: 'joule'},
        {unit: 'GJ', name: 'gigajoule', multiplier: 1000000000, baseUnit: 'joule'},
        {unit: 'cal', name: 'calorie', multiplier: 1, baseUnit: 'calorie'},
        {unit: 'kcal', name: 'Calorie', multiplier: 1000, baseUnit: 'calorie'},
        {unit: 'IU', name: 'International units'}
    ]
)
WHERE n.units = r.unit

Sottoquery scalari

Un'espressione di sottoquery scalare è una sottoquery che restituisce un singolo valore. Il valore dell'espressione sottoquery scalare è il valore della proiezione (clausola SELECT) 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 clausole SELECT e WHERE.

L'uso di una sottoquery scalare non consente sempre di ottimizzare. Ad esempio, passando una sottoquery scalare come argomento a un sistema o a funzioni definite dall'utente non offre alcun vantaggio nel consumo o nella latenza dell'unità risorse.

Le sottoquerie scalari possono essere ulteriormente classificate come:

  • Sottoquerie scalari di espressione semplice
  • Sottoquerie scalari di aggregazione

Sottoquerie scalari di espressione semplice

Una sottoquery scalare di espressioni semplici è una sottoquery correlata con una clausola SELECT che non contiene espressioni di aggregazione. Queste sottoquerie 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.

Di seguito sono riportati alcuni esempi:

Esempio 1

SELECT 1 AS a, 2 AS b

È possibile riscrivere questa query usando una sottoquery scalare di espressioni semplici a:

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

Entrambe le query producono questo output:

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

Esempio 2

SELECT TOP 5 Concat('id_', f.id) AS id
FROM food f

È possibile riscrivere questa query usando una sottoquery scalare di espressioni semplici a:

SELECT TOP 5 (SELECT VALUE Concat('id_', f.id)) AS id
FROM food f

Output query:

[
  { "id": "id_03226" },
  { "id": "id_03227" },
  { "id": "id_03228" },
  { "id": "id_03229" },
  { "id": "id_03230" }
]

Esempio 3

SELECT TOP 5 f.id, Contains(f.description, 'fruit') = true ? f.description : undefined
FROM food f

È possibile riscrivere questa query usando una sottoquery scalare di espressioni semplici a:

SELECT TOP 10 f.id, (SELECT f.description WHERE Contains(f.description, 'fruit')).description
FROM food f

Output query:

[
  { "id": "03230" },
  { "id": "03238", "description":"Babyfood, dessert, tropical fruit, junior" },
  { "id": "03229" },
  { "id": "03226", "description":"Babyfood, dessert, fruit pudding, orange, strained" },
  { "id": "03227" }
]

Sottoquerie scalari di aggregazione

Una sottoquery scalare aggregata è una sottoquery che ha una funzione di aggregazione nella proiezione o nel filtro che restituisce un singolo valore.

Esempio 1:

Ecco una sottoquery con un'unica espressione di funzione di aggregazione nella relativa proiezione:

SELECT TOP 5 
    f.id, 
    (SELECT VALUE Count(1) FROM n IN f.nutrients WHERE n.units = 'mg'
) AS count_mg
FROM food f

Output query:

[
  { "id": "03230", "count_mg": 13 },
  { "id": "03238", "count_mg": 14 },
  { "id": "03229", "count_mg": 13 },
  { "id": "03226", "count_mg": 15 },
  { "id": "03227", "count_mg": 19 }
]

Esempio 2

Ecco una sottoquery con più espressioni di funzione di aggregazione:

SELECT TOP 5 f.id, (
    SELECT Count(1) AS count, Sum(n.nutritionValue) AS sum 
    FROM n IN f.nutrients 
    WHERE n.units = 'mg'
) AS unit_mg
FROM food f

Output query:

[
  { "id": "03230","unit_mg": { "count": 13,"sum": 147.072 } },
  { "id": "03238","unit_mg": { "count": 14,"sum": 107.385 } },
  { "id": "03229","unit_mg": { "count": 13,"sum": 141.579 } },
  { "id": "03226","unit_mg": { "count": 15,"sum": 183.91399999999996 } },
  { "id": "03227","unit_mg": { "count": 19,"sum": 94.788999999999987 } }
]

Esempio 3

Ecco una query con una sottoquery di aggregazione sia nella proiezione che nel filtro:

SELECT TOP 5 
    f.id, 
	(SELECT VALUE Count(1) FROM n IN f.nutrients WHERE n.units = 'mg') AS count_mg
FROM food f
WHERE (SELECT VALUE Count(1) FROM n IN f.nutrients WHERE n.units = 'mg') > 20

Output query:

[
  { "id": "03235", "count_mg": 27 },
  { "id": "03246", "count_mg": 21 },
  { "id": "03267", "count_mg": 21 },
  { "id": "03269", "count_mg": 21 },
  { "id": "03274", "count_mg": 21 }
]

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 TOP 5 f.id, count_mg
FROM food f
JOIN (SELECT VALUE Count(1) FROM n IN f.nutrients WHERE n.units = 'mg') AS count_mg
WHERE count_mg > 20

Espressione EXISTS

Azure Cosmos DB supporta espressioni EXISTS. Si tratta di una sottoquery scalare aggregata incorporata nell'API SQL di Azure Cosmos DB. EXISTS è un'espressione booleana che accetta un'espressione sottoquery e restituisce true se la sottoquery restituisce qualsiasi riga. Negli altri casi, viene restituito false.

Poiché l'API SQL di Azure Cosmos DB non distingue tra espressioni booleane e altre espressioni scalari, è possibile usare EXISTS nelle clausole SELECT e WHERE. A differenza di T-SQL, in cui un'espressione booleana (ad esempio, EXISTS, BETWEEN e IN) è limitata al filtro.

Se la sottoquery EXISTS restituisce un singolo valore non definito, EXISTS restituirà false. Si consideri, ad esempio, la query seguente che restituisce false:

SELECT EXISTS (SELECT VALUE undefined)

Se la parola chiave VALUE nella sottoquery precedente viene omessa, la query restituirà true:

SELECT EXISTS (SELECT undefined) 

La sottoquery racchiuderà l'elenco di valori nell'elenco selezionato in un oggetto. Se l'elenco selezionato non ha valori, la sottoquery restituirà il valore singolo '{}'. Questo valore è definito, quindi EXISTS restituisce true.

Esempio: Riscrivere ARRAY_CONTAINS e JOIN come EXISTS

Un caso d'uso comune di ARRAY_CONTAINS consiste nel filtrare un documento in base all'esistenza di un elemento in una matrice. In questo caso, si verifica se la matrice di tag contiene un elemento denominato "arancione".

SELECT TOP 5 f.id, f.tags
FROM food f
WHERE ARRAY_CONTAINS(f.tags, {name: 'orange'})

È possibile riscrivere la stessa query per usare EXISTS:

SELECT TOP 5 f.id, f.tags
FROM food f
WHERE EXISTS(SELECT VALUE t FROM t IN f.tags WHERE t.name = 'orange')

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.

Si consideri la query seguente che filtra in base alle unità e nutritionValue alle proprietà nella matrice:

SELECT VALUE c.description
FROM c
JOIN n IN c.nutrients
WHERE n.units= "mg" AND n.nutritionValue > 0

Per ognuno dei documenti della raccolta, viene eseguito un prodotto incrociato con i relativi elementi di matrice. Questa operazione JOIN consente di filtrare le proprietà all'interno della matrice. Tuttavia, il consumo di UR della query sarà significativo. Ad esempio, se 1.000 documenti hanno 100 elementi in ogni matrice, si espanderà fino a 1.000 x 100 ,ovvero 100.000 tuple.

L'uso di EXISTS può aiutare a evitare questo costoso cross-product:

SELECT VALUE c.description
FROM c
WHERE EXISTS(
    SELECT VALUE n
    FROM n IN c.nutrients
    WHERE n.units = "mg" AND n.nutritionValue > 0
)

In questo caso, è possibile filtrare gli elementi della matrice all'interno della sottoquery EXISTS. Se un elemento di matrice corrisponde al filtro, lo si proietta e EXISTS restituisce true.

È anche possibile alias EXISTS e farvi riferimento nella proiezione:

SELECT TOP 1 c.description, EXISTS(
    SELECT VALUE n
    FROM n IN c.nutrients
    WHERE n.units = "mg" AND n.nutritionValue > 0) as a
FROM c

Output query:

[
    {
        "description": "Babyfood, dessert, fruit pudding, orange, strained",
        "a": true
    }
]

Espressione ARRAY

È possibile usare l'espressione ARRAY per proiettare i risultati di una query come matrice. È possibile usare questa espressione solo all'interno della clausola SELECT della query.

SELECT TOP 1   f.id, ARRAY(SELECT VALUE t.name FROM t in f.tags) AS tagNames
FROM  food f

Output query:

[
    {
        "id": "03238",
        "tagNames": [
            "babyfood",
            "dessert",
            "tropical fruit",
            "junior"
        ]
    }
]

Come per altre sottoquerie, i filtri con l'espressione ARRAY sono possibili.

SELECT TOP 1 c.id, ARRAY(SELECT VALUE t FROM t in c.tags WHERE t.name != 'infant formula') AS tagNames
FROM c

Output query:

[
    {
        "id": "03226",
        "tagNames": [
            {
                "name": "babyfood"
            },
            {
                "name": "dessert"
            },
            {
                "name": "fruit pudding"
            },
            {
                "name": "orange"
            },
            {
                "name": "strained"
            }
        ]
    }
]

Le espressioni di matrice possono anche venire dopo la clausola FROM nelle sottoquerie.

SELECT TOP 1 c.id, ARRAY(SELECT VALUE t.name FROM t in c.tags) as tagNames
FROM c
JOIN n IN (SELECT VALUE ARRAY(SELECT t FROM t in c.tags WHERE t.name != 'infant formula'))

Output query:

[
    {
        "id": "03238",
        "tagNames": [
            "babyfood",
            "dessert",
            "tropical fruit",
            "junior"
        ]
    }
]

Passaggi successivi