Subquery's in Azure Cosmos DB for NoSQL

VAN TOEPASSING OP: NoSQL

Een subquery is een query die is genest binnen een andere query in Azure Cosmos DB voor NoSQL. Een subquery wordt ook wel een binnenste query of binnenste SELECTquery genoemd. De instructie die een subquery bevat, wordt meestal een buitenste query genoemd.

Typen subquery's

Er zijn twee hoofdtypen subquery's:

• Gecorreleerd: een subquery die verwijst naar waarden uit de buitenste query. De subquery wordt eenmaal geëvalueerd voor elke rij die door de buitenste query wordt verwerkt.
• Niet-gecorreleerd: een subquery die onafhankelijk is van de buitenste query. Deze kan zelfstandig worden uitgevoerd zonder dat deze afhankelijk is van de buitenste query.

Notitie

Azure Cosmos DB ondersteunt alleen gecorreleerde subquery's.

Subquery's kunnen verder worden geclassificeerd op basis van het aantal rijen en kolommen dat ze retourneren. Er zijn drie typen:

• Tabel: Retourneert meerdere rijen en meerdere kolommen.
• Meerdere waarden: retourneert meerdere rijen en één kolom.
• Scalar: retourneert één rij en één kolom.

Query's in Azure Cosmos DB for NoSQL retourneren altijd één kolom (een eenvoudige waarde of een complex item). Daarom zijn alleen subquery's met meerdere waarden en scalaire subquery's van toepassing. U kunt een subquery met meerdere waarden alleen in de FROM component gebruiken als een relationele expressie. U kunt een scalaire subquery gebruiken als een scalaire expressie in de SELECT of WHERE component, of als een relationele expressie in de FROM component.

Subquery's met meerdere waarden

Subquery's met meerdere waarden retourneren een set items en worden altijd binnen de FROM component gebruikt. Ze worden gebruikt voor:

• JOIN Expressies (self-join) optimaliseren.
• Dure expressies één keer evalueren en meerdere keren verwijzen.

Self-join-expressies optimaliseren

Subquery's met meerdere waarden kunnen expressies optimaliseren JOIN door predicaten na elke select-many-expressie te pushen in plaats van na alle cross-joins in de WHERE component.

Houd rekening met de volgende query:

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

Voor deze query komt de index overeen met elk item met een tag met een van de name 'winter' of 'herfst', ten minste één quantity tussen nul en tien, en ten minste één magazijn waar het backstock isfalse. De JOIN expressie hier voert het kruisproduct uit van alle items van tags, onHandQuantitiesen warehouseStock matrices voor elk overeenkomend item voordat een filter wordt toegepast.

De WHERE component past vervolgens het filterpredicaat toe op elke <c, t, n, s> tuple. Als een overeenkomend item bijvoorbeeld tien items in elk van de drie matrices bevat, wordt het uitgebreid tot 1 x 10 x 10 x 10 (dat wil gezegd 1000) tuples. Het gebruik van subquery's hier kan helpen bij het filteren van gekoppelde matrixitems voordat u met de volgende expressie gaat samenvoegen.

Deze query is gelijk aan de voorgaande, maar gebruikt subquery's:

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)

Stel dat slechts één item in de tagsmatrix overeenkomt met het filter en dat er vijf items zijn voor zowel hoeveelheid als voorraadmatrices. De JOIN expressies worden vervolgens uitgebreid naar 1 x 1 x 5 x 5 (25) items, in plaats van 1000 items in de eerste query.

Eenmaal evalueren en vaak verwijzen

