Podzapytania — język zapytań w usłudze Cosmos DB (na platformie Azure i w sieci szkieletowej)

Podzapytywanie to zapytanie zagnieżdżone w ramach innego zapytania w języku zapytań. Podzapytanie jest również nazywane zapytaniem wewnętrznym lub wewnętrznym SELECT. Instrukcja zawierająca podzapytywanie jest zwykle nazywana zapytaniem zewnętrznym.

Typy podzapytania

Istnieją dwa główne typy podzapytania:

• Skorelowane: podzapytywanie odwołujące się do wartości z zapytania zewnętrznego. Podzapytywanie jest oceniane raz dla każdego wiersza, który przetwarza zapytanie zewnętrzne.
• Niezrelowane: podzapytywanie niezależne od zapytania zewnętrznego. Można go uruchomić samodzielnie bez polegania na zapytaniu zewnętrznym.

Podzapytania można dalej klasyfikować na podstawie liczby zwracanych wierszy i kolumn. Dostępne są trzy typy kosztów:

• Tabela: zwraca wiele wierszy i wiele kolumn.
• Wiele wartości: zwraca wiele wierszy i jedną kolumnę.
• Skalarny: zwraca pojedynczy wiersz i jedną kolumnę.

Zapytania w języku zapytań zawsze zwracają jedną kolumnę (prostą wartość lub złożony element). W związku z tym mają zastosowanie tylko podzapytania wielowartego i skalarne. Podzapytywanie wielowartość można użyć tylko w klauzuli FROM jako wyrażenia relacyjnego. Można użyć podzapytania skalarnego jako wyrażenia skalarnego w klauzuli SELECT or WHERE lub jako wyrażenia relacyjnego w klauzuli FROM .

Podzapytania wielu wartości

Podzapytania wielowartościowe zwracają zestaw elementów i są zawsze używane w klauzuli FROM . Są one używane do:

• JOIN Optymalizowanie wyrażeń (samosprzężenia).
• Ocenianie kosztownych wyrażeń raz i odwoływanie się do wielu razy.

Optymalizowanie wyrażeń samosprzężenia

Podzapytania wielu wartości mogą optymalizować JOIN wyrażenia, wypychając predykaty po każdym wyrażeniu select-many , a nie po wszystkich sprzężeniach krzyżowych w klauzuli WHERE .

Rozważ następujące zapytanie:

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

W przypadku tego zapytania indeks pasuje do dowolnego elementu, który ma tag z wartością keyfabric lub material, co najmniej jeden rozmiar o order wartości większej niż *trzy, a co najmniej jeden kolor gray jako podciąg. Wyrażenie JOIN w tym miejscu wykonuje krzyżowy produkt wszystkich elementów tags, sizesi colors tablic dla każdego pasującego elementu przed zastosowaniem dowolnego filtru.

Klauzula WHERE stosuje następnie predykat filtru dla każdej krotki $<c, t, n, s>$. Na przykład jeśli pasujący element miał dziesięć elementów w każdej z trzech tablic, rozszerza się do 1000 krotek przy użyciu tej formuły:

$$1 x 10 x 10 x 10$$

Użycie podzapytania może pomóc w odfiltrowaniu sprzężonych elementów tablicy przed dołączeniem do następnego wyrażenia.

To zapytanie jest równoważne poprzedniemu, ale używa podzapytania:

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

Załóżmy, że tylko jeden element w tablicy tagów jest zgodny z filtrem i istnieje pięć elementów zarówno dla liczby, jak i tablic magazynowych. Następnie JOIN wyrażenie rozszerza się do 25 krotek przy użyciu tej formuły, w przeciwieństwie do 1000 elementów w pierwszym zapytaniu:

$$1 x 1 x 5 x 5$$

Ocenianie raz i odwołuje się wiele razy

Podzapytania mogą ułatwić optymalizowanie zapytań za pomocą kosztownych wyrażeń, takich jak funkcje zdefiniowane przez użytkownika (UDF), złożone ciągi lub wyrażenia arytmetyczne. Możesz użyć podzapytania wraz z wyrażeniem JOIN , aby obliczyć wyrażenie raz, ale odwoływać się do niego wiele razy.

To przykładowe zapytanie oblicza cenę z dodatkiem 25% wiele razy w zapytaniu.

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

