Underfrågor – Frågespråk i Cosmos DB (i Azure och Fabric)

En underfråga är en fråga kapslad i en annan fråga i frågespråket. En underfråga kallas även för en inre fråga eller en inre SELECT. Instruktionen som innehåller en underfråga kallas vanligtvis för en yttre fråga.

Typer av underfrågor

Det finns två huvudtyper av underfrågor:

• Korrelerad: En underfråga som refererar till värden från den yttre frågan. Underfrågan utvärderas en gång för varje rad som den yttre frågan bearbetar.
• Icke-korrelerad: En underfråga som är oberoende av den yttre frågan. Den kan köras på egen hand utan att förlita sig på den yttre frågan.

Underfrågor kan klassificeras ytterligare baserat på antalet rader och kolumner som de returnerar. Det finns tre typer:

• Tabell: Returnerar flera rader och flera kolumner.
• Flera värden: Returnerar flera rader och en enda kolumn.
• Skalär: Returnerar en enskild rad och en enda kolumn.

Frågor i frågespråket returnerar alltid en enda kolumn (antingen ett enkelt värde eller ett komplext objekt). Därför gäller endast underfrågor med flera värden och skalär. Du kan endast använda en underfråga med flera värden i FROM -satsen som ett relationsuttryck. Du kan använda en skalär underfråga som ett skalärt uttryck i -instruktionen SELECT eller WHERE som ett relationsuttryck i FROM -satsen.

Underfrågor med flera värden

Underfrågor med flera värden returnerar en uppsättning objekt och används alltid i FROM -satsen. De används för:

• JOIN Optimera (självkoppling) uttryck.
• Utvärdera dyra uttryck en gång och referera till flera gånger.

Optimera självkopplingsuttryck

Underfrågor med flera värden kan optimera JOIN uttryck genom att push-överföra predikat efter varje select-many-uttryck i stället för efter alla korskopplingar i WHERE -satsen.

Överväg följande fråga:

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

För den här frågan matchar indexet alla objekt som har en tagg med antingen keyfabric eller material, minst en storlek med ett order värde större än *tre och minst en färg med gray som understräng. Uttrycket JOIN här utför korsprodukten av alla objekt tagsi , sizesoch colors matriser för varje matchande objekt innan något filter tillämpas.

Satsen WHERE tillämpar sedan filterpredikatet på varje tupplar för $<c, t, n, s>$ Om ett matchande objekt till exempel hade tio objekt i var och en av de tre matriserna expanderas det till 1 000 tupplar med hjälp av den här formeln:

$$1 x 10 x 10 x 10$$

Om du använder underfrågor här kan du filtrera bort kopplade matrisobjekt innan du ansluter till nästa uttryck.

Den här frågan motsvarar den föregående, men använder underfrågor:

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

Anta att endast ett objekt i taggarnas matris matchar filtret och att det finns fem objekt för både kvantitets- och lagermatriser. Uttrycket JOIN expanderar sedan till 25 tupplar med den här formeln i stället för 1 000 objekt i den första frågan:

$$1 x 1 x 5 x 5$$

Utvärdera en gång och referens många gånger

Underfrågor kan hjälpa till att optimera frågor med dyra uttryck som användardefinierade funktioner (UDF), komplexa strängar eller aritmetiska uttryck. Du kan använda en underfråga tillsammans med ett JOIN uttryck för att utvärdera uttrycket en gång men referera till det många gånger.

Den här exempelfrågan beräknar priset med ett tillägg på 25% multiplar gånger i frågan.

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

Här är en motsvarande fråga som bara kör beräkningen en gång:

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

Tips/Råd

Tänk på uttryckens beteende JOIN mellan produkter. Om uttrycket kan utvärderas till bör du se till undefinedatt JOIN uttrycket alltid skapar en enskild rad genom att returnera ett objekt från underfrågan i stället för värdet direkt.

Efterlikna relationskoppling med externa referensdata

Du kan ofta behöva referera till statiska data som sällan ändras, till exempel måttenheter. Det är idealiskt att inte duplicera statiska data för varje objekt i en fråga. Om du undviker den här dupliceringen sparas lagring och skrivprestanda förbättras genom att den enskilda objektstorleken hålls mindre. Du kan använda en underfråga för att efterlikna inre kopplingssemantik med en samling statiska referensdata.

Tänk till exempel på den här uppsättningen mått som representerar längden på ett plagg:

Storlek	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

Följande fråga efterliknar sammanfogning med dessa data så att du lägger till namnet på enheten i utdata:

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ära underfrågor

Ett skalärt underfrågasuttryck är en underfråga som utvärderas till ett enda värde. Värdet för det skalära underquery-uttrycket är värdet för underfrågans projektion (SELECT -sats). Du kan använda ett skalärt underfrågasuttryck på många platser där ett skalärt uttryck är giltigt. Du kan till exempel använda en skalär underfråga i valfritt uttryck i både SELECT - och-satserna WHERE .

Att använda en skalär underfråga hjälper inte alltid till att optimera frågan. Att till exempel skicka en skalär underfråga som ett argument till antingen ett system eller användardefinierade funktioner ger ingen fördel när det gäller att minska förbrukningen av resursenheter (RU) eller svarstiden.

Skalära underfrågor kan klassificeras ytterligare som:

• Skalära underfrågor med enkla uttryck
• Aggregera skalära underfrågor

