Underfrågor i Azure Cosmos DB för NoSQL

GÄLLER FÖR: NoSQL

En underfråga är en fråga kapslad i en annan fråga i Azure Cosmos DB för NoSQL. 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.

Anteckning

Azure Cosmos DB stöder endast korrelerade underfrågor.

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 Azure Cosmos DB för NoSQL 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 alla korskopplingar i WHERE -satsen.

Överväg följande fråga:

SELECT VALUE
    COUNT(1)
FROM
    products p
JOIN 
    t in p.tags
JOIN 
    q in p.onHandQuantities
JOIN 
    s in p.warehouseStock
WHERE 
    t.name IN ("winter", "fall") AND
    (q.quantity BETWEEN 0 AND 10) AND
    NOT s.backstock

För den här frågan matchar indexet alla objekt som har en tagg med antingen name"vinter" eller "fall", minst en quantity mellan noll och tio och minst ett lager där backstock är false. Uttrycket JOIN här utför korsprodukten av alla objekt i tags, onHandQuantitiesoch warehouseStock matriser för varje matchande objekt innan något filter tillämpas.

Satsen WHERE tillämpar sedan filterpredikatet på varje <c, t, n, s> tupplar. Om ett matchande objekt till exempel hade tio objekt i var och en av de tre matriserna expanderas det till 1 x 10 x 10 x 10 (dvs. 1 000) tupplar. Om du använder underfrågor här kan du filtrera bort anslutna 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.name IN ("winter", "fall"))
JOIN 
    (SELECT VALUE q FROM q IN p.onHandQuantities WHERE q.quantity BETWEEN 0 AND 10)
JOIN 
    (SELECT VALUE s FROM s IN p.warehouseStock WHERE NOT s.backstock)

Anta att endast ett objekt i taggarnas matris matchar filtret och att det finns fem objekt för både näringsämnen och serveringsmatriser. Uttrycken JOIN expanderar sedan till 1 x 1 x 5 x 5 (25) objekt, till skillnad från 1 000 objekt i den första frågan.

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

Underfrågor kan hjälpa dig 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.

Anta att du har definierat följande UDF (getTotalWithTax).

function getTotalWithTax(subTotal){
  return subTotal * 1.25;
}

Följande fråga kör UDF getTotalWithTax flera gånger:

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

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

SELECT VALUE {
    subtotal: p.price,
    total: totalPrice
}
FROM
    products p
JOIN
    (SELECT VALUE udf.getTotalWithTax(p.price)) totalPrice
WHERE
    totalPrice < 22.25

Tips

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

Efterlikna koppling 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 blir 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:

	Namn	Multiplikator	Basenhet
ng	Nanogram	1.00E-09	Gram
µg	Mikrogram	1.00E-06	Gram
mg	Milligram	1.00E-03	Gram
g	Gram	1.00E+00	Gram
kg	Kilogram	1.00E+03	Gram
Mg	Megagram	1.00E+06	Gram
Gg	Gigagram	1.00E+09	Gram

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

SELECT
    s.id,
    (s.weight.quantity * m.multiplier) AS calculatedWeight,
    m.unit AS unitOfWeight
FROM
    shipments s
JOIN m 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'}
    ]
)
WHERE
    s.weight.units = m.unit

Skalär 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 underkryuttrycket är värdet för projektionen (SELECT -satsen) för underfrågan. Du kan använda ett skalärt underkärt uttryck 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. Om du till exempel skickar en skalär underfråga som ett argument till antingen ett system eller användardefinierade funktioner har du ingen fördel när det gäller att minska resursenhetsförbrukningen (RU) eller svarstiden.

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

Nästa exempelfråga sammanfogar 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, "glove")).name
FROM
    products p

[
  {
    "id": "03230",
    "name": "Winter glove"
  },
  {
    "id": "03238"
  },
  {
    "id": "03229"
  }
]

Aggregera skalära underfrågor

En aggregerad skalär underfråga är en underfråga som har en mängdfunktion 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": "Snow coat",
  "inventory": [
    {
      "location": "Redmond, WA",
      "quantity": 50
    },
    {
      "location": "Seattle, WA",
      "quantity": 30
    },
    {
      "location": "Washington, DC",
      "quantity": 25
    }
  ]
}

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 i IN p.inventory) AS locationCount
FROM
    products p

[
  {
    "name": "Snow coat",
    "locationCount": 3
  }
]

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

SELECT
    p.name,
    (SELECT VALUE COUNT(1) FROM i IN p.inventory WHERE ENDSWITH(i.location, "WA")) AS washingtonLocationCount
FROM
    products p

[
  {
    "name": "Snow coat",
    "washingtonLocationCount": 2
  }
]

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

SELECT
    p.name,
    (SELECT
        COUNT(1) AS locationCount,
        SUM(i.quantity) AS totalQuantity
    FROM i IN p.inventory) AS inventoryData
FROM
    products p

[
  {
    "name": "Snow coat",
    "inventoryData": {
      "locationCount": 2,
      "totalQuantity": 75
    }
  }
]

Här är slutligen en fråga med en mängdunderfråga i både projektionen och filtret:

