Sottoquery - Linguaggio di query in Cosmos DB (in Azure e Infrastruttura)

Una sottoquery è una query annidata all'interno di un'altra query all'interno del linguaggio di query. Una sottoquery è detta anche query interna o interna SELECT. L'istruzione che contiene una sottoquery viene in genere chiamata 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.

Le sottoquery possono essere ulteriormente classificate in base al numero di righe e colonne restituite. Sono disponibili 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 nel linguaggio di query 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 seguente interrogazione:

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

Per questa query, l'indice corrisponde a qualsiasi elemento con un tag con fabricmaterialo key , almeno una dimensione con un order valore maggiore di *tre e almeno un colore con gray come sottostringa. L'espressione JOIN seguente esegue il prodotto incrociato di tutti gli elementi di tags, sizese colors matrici per ogni elemento corrispondente prima dell'applicazione di qualsiasi filtro.

La WHERE clausola applica quindi il predicato di filtro per ogni tupla $<c, t, n, s>$. Ad esempio, se un elemento corrispondente ha dieci elementi in ognuna delle tre matrici, si espande a 1.000 tuple usando questa formula:

$$1 x 10 x 10 x 10$$

L'uso delle sottoquery qui può essere utile per filtrare gli elementi della matrice uniti prima di unire l'espressione successiva.

Questa query equivale a quella precedente, ma usa sottoquery:

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

Si supponga che solo un elemento nella matrice di tag corrisponda al filtro e che siano presenti cinque elementi sia per la quantità che per le matrici di scorte. L'espressione JOIN si espande quindi a 25 tuple usando questa formula anziché 1.000 elementi nella prima query:

$$1 x 1 x 5 x 5$$

Valutare una volta e fare riferimento più volte

Le sottoquery consentono di ottimizzare le query con espressioni costose, ad esempio funzioni definite dall'utente ,stringhe complesse o espressioni aritmetiche. È possibile usare una sottoquery insieme a un'espressione JOIN per valutare l'espressione una sola volta, ma farvi riferimento più volte.

Questa query di esempio calcola il prezzo con un supplemento di 25% più volte nella query.

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

Ecco una query equivalente che esegue il calcolo una sola volta:

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

Suggerimento

Tenere presente il comportamento tra prodotti delle JOIN espressioni. Se l'espressione può restituire undefined, è necessario assicurarsi che l'espressione JOIN produci sempre una singola riga restituendo un oggetto dalla sottoquery anziché direttamente dal valore.

Simulare un join relazionale 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ù piccoli. È 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 che rappresenta la lunghezza di un capo:

Misura	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

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

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

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'unica espressione semplice più grande. Non esiste alcun contesto correlato tra le query interne ed esterne.

Come primo esempio, si consideri questa query semplice.

SELECT
  1 AS a,
  2 AS b

È possibile riscrivere questa query usando una sottoquery scalare con espressione semplice.

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

Entrambe le query producono lo stesso output.

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

In questo esempio di query seguente viene concatenato l'identificatore univoco con un prefisso come sottoquery scalare con espressione semplice.

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

In questo esempio viene utilizzata una sottoquery scalare con 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, "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"
  }
]

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": "Blators Snowboard Boots",
    "colors": [
      "turquoise",
      "cobalt",
      "jam",
      "galliano",
      "violet"
    ],
    "sizes": [ ... ],
    "tags": [ ... ]
  }
]

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 c IN p.colors) AS colorsCount
FROM
  products p
WHERE
  p.id = "00000000-0000-0000-0000-000000004389"

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

Ecco la stessa sottoquery con un filtro.

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

Ecco un'altra sottoquery con più espressioni di funzione di aggregazione:

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

Infine, ecco una query con una sottoquery di aggregazione sia nella proiezione che nel filtro:

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

Un modo più ottimale per scrivere questa query consiste nel creare un 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,
  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

Espressione EXISTS

Il linguaggio di query supporta EXISTS le espressioni. Questa espressione è una sottoquery scalare aggregata incorporata nel linguaggio di query. EXISTS accetta un'espressione di sottoquery e restituisce true se la sottoquery restituisce righe. In caso contrario, restituisce false.

Poiché il motore di query non distingue tra espressioni booleane e altre espressioni scalari, è possibile usare EXISTS in entrambe SELECT le clausole e 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 non restituisce nulla.

SELECT VALUE
  undefined

Se si usa l'espressione EXISTS e la query precedente come sottoquery, l'espressione restituisce false.

SELECT VALUE
  EXISTS (SELECT VALUE undefined)

[
  false
]

Se la parola chiave VALUE nella sottoquery precedente viene omessa, la sottoquery restituisce una matrice con un singolo oggetto vuoto.

SELECT
  undefined

[
  {}
]

A questo punto, l'espressione EXISTS restituisce true poiché l'oggetto ({}) esce tecnicamente.

SELECT VALUE
  EXISTS (SELECT undefined)

[
  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, viene verificato se la tags matrice contiene un elemento denominato "outerwear".

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

La stessa query può essere usata EXISTS come opzione alternativa.

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

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": "Cosmoxy Pack",
    "tags": [
      {
        "key": "fabric",
        "value": "leather",
        "description": "Leather"
      },
      {
        "key": "volume",
        "value": "68-gal",
        "description": "6.8 Gal"
      }
    ]
  }
]

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

Per ogni elemento della raccolta, un prodotto incrociato viene eseguito con i relativi elementi di matrice. Questa JOIN operazione consente di filtrare le proprietà all'interno della matrice. Tuttavia, il consumo di UR di questa query è significativo. Ad esempio, se 1.000 elementi hanno 100 elementi in ogni matrice, si espande fino a 100.000 tuple usando questa formula:

$$1.000 x 100$$

L'uso EXISTS di aiuta a evitare questo costoso prodotto incrociato. In questo esempio successivo la query filtra gli elementi della matrice all'interno della EXISTS sottoquery. Se un elemento di matrice corrisponde al filtro, lo si proietta e EXISTS restituisce 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"
]

Le query possono anche eseguire l'alias EXISTS e fare riferimento all'alias nella proiezione:

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

Espressione ARRAY

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

Per questi esempi, si supponga che sia presente un contenitore con almeno questo elemento.

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

In questo primo esempio, l'espressione viene usata all'interno della SELECT clausola .

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

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

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

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

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

Contenuti correlati

• Esaminare il linguaggio di query
• Introduzione a JSON nel linguaggio di query
• Eseguire self-join
• Esplorare le funzioni di sistema