Skalära underfrågor med enkla uttryck

En skalär underfråga med enkla uttryck är en korrelerad underfråga som har en SELECT sats som inte innehåller några aggregerade uttryck. Dessa underfrågor ger inga optimeringsfördelar eftersom kompilatorn konverterar dem till ett större enkelt uttryck. Det finns ingen korrelerad kontext mellan de inre och yttre frågorna.

Som ett första exempel bör du överväga den här triviala frågan.

SELECT
  1 AS a,
  2 AS b

Du kan skriva om den här frågan med hjälp av en skalär underfråga med enkla uttryck.

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

Båda frågorna genererar samma utdata.

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

I nästa exempelfråga sammanfogas den unika identifieraren med ett prefix som en skalär underfråga med enkla uttryck.

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

I det här exemplet används en skalär underfråga med enkla uttryck för att endast returnera relevanta fält för varje objekt. Frågan matar ut något för varje objekt, men den innehåller bara det projicerade fältet om det uppfyller filtret i underfrågan.

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

Aggregera skalära underfrågor

En aggregerad skalär underfråga är en underfråga som har en aggregeringsfunktion i projektionen eller filtret som utvärderas till ett enda värde.

Som ett första exempel bör du överväga ett objekt med följande fält.

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

Här är en underfråga med ett enda mängdfunktionsuttryck i projektionen. Den här frågan räknar alla taggar för varje objekt.

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

Här är samma underfråga med ett filter.

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

Här är en annan underfråga med flera mängdfunktionsuttryck:

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

Slutligen är här en fråga med en aggregerad underfråga i både projektionen och filtret:

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

Ett mer optimalt sätt att skriva den här frågan är att ansluta till underfrågan och referera till underfrågans alias i både SELECT- och WHERE-satserna. Den här frågan är effektivare eftersom du bara behöver köra underfrågan i kopplingsinstruktionen och inte i både projektionen och filtret.

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

EXISTS-uttryck

Frågespråket stöder EXISTS uttryck. Det här uttrycket är en aggregerad skalär underfråga som är inbyggd i frågespråket. EXISTS tar ett underfrågasuttryck och returnerar true om underfrågan returnerar några rader. Annars returneras false.

Eftersom frågemotorn inte skiljer mellan booleska uttryck och andra skalära uttryck kan du använda EXISTS i både SELECT - och WHERE -satser. Det här beteendet skiljer sig från T-SQL, där ett booleskt uttryck begränsas till endast filter.

EXISTS Om underfrågan returnerar ett enda värde som är undefined, EXISTS utvärderas till false. Tänk till exempel på följande fråga som inte returnerar något.

SELECT VALUE
  undefined

Om du använder EXISTS uttrycket och föregående fråga som en underfråga returnerar falseuttrycket .

SELECT VALUE
  EXISTS (SELECT VALUE undefined)

[
  false
]

Om nyckelordet VALUE i föregående underfråga utelämnas utvärderas underfrågan till en matris med ett enda tomt objekt.

SELECT
  undefined

[
  {}
]

EXISTS Då utvärderas uttrycket till true eftersom objektet ({}) tekniskt avslutas.

SELECT VALUE
  EXISTS (SELECT undefined)

[
  true
]

Ett vanligt användningsfall ARRAY_CONTAINS är att filtrera ett objekt efter förekomsten av ett objekt i en matris. I det här fallet kontrollerar vi om matrisen tags innehåller ett objekt med namnet "outerwear".

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

Samma fråga kan användas EXISTS som ett alternativt alternativ.

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

Kan dessutom ARRAY_CONTAINS bara kontrollera om ett värde är lika med alla element i en matris. Om du behöver mer komplexa filter för matrisegenskaper använder du JOIN i stället.

Överväg det här exempelobjektet i en uppsättning med flera objekt som var och en innehåller en accessories matris.

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

Tänk dig nu följande fråga som filtrerar baserat på type egenskaperna och quantityOnHand i matrisen i varje objekt.

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

För varje objekt i samlingen utförs en korsprodukt med dess matriselement. Den här JOIN åtgärden gör det möjligt att filtrera efter egenskaper i matrisen. Den här frågans RU-förbrukning är dock betydande. Om till exempel 1 000 objekt hade 100 objekt i varje matris expanderas det till 100 000 tupplar med den här formeln:

$$1 000 x 100$$

Att använda EXISTS hjälper till att undvika denna dyra korsprodukt. I nästa exempel filtrerar frågan på matriselement i EXISTS underfrågan. Om ett matriselement matchar filtret projicerar du det och EXISTS utvärderar det till sant.

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

Frågor tillåts även alias EXISTS och referera till aliaset i projektionen:

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

ARRAY-uttryck

Du kan använda ARRAY uttrycket för att projicera resultatet av en fråga som en matris. Du kan bara använda det här uttrycket i SELECT frågans sats.

I de här exemplen antar vi att det finns en container med minst det här objektet.

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

I det första exemplet används uttrycket i SELECT -satsen.

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

Precis som med andra underfrågor är filter med ARRAY uttrycket möjliga.

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

Matrisuttryck kan också komma efter FROM -satsen i underfrågor.

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

Relaterat innehåll

• Granska frågespråket
• Kom igång med JSON på frågespråket
• Utföra självkopplingar
• Utforska systemfunktioner