Met subquery's kunt u query's optimaliseren met dure expressies, zoals door de gebruiker gedefinieerde functies (UDF's), complexe tekenreeksen of rekenkundige expressies. U kunt een subquery samen met een JOIN expressie gebruiken om de expressie eenmaal te evalueren, maar vaak ernaar te verwijzen.

Stel dat u de volgende UDF (getTotalWithTax) hebt gedefinieerd.

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

Met de volgende query wordt de UDF getTotalWithTax meerdere keren uitgevoerd:

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

Hier volgt een equivalente query die de UDF slechts één keer uitvoert:

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

Tip

Houd rekening met het gedrag van JOIN expressies voor meerdere producten. Als de UDF-expressie kan evalueren undefined, moet u ervoor zorgen dat de JOIN expressie altijd één rij produceert door een object uit de subquery te retourneren in plaats van de waarde rechtstreeks.

Samenvoegen met externe referentiegegevens nabootsen

Mogelijk moet u vaak verwijzen naar statische gegevens die zelden veranderen, zoals maateenheden. Het is ideaal om statische gegevens niet te dupliceren voor elk item in een query. Als u deze duplicatie vermijdt, bespaart u opslag en verbetert u de schrijfprestaties door de afzonderlijke itemgrootte kleiner te houden. U kunt een subquery gebruiken om semantiek van inner-joins na te bootsen met een verzameling statische referentiegegevens.

Denk bijvoorbeeld aan deze set metingen:

	Naam	Vermenigvuldigingsfactor	Basiseenheid
ng	Nanogram	1.00E-09	Gram
µg	Microgram	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

Met de volgende query wordt het samenvoegen van deze gegevens nagebootst, zodat u de naam van de eenheid toevoegt aan de uitvoer:

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

Scalaire subquery's

Een scalaire subquery-expressie is een subquery die één waarde oplevert. De waarde van de scalaire subquery-expressie is de waarde van de projectie (SELECT component) van de subquery. U kunt een scalaire subquery-expressie gebruiken op veel plaatsen waar een scalaire expressie geldig is. U kunt bijvoorbeeld een scalaire subquery gebruiken in elke expressie in zowel de SELECT als WHERE de component.

Het gebruik van een scalaire subquery helpt niet altijd om uw query te optimaliseren. Het doorgeven van een scalaire subquery als argument aan een systeem of door de gebruiker gedefinieerde functies biedt bijvoorbeeld geen voordeel bij het verminderen van ru-verbruik (Resource Unit) of latentie.

Scalaire subquery's kunnen verder worden geclassificeerd als:

• Scalaire scalaire subquery's voor eenvoudige expressies
• Scalaire subquery's aggregeren

Scalaire scalaire subquery's voor eenvoudige expressies

Een scalaire scalaire subquery voor eenvoudige expressies is een gecorreleerde subquery met een SELECT component die geen statistische expressies bevat. Deze subquery's bieden geen optimalisatievoordelen omdat de compiler deze converteert naar een grotere eenvoudige expressie. Er is geen gecorreleerde context tussen de binnenste en buitenste query's.

Bekijk deze triviale query als eerste voorbeeld.

SELECT
    1 AS a,
    2 AS b

U kunt deze query opnieuw schrijven met behulp van een scalaire scalaire subquery voor eenvoudige expressies.

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

Beide query's produceren dezelfde uitvoer.

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

Met deze volgende voorbeeldquery wordt de unieke id samengevoegd met een voorvoegsel als scalaire scalaire subquery voor eenvoudige expressies.

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

In dit voorbeeld wordt een scalaire scalaire scalaire subquery gebruikt om alleen de relevante velden voor elk item te retourneren. De query voert iets uit voor elk item, maar bevat alleen het verwachte veld als het voldoet aan het filter in de subquery.

SELECT
    p.id,
    (SELECT p.name WHERE CONTAINS(p.name, "glove")).name
FROM
    products p

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

Scalaire subquery's aggregeren

Een geaggregeerde scalaire subquery is een subquery met een statistische functie in de projectie of het filter die resulteert in één waarde.

Bekijk als eerste voorbeeld een item met de volgende velden.

{
  "name": "Snow coat",
  "inventory": [
    {
      "location": "Redmond, WA",
      "quantity": 50
    },
    {
      "location": "Seattle, WA",
      "quantity": 30
    },
    {
      "location": "Washington, DC",
      "quantity": 25
    }
  ]
}

Hier volgt een subquery met één statistische functie-expressie in de projectie. Met deze query worden alle tags voor elk item geteld.

SELECT
    p.name,
    (SELECT VALUE COUNT(1) FROM i IN p.inventory) AS locationCount
FROM
    products p

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

Hier volgt dezelfde subquery met een 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
  }
]

Hier volgt een andere subquery met meerdere expressies voor statistische functies:

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

Ten slotte ziet u hier een query met een geaggregeerde subquery in zowel de projectie als het filter:

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