SELECT
    p.name,
    (SELECT VALUE AVG(q.quantity) FROM q IN p.inventory WHERE q.quantity > 10) AS averageInventory
FROM
    products p
WHERE
    (SELECT VALUE COUNT(1) FROM i IN p.inventory WHERE i.quantity > 10) >= 1

[
  {
    "name": "Snow coat",
    "averageInventory": 35
  }
]

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 mer effektiv eftersom du bara behöver köra underfrågan i kopplingsinstruktionen och inte i både projektionen och filtret.

SELECT
    p.name,
    inventoryData.inventoryAverage
FROM
    products p
JOIN
    (SELECT 
        COUNT(1) AS inventoryCount, 
        AVG(i.quantity) as inventoryAverage 
    FROM i IN p.inventory 
    WHERE i.quantity > 10) AS inventoryData
WHERE
    inventoryData.inventoryCount >= 1

EXISTS-uttryck

Azure Cosmos DB för NoSQL:s frågemotor stöder EXISTS uttryck. Det här uttrycket är en aggregerad skalär underfråga som är inbyggd i Azure Cosmos DB för NoSQL. EXISTS tar ett underkry-uttryck 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ågonting.

SELECT VALUE
    undefined

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

SELECT
    EXISTS (SELECT VALUE undefined)

[
  {
    "$1": 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 
    EXISTS (SELECT undefined) 

[
  {
    "$1": true
  }
]

Ett vanligt användningsfall ARRAY_CONTAINS för ä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.tags
FROM
    products p
WHERE
    ARRAY_CONTAINS(p.tags, "outerwear")

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

SELECT
    p.name,
    p.tags
FROM
    products p
WHERE
    EXISTS (SELECT VALUE t FROM t IN p.tags WHERE t = "outerwear")

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.

Tänk på det här exempelobjektet i en uppsättning med flera objekt som var och en innehåller en accessories matris.

{
  "name": "Unobtani road bike",
  "accessories": [
    {
      "name": "Front/rear tire",
      "type": "tire",
      "quantityOnHand": 5
    },
    {
      "name": "9-speed chain",
      "type": "chains",
      "quantityOnHand": 25
    },
    {
      "name": "Clip-in pedals",
      "type": "pedals",
      "quantityOnHand": 15
    }
  ]
}

Överväg nu följande fråga som filtrerar baserat på type egenskaperna och quantityOnHand i matrisen i varje objekt.

SELECT
    p.name,
    a.name AS accessoryName
FROM
    products p
JOIN
    a IN p.accessories
WHERE
    a.type = "chains" AND
    a.quantityOnHand >= 10

[
  {
    "name": "Unobtani road bike",
    "accessoryName": "9-speed chain"
  }
]

För vart och ett av objekten 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 1,000 x 100 (dvs . 100 000) tupplar.

Att använda EXISTS kan hjälpa 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 till sant.

SELECT VALUE
    p.name
FROM
    products p
WHERE
    EXISTS (SELECT VALUE 
        a 
    FROM 
        a IN p.accessories
    WHERE
        a.type = "chains" AND
        a.quantityOnHand >= 10)

[
  "Unobtani road bike"
]

Frågor kan också alias EXISTS och referera till aliaset i projektionen:

SELECT
    p.name,
    EXISTS (SELECT VALUE
        a 
    FROM 
        a IN p.accessories
    WHERE
        a.type = "chains" AND
        a.quantityOnHand >= 10) AS chainAccessoryAvailable
FROM
    products p

[
  {
    "name": "Unobtani road bike",
    "chainAccessoryAvailable": 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 åtminstone det här objektet.

{
  "name": "Radimer mountain bike",
  "tags": [
    {
      "name": "road"
    },
    {
      "name": "bike"
    },
    {
      "name": "competitive"
    }
  ]
}

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

SELECT
    p.name,
    ARRAY (SELECT VALUE t.name FROM t in p.tags) AS tagNames
FROM
    products p

[
  {
    "name": "Radimer mountain bike",
    "tagNames": [
      "road",
      "bike",
      "competitive"
    ]
  }
]

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

SELECT
    p.name,
    ARRAY (SELECT VALUE t.name FROM t in p.tags) AS tagNames,
    ARRAY (SELECT VALUE t.name FROM t in p.tags WHERE CONTAINS(t.name, "bike")) AS bikeTagNames
FROM
    products p

[
  {
    "name": "Radimer mountain bike",
    "tagNames": [
      "road",
      "bike",
      "competitive"
    ],
    "bikeTagNames": [
      "bike"
    ]
  }
]

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

SELECT
    p.name,
    n.t.name AS nonBikeTagName
FROM
    products p
JOIN
    n IN (SELECT VALUE ARRAY(SELECT t FROM t in p.tags WHERE t.name NOT LIKE "%bike%"))

[
  {
    "name": "Radimer mountain bike",
    "nonBikeTagName": "road"
  },
  {
    "name": "Radimer mountain bike",
    "nonBikeTagName": "competitive"
  }
]

Relaterat innehåll

• JOIN Klausul
• Konstanter
• Nyckelord