Poddotazy – Dotazovací jazyk ve službě Cosmos DB (v Azure a prostředcích infrastruktury)

Poddotaz je dotaz vnořený do jiného dotazu v rámci dotazovacího jazyka. Poddotaz se také nazývá vnitřní dotaz nebo vnitřní SELECT. Příkaz, který obsahuje poddotaz, se obvykle nazývá vnější dotaz.

Typy poddotazů

Existují dva hlavní typy poddotazů:

• Korelace: Poddotaz, který odkazuje na hodnoty z vnějšího dotazu. Poddotaz se vyhodnocuje jednou pro každý řádek, který zpracovává vnější dotaz.
• Nekoelované: Poddotaz, který je nezávislý na vnějším dotazu. Můžete ho spustit samostatně, aniž byste museli spoléhat na vnější dotaz.

Poddotazy je možné dále klasifikovat na základě počtu řádků a sloupců, které vrací. Existují tři typy:

• Tabulka: Vrátí více řádků a více sloupců.
• Více hodnot: Vrátí více řádků a jeden sloupec.
• Skalární: Vrátí jeden řádek a jeden sloupec.

Dotazy v dotazovacím jazyce vždy vrací jeden sloupec (jednoduchou hodnotu nebo složitou položku). Proto platí pouze vícehodnotové a skalární poddotazy. Poddotaz s více hodnotami můžete použít pouze v FROM klauzuli jako relační výraz. Skalární poddotaz můžete použít jako skalární výraz v SELECT klauzuli nebo WHERE jako relační výraz v klauzuli FROM .

Poddotazy s více hodnotami

Poddotazy s více hodnotami vrací sadu položek a vždy se používají v rámci FROM klauzule. Používají se pro:

• Optimalizace JOIN výrazů (self-join)
• Vyhodnocení drahých výrazů jednou a odkazování na vícekrát

Optimalizace výrazů s vlastním spojením

Poddotazy s více hodnotami můžou optimalizovat JOIN výrazy tím, že za každý výraz select-many nasdílí predikáty místo za všemi křížovými spojeními v WHERE klauzuli.

Představte si následující dotaz:

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

Pro tento dotaz index odpovídá libovolné položce, která má značku s keyfabric jednou nebo materialnejméně jednou velikostí s order hodnotou větší než *tři, aspoň jednu barvu s gray podřetětědcem. Výraz JOIN zde provede křížový součin všech položek tags, sizesa colors pole pro každou odpovídající položku před použití jakéhokoli filtru.

Klauzule WHERE pak použije predikát filtru na každý $<c, t, n, s>$ řazené kolekce členů. Pokud například odpovídající položka měla v každé ze tří polí deset položek, rozšíří se na 1 000 řazených kolekcí členů pomocí tohoto vzorce:

$$1 x 10 x 10 x 10$$

Použití poddotazů zde může pomoct při filtrování připojených položek pole před spojením s dalším výrazem.

Tento dotaz je ekvivalentní předchozímu dotazu, ale používá poddotazy:

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

Předpokládejme, že filtr odpovídá pouze jedné položce v poli značek a pro množství i pole akcií existuje pět položek. Výraz JOIN se pak pomocí tohoto vzorce rozšíří na 25 řazených kolekcí členů, nikoli na 1 000 položek v prvním dotazu:

$$1 x 1 x 5 x 5$$

Vyhodnocení jednou a mnohokrát odkazování

Poddotazy můžou pomoct optimalizovat dotazy s nákladnými výrazy, jako jsou uživatelem definované funkce (UDF), komplexní řetězce nebo aritmetické výrazy. Poddotaz můžete použít spolu s výrazem JOIN k vyhodnocení výrazu jednou, ale mnohokrát na něj odkazovat.

Tento ukázkový dotaz vypočítá cenu s dodatkem 25% vícekrát v dotazu.

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

Tady je ekvivalentní dotaz, který spouští výpočet pouze jednou:

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

Návod

Mějte na paměti chování výrazů napříč JOIN produkty. Pokud se výraz může vyhodnotit jako undefined, měli byste zajistit, aby výraz vždy vytvořil jeden řádek tak, že JOIN vrátí objekt z poddotazu, nikoli přímo hodnotu.

Napodobování relačního spojení s externími referenčními daty

Často budete muset odkazovat na statická data, která se zřídka mění, například měrné jednotky. Je ideální ne duplikovat statická data pro každou položku v dotazu. Vyhnete se tomu, že se tato duplicita šetří na úložišti a zlepšuje výkon zápisu tím, že velikost jednotlivých položek bude menší. Poddotaz můžete použít k napodobení sémantiky vnitřního spojení s kolekcí statických referenčních dat.

Představte si například tuto sadu měření, která představuje délku oděvu:

Velikost	Délka	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

Následující dotaz napodobuje spojení s těmito daty, takže do výstupu přidáte název jednotky:

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

Skalární poddotazy

Skalární poddotaz je poddotaz, který se vyhodnotí jako jedna hodnota. Hodnota skalárního poddotazového výrazu je hodnota projekce (SELECT klauzule) poddotazu. Skalární poddotazový výraz můžete použít na mnoha místech, kde je skalární výraz platný. Skalární poddotaz můžete například použít v libovolném výrazu SELECT v klauzulích i WHERE v klauzulích.

Použití skalárního poddotazu vždy nepomůže optimalizovat dotaz. Například předání skalárního poddotazu jako argumentu funkcím definovanému systémem nebo uživatelem neposkytuje žádnou výhodu při snížení spotřeby nebo latence jednotky prostředků .RU.

Skalární poddotazy lze dále klasifikovat jako:

• Skalární poddotazy jednoduchého výrazu
• Agregace skalárních poddotazů