Een optimale manier om deze query te schrijven, is door deel te nemen aan de subquery en te verwijzen naar de subqueryalias in zowel de SELECT- als WHERE-componenten. Deze query is efficiënter omdat u de subquery alleen binnen de join-instructie moet uitvoeren en niet in zowel de projectie als het filter.

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-expressie

De query-engine van Azure Cosmos DB for NoSQL ondersteunt EXISTS expressies. Deze expressie is een geaggregeerde scalaire subquery die is ingebouwd in Azure Cosmos DB for NoSQL. EXISTS gebruikt een subquery-expressie en retourneert true als de subquery rijen retourneert. Anders wordt het geretourneerd false.

Omdat de query-engine geen onderscheid maakt tussen booleaanse expressies en andere scalaire expressies, kunt u in beide SELECT en WHERE componenten gebruikenEXISTS. Dit gedrag is in tegenstelling tot T-SQL, waarbij een Boole-expressie wordt beperkt tot alleen filters.

Als de EXISTS subquery één waarde undefinedretourneert, EXISTS resulteert dit in onwaar. Denk bijvoorbeeld aan de volgende query die niets retourneert.

SELECT VALUE
    undefined

Als u de EXISTS expressie en de voorgaande query als subquery gebruikt, retourneert falsede expressie.

SELECT
    EXISTS (SELECT VALUE undefined)

[
  {
    "$1": false
  }
]

Als het trefwoord VALUE in de voorgaande subquery wordt weggelaten, resulteert de subquery in een matrix met één leeg object.

SELECT
    undefined

[
  {}
]

Op dat moment wordt de EXISTS expressie geëvalueerd true omdat het object ({}) technisch wordt afgesloten.

SELECT 
    EXISTS (SELECT undefined) 

[
  {
    "$1": true
  }
]

Een veelvoorkomend gebruiksvoorbeeld is het filteren van ARRAY_CONTAINS een item op basis van het bestaan van een item in een matrix. In dit geval controleren we of de matrix een item bevat met de tags naam 'outerwear'.

SELECT
    p.name,
    p.tags
FROM
    products p
WHERE
    ARRAY_CONTAINS(p.tags, "outerwear")

Dezelfde query kan worden gebruikt EXISTS als een alternatieve optie.

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

ARRAY_CONTAINS Daarnaast kan alleen worden gecontroleerd of een waarde gelijk is aan een element in een matrix. Als u complexere filters voor matrixeigenschappen nodig hebt, gebruikt JOIN u in plaats daarvan.

Bekijk dit voorbeelditem in een set met meerdere items die elk een accessories matrix bevatten.

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

Bekijk nu de volgende query die filtert op basis van de type en quantityOnHand eigenschappen in de matrix binnen elk item.

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

Voor elk van de items in de verzameling wordt een kruisproduct uitgevoerd met de bijbehorende matrixelementen. Met deze JOIN bewerking kunt u filteren op eigenschappen binnen de matrix. Het RU-verbruik van deze query is echter aanzienlijk. Als er bijvoorbeeld 1000 items in elke matrix 100 items bevatten, wordt deze uitgebreid tot 1,000 x 100 (dat wil gezegd 100.000) tuples.

Het gebruik EXISTS kan helpen om dit dure cross-product te voorkomen. In dit volgende voorbeeld filtert de query op matrixelementen in de EXISTS subquery. Als een matrixelement overeenkomt met het filter, projecteert u het en EXISTS resulteert u in waar.

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

Query's kunnen ook EXISTS aliasen en verwijzen naar de alias in de projectie:

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

MATRIX-expressie

U kunt de ARRAY expressie gebruiken om de resultaten van een query als matrix te projecteren. U kunt deze expressie alleen gebruiken binnen de SELECT component van de query.

Voor deze voorbeelden gaan we ervan uit dat er een container is met ten minste dit item.

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

In dit eerste voorbeeld wordt de expressie in de SELECT component gebruikt.

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

Net als bij andere subquery's zijn filters met de ARRAY expressie mogelijk.

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

Matrixexpressies kunnen ook na de FROM component in subquery's komen.

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

Gerelateerde inhoud

• JOIN clausule
• Constanten
• Zoekwoorden