Oto równoważne zapytanie, które uruchamia obliczenia tylko raz:

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

Wskazówka

Należy pamiętać o zachowaniu JOIN wyrażeń między produktami. Jeśli wyrażenie może mieć wartość undefined, należy upewnić się, że JOIN wyrażenie zawsze generuje pojedynczy wiersz, zwracając obiekt z podzapytania, a nie bezpośrednio.

Naśladować sprzężenia relacyjne z zewnętrznymi danymi referencyjnymi

Często może być konieczne odwołanie się do danych statycznych, które rzadko się zmieniają, takich jak jednostki miary. Jest to idealne rozwiązanie, aby nie duplikować danych statycznych dla każdego elementu w zapytaniu. Unikanie tego duplikowania pozwala zaoszczędzić na magazynie i zwiększyć wydajność zapisu, zachowując mniejszy rozmiar pojedynczego elementu. Za pomocą podzapytania można naśladować semantykę sprzężenia wewnętrznego z kolekcją statycznych danych referencyjnych.

Rozważmy na przykład ten zestaw pomiarów, które reprezentują długość odzieży:

Rozmiar	Długość	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

Następujące zapytanie naśladuje dołączenie do tych danych, aby dodać nazwę lekcji do danych wyjściowych:

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

Podzapytania skalarne

Wyrażenie podzapytania skalarnego to podzapytywanie, które daje w wyniku pojedynczą wartość. Wartość wyrażenia podzapytania skalarnego jest wartością projekcji (SELECT klauzuli) podzapytania. W wielu miejscach można użyć wyrażenia podrzędnego skalarnego, w którym wyrażenie skalarne jest prawidłowe. Można na przykład użyć podzapytania skalarnego w dowolnym wyrażeniu SELECT w klauzulach i WHERE .

Użycie podzapytania skalarnego nie zawsze pomaga zoptymalizować zapytanie. Na przykład przekazanie podzapytania skalarnego jako argumentu do funkcji zdefiniowanych przez system lub użytkownika nie zapewnia korzyści w zmniejszeniu zużycia jednostek zasobów (RU) ani opóźnienia.

Podzapytania skalarne mogą być dalej klasyfikowane jako:

• Podzapytania skalarne wyrażeń prostych
• Agregowanie podzapytania skalarnych

Podzapytania skalarne wyrażeń prostych

Podzapytywanie skalarne wyrażeń prostych jest skorelowanym podzapytaniem zawierającym klauzulę SELECT , która nie zawiera żadnych wyrażeń agregujących. Te podzapytania nie zapewniają żadnych korzyści optymalizacji, ponieważ kompilator konwertuje je na jedno większe proste wyrażenie. Nie ma skorelowanego kontekstu między zapytaniami wewnętrznymi i zewnętrznymi.

Jako pierwszy przykład rozważ to proste zapytanie.

SELECT
  1 AS a,
  2 AS b

Możesz ponownie napisać to zapytanie, używając podzapytania skalarnego prostego wyrażenia.

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

Oba zapytania generują te same dane wyjściowe.

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

To następne przykładowe zapytanie łączy unikatowy identyfikator z prefiksem jako podzapytywaniem skalarnym prostego wyrażenia.

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

W tym przykładzie użyto podzapytania skalarnego prostego wyrażenia, aby zwrócić tylko odpowiednie pola dla każdego elementu. Zapytanie zwraca coś dla każdego elementu, ale zawiera tylko pole przewidywane, jeśli spełnia filtr w podzapytaniu.

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

Agregowanie podzapytania skalarnych

Podzapytywanie skalarne agregacji to podzapytywanie, które ma funkcję agregacji w projekcji lub filtrze, który daje jedną wartość.

Jako pierwszy przykład rozważ element z następującymi polami.

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

Oto podzapytywanie z pojedynczym wyrażeniem funkcji agregującej w projekcji. To zapytanie zlicza wszystkie tagi dla każdego elementu.

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

Oto ta sama podzapytywanie z 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
  }
]

Oto kolejna podzapytywanie z wieloma wyrażeniami funkcji agregacji:

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

Na koniec oto zapytanie z zagregowanym podzapytaniem zarówno w projekcji, jak i w filtrze:

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