Skalární poddotazy jednoduchého výrazu

Skalární poddotaz jednoduchých výrazů je korelovaný poddotaz, který obsahuje SELECT klauzuli, která neobsahuje žádné agregační výrazy. Tyto poddotazy neposkytují žádné výhody optimalizace, protože kompilátor je převede na jeden větší jednoduchý výraz. Mezi vnitřními a vnějšími dotazy neexistuje žádný korelovaný kontext.

Jako první příklad zvažte tento triviální dotaz.

SELECT
  1 AS a,
  2 AS b

Tento dotaz můžete přepsat pomocí skalárního poddotazu jednoduchého výrazu.

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

Oba dotazy vytvářejí stejný výstup.

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

Tento další příklad dotazu zřetězí jedinečný identifikátor s předponou jako skalární poddotaz jednoduchých výrazů.

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

Tento příklad používá skalární poddotaz jednoduchých výrazů k vrácení relevantních polí pouze pro každou položku. Dotaz vypíše něco pro každou položku, ale obsahuje pouze předpokládané pole, pokud splňuje filtr v rámci poddotazu.

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

Agregace skalárních poddotazů

Agregační skalární poddotaz je poddotaz, který má v projekci nebo filtru agregační funkci, která se vyhodnotí jako jedna hodnota.

Jako první příklad zvažte položku s následujícími poli.

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

Tady je poddotaz s jedním agregačním výrazem funkce v projekci. Tento dotaz spočítá všechny značky pro každou položku.

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

Tady je stejný poddotaz s filtrem.

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

Tady je další poddotaz s více agregačními výrazy funkcí:

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

Nakonec tady je dotaz s agregačním poddotazem v projekci i filtru:

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

Vhodnější způsob, jak tento dotaz napsat, je spojit se s poddotazem a odkazovat na alias poddotazů v klauzulích SELECT i WHERE. Tento dotaz je efektivnější, protože potřebujete poddotaz spustit pouze v rámci příkazu join, a ne v projekci i filtru.

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

VÝRAZ EXISTS

Dotazovací jazyk podporuje EXISTS výrazy. Tento výraz je agregační skalární poddotaz integrovaný do dotazovacího jazyka. EXISTS vezme výraz poddotaz a vrátí true , pokud poddotaz vrátí všechny řádky. V opačném případě vrátí false.

Vzhledem k tomu, že dotazovací modul nerozlišuje mezi logickými výrazy a žádnými dalšími skalárními výrazy, můžete je použít EXISTS v SELECT obou klauzulích i WHERE v klauzulích. Toto chování se liší od jazyka T-SQL, kdy je logický výraz omezen pouze na filtry.

EXISTS Pokud poddotaz vrátí jednu hodnotu, která je undefined, EXISTS vyhodnotí se jako false. Představte si například následující dotaz, který nevrací nic.

SELECT VALUE
  undefined

Pokud použijete EXISTS výraz a předchozí dotaz jako poddotaz, výraz vrátí false.

SELECT VALUE
  EXISTS (SELECT VALUE undefined)

[
  false
]

Pokud je klíčové slovo HODNOTA v předchozím poddotazu vynecháno, poddotaz se vyhodnotí jako pole s jedním prázdným objektem.

SELECT
  undefined

[
  {}
]

V tomto okamžiku se EXISTS výraz vyhodnotí jako true od technicky ukončeného objektu ({}).

SELECT VALUE
  EXISTS (SELECT undefined)

[
  true
]

Běžným případem ARRAY_CONTAINS použití je filtrování položky podle existence položky v poli. V tomto případě kontrolujeme, jestli tags pole obsahuje položku s názvem "vnější oděvy".

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

Stejný dotaz může použít EXISTS jako alternativní možnost.

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

Kromě toho může zkontrolovat, ARRAY_CONTAINS zda je hodnota rovna libovolnému prvku v rámci pole. Pokud potřebujete složitější filtry vlastností pole, použijte JOIN místo toho.

Představte si tuto ukázkovou položku v sadě s více položkami, které obsahují accessories pole.

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

Teď zvažte následující dotaz, který filtruje na typequantityOnHand základě vlastností pole v rámci každé položky.

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

Pro každou položku v kolekci se provádí křížový součin se svými prvky pole. Tato JOIN operace umožňuje filtrovat vlastnosti v rámci pole. Spotřeba RU tohoto dotazu je ale významná. Pokud například 1 000 položek obsahovalo v každé matici 100 položek, rozšíří se na 100 000 řazených kolekcí členů pomocí tohoto vzorce:

$$1 000 x 100$$

Použití EXISTS pomáhá vyhnout se tomuto nákladnému křížovému produktu. V tomto dalším příkladu filtruje dotaz na prvky pole v poddotadu EXISTS . Pokud prvek pole odpovídá filtru, pak ho promítnete a EXISTS vyhodnotíte jako 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"
]

Dotazy mohou také aliasy EXISTS a odkazovat na tento alias v projekci:

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

VÝRAZ ARRAY

Pomocí výrazu ARRAY můžete promítnout výsledky dotazu jako pole. Tento výraz můžete použít pouze v rámci SELECT klauzule dotazu.

V těchto příkladech předpokládejme, že existuje kontejner s alespoň touto položkou.

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

V tomto prvním příkladu SELECT se výraz použije v rámci klauzule.

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

Stejně jako u jiných poddotazů jsou možné filtry s výrazem ARRAY .

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

Výrazy pole můžou pocházet také za FROM klauzulí v poddotazech.

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

Související obsah

• Kontrola dotazovacího jazyka
• Začínáme s JSON v dotazovacím jazyce
• Provádění samoobslužných spojení
• Prozkoumání systémových funkcí