Bardziej optymalnym sposobem na napisanie tego zapytania jest sprzężenie w podzapytaniu i odwołanie do aliasu podzapytania zarówno w klauzulach SELECT, jak i WHERE. To zapytanie jest bardziej wydajne, ponieważ należy wykonać podzapytywanie tylko w instrukcji join, a nie w projekcji i filtrze.

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

Wyrażenie EXISTS

Język zapytań obsługuje EXISTS wyrażenia. To wyrażenie jest agregowanym podzapytaniem skalarnym wbudowanym w język zapytań. EXISTS przyjmuje wyrażenie podzapytania i zwraca true wartość , jeśli podzapytywanie zwraca dowolne wiersze. W przeciwnym razie zwraca false.

Ponieważ aparat zapytań nie rozróżnia wyrażeń logicznych i innych wyrażeń skalarnych, można użyć EXISTS w klauzulach SELECT i WHERE . To zachowanie jest w przeciwieństwie do języka T-SQL, gdzie wyrażenie logiczne jest ograniczone tylko do filtrów.

EXISTS Jeśli podzapytywanie zwraca pojedynczą wartość undefined, EXISTS zwraca wartość false. Rozważmy na przykład następujące zapytanie, które nie zwraca niczego.

SELECT VALUE
  undefined

Jeśli używasz EXISTS wyrażenia i poprzedniego zapytania jako podzapytania, wyrażenie zwraca wartość false.

SELECT VALUE
  EXISTS (SELECT VALUE undefined)

[
  false
]

Jeśli pominięto słowo kluczowe VALUE w poprzednim podzapytaniu, podzapytywanie jest obliczane na tablicę z pojedynczym pustym obiektem.

SELECT
  undefined

[
  {}
]

W tym momencie EXISTS wyrażenie daje wartość , true ponieważ obiekt ({}) kończy się technicznie.

SELECT VALUE
  EXISTS (SELECT undefined)

[
  true
]

Typowym przypadkiem ARRAY_CONTAINS użycia elementu jest filtrowanie elementu przez istnienie elementu w tablicy. W tym przypadku sprawdzamy, czy tablica tags zawiera element o nazwie "odzież zewnętrzna".

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

To samo zapytanie może być używane EXISTS jako alternatywna opcja.

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

Ponadto można sprawdzić tylko, ARRAY_CONTAINS czy wartość jest równa dowolnemu elementowi w tablicy. Jeśli potrzebujesz bardziej złożonych filtrów we właściwościach tablicy, użyj JOIN zamiast tego.

Rozważmy ten przykładowy element w zestawie z wieloma elementami zawierającymi tablicę accessories .

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

Teraz rozważ następujące zapytanie, które filtruje na type podstawie właściwości i quantityOnHand w tablicy w ramach każdego elementu.

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

Dla każdego elementu w kolekcji element między produktami jest wykonywany z elementami tablicy. Ta JOIN operacja umożliwia filtrowanie właściwości w tablicy. Jednak użycie jednostek RU tego zapytania jest znaczące. Jeśli na przykład 1000 elementów miało 100 elementów w każdej tablicy, rozszerza się do 100 000 krotek przy użyciu tej formuły:

$$1,000 x 100$$

Użycie EXISTS pomaga uniknąć tego kosztownego produktu krzyżowego. W następnym przykładzie zapytanie filtruje elementy tablicy w podzapytaniu EXISTS . Jeśli element tablicy jest zgodny z filtrem, projektujesz go i EXISTS obliczasz wartość 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"
]

Zapytania mogą również aliasować EXISTS i odwoływać się do aliasu w projekcji:

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

Wyrażenie ARRAY

Możesz użyć ARRAY wyrażenia do projekcji wyników zapytania jako tablicy. To wyrażenie można użyć tylko w SELECT klauzuli zapytania.

W tych przykładach załóżmy, że istnieje kontener z co najmniej tym elementem.

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

W tym pierwszym przykładzie wyrażenie jest używane w klauzuli SELECT .

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

Podobnie jak w przypadku innych podzapytania, możliwe są filtry z wyrażeniem 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"
    ]
  }
]

Wyrażenia tablicowe mogą również pochodzić po klauzuli w podzapytaniach FROM .

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

Treści powiązane

• Przeglądanie języka zapytań
• Wprowadzenie do formatu JSON w języku zapytań
• Wykonywanie samosprzężeń
• Eksplorowanie funkcji systemowych