Sous-requêtes dans Azure Cosmos DB for NoSQL

S’APPLIQUE À : NoSQL

Une sous-requête est une requête imbriquée dans une autre requête dans Azure Cosmos DB for NoSQL. Une sous-requête est également appelée  requête interne  ou  sélection interne SELECT. L’instruction qui contient une sous-requête est généralement appelée  requête externe .

Types des sous-requêtes

Il existe deux principaux types de sous-requêtes :

• Corrélé : sous-requête qui référence des valeurs de la requête externe. La sous-requête est évaluée une fois pour chaque ligne que traite la requête externe.
• Non corrélé : sous-requête indépendante de la requête externe. Elle peut être exécutée indépendamment de la requête externe.

Notes

Azure Cosmos DB prend en charge uniquement les sous-requêtes corrélées.

Les sous-requêtes peuvent également être classées en fonction du nombre de lignes et colonnes qu’elles retournent. Il existe trois types :

• Table : retourne plusieurs lignes et plusieurs colonnes.
• Multivaleur : retourne plusieurs lignes et une seule colonne.
• Scalaire : retourne une seule ligne et une seule colonne.

Les requêtes SQL dans Azure Cosmos DB for NoSQL retournent toujours une seule colonne (une valeur simple ou un élément complexe). Ainsi, seules les sous-requêtes à valeurs multiples et les sous-requêtes scalaires sont applicables. Vous pouvez utiliser une sous-requête à valeurs multiples uniquement dans la clause FROM sous forme d’expression relationnelle. Vous pouvez utiliser une sous-requête scalaire comme expression scalaire dans la clause SELECT ou WHERE, ou encore comme expression relationnelle dans la clause FROM.

Sous-requêtes multivaleurs

Les sous-requêtes à valeurs multiples retournent un ensemble d’éléments et sont toujours utilisées dans la clause FROM. Elles sont utilisées pour :

• Optimisation des expressions (jointure automatique) JOIN.
• Évaluer les expressions coûteuses une seule fois et les référencer plusieurs fois.

Optimiser les expressions de jointure automatique

Les sous-requêtes à valeurs multiples peuvent optimiser des expressions JOIN en envoyant les prédicats après chaque expression de sélection multiple, plutôt qu’après toutes les jointures croisées dans la clause WHERE.

Considérez la requête suivante :

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

Pour cette requête, l’index correspond à tout élément qui a une balise name avec un « hiver » ou « automne », au moins un quantity entre zéro et dix, et au moins un entrepôt où est le backstock est false. L’expression JOIN effectue le produit croisé de tous les éléments des tableaux tags, onHandQuantities et warehouseStock pour chaque élément correspondant avant qu’un filtre ne soit appliqué.

La clause WHERE applique alors le prédicat de filtre sur chaque tuple <c, t, n, s>. Par exemple, si un élément correspondant a dix éléments dans chacun des trois tableaux, il s’étend jusqu’à 1 x 10 x 10 x 10(c’est à dire, 1,000) tuples. L’utilisation de sous-requêtes ici peut aider à filtrer des éléments de tableaux joints avant d’effectuer une jointure avec l’expression suivante.

Cette requête est équivalente à la précédente, mais utilise des sous-requêtes :

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)

Supposons qu’un seul élément du tableau d’étiquettes correspond au filtre et qu’il existe cinq éléments pour les tableaux de quantité et de stock. Les expressions JOIN s’étendent à 1 x 1 x 5 x 5 (25) éléments, au lieu de 1,000 éléments dans la première requête.

Évaluer une seule fois et référencer plusieurs fois

Les sous-requêtes peuvent aider à optimiser les requêtes ayant des expressions coûteuses telles que des fonctions définies par l’utilisateur, des chaînes complexes ou des expressions arithmétiques. Vous pouvez utiliser une sous-requête avec une expression JOIN pour évaluer l’expression une seule fois, mais la référencer plusieurs fois.

Supposons que vous ayez determiné les fonctions définies par l’utilisateur suivantes (getTotalWithTax).

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

La requête suivante exécute la fonction définie par l’utilisateur getTotalWithTax plusieurs fois :

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

Voici une requête équivalente qui exécute la fonction définie par l’utilisateur une seule fois :

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

Conseil

Gardez à l’esprit que les expressions JOIN effectuent des produits croisés. Si l’expression des fonctions définies par l’utilisateur peut avoir une valeur undefined, vous devez vous assurer que l’expression JOIN produit toujours une seule ligne en retournant un objet à partir de la sous-requête plutôt que la valeur directement.

Imiter la jointure avec des données de référence externes

Souvent, vous pouvez être amené à référencer des données statiques qui changent rarement, telles que les unités de mesure. Il est idéal de ne pas dupliquer de données statiques pour chaque élément d’une requête. Le fait d’éviter cette duplication permet d’économiser l’espace de stockage et d’améliorer les performances des écritures en réduisant la taille des éléments individuels. Vous pouvez utiliser une sous-requête pour imiter la sémantique d’une jointure interne avec une collection de données de référence statiques.

Pour instance, considérez cet ensemble de mesures :

	Nom	Multiplicateur	Unité de base
ng	Nanogram	1.00E-09	Gramme
µg	Microgram	1.00E-06	Gramme
mg	Milligram	1.00E-03	Gramme
g	Gramme	1.00E+00	Gramme
kg	Kilogram	1.00E+03	Gramme
Mg	Megagram	1.00E+06	Gramme
Gg	Gigagram	1.00E+09	Gramme

La requête suivante imite une jointure avec ces données afin que le nom de l’unité soit ajouté à la sortie :

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

Sous-requêtes scalaires

Une expression de sous-requête scalaire est une sous-requête qui aboutit à une valeur unique. La valeur de l’expression de sous-requête scalaire est la valeur de la projection (clause SELECT) de la sous-requête. Vous pouvez utiliser une expression de sous-requête scalaire dans de nombreux endroits où une expression scalaire est valide. Par exemple, vous pouvez utiliser une sous-requête scalaire dans toute expression au sein des clauses SELECT et WHERE.

L’utilisation d’une sous-requête scalaire n’optimise pas systématiquement votre requête. Par exemple, le fait de passer une sous-requête scalaire en tant qu’argument à des fonctions définies par l’utilisateur ou le système n’offre aucun avantage en termes de réduction de consommation d’unités de ressource ou de latence.

Les sous-requêtes scalaires peuvent être classées comme suit :

• Sous-requêtes scalaires à expression simple
• Sous-requêtes scalaires d’agrégation

Sous-requêtes scalaires à expression simple

Une sous-requête scalaire à expression simple est une sous-requête corrélée qui a une clause SELECT dépourvue d’expressions d’agrégation. Ces sous-requêtes n’apportent aucun avantage en termes d’optimisation, car le compilateur les convertit en une expression simple plus grande. Il n’existe aucun contexte corrélé entre les requêtes internes et externes.

Comme premier exemple, prenez en compte cette requête triviale.

SELECT
    1 AS a,
    2 AS b

Vous pouvez réécrire cette requête, à l’aide d’une sous-requête scalaire à expression simple.

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

Les deux requêtes produisent la même sortie.

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

Cet exemple de requête suivant concatène l’identificateur unique avec un préfixe en tant que sous-requête scalaire d’expression simple.

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

Cet exemple utilise une sous-requête scalaire simple-expression pour renvoyer uniquement les champs pertinents pour chaque élément. La requête génère quelque chose pour chaque élément, mais elle inclut uniquement le champ projeté s’il répond au filtre dans la sous-requête.

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

[
  {
    "id": "aaaaaaaa-0000-1111-2222-bbbbbbbbbbbb",
    "name": "Winter glove"
  },
  {
    "id": "bbbbbbbb-1111-2222-3333-cccccccccccc"
  },
  {
    "id": "cccccccc-2222-3333-4444-dddddddddddd"
  }
]

Sous-requêtes scalaires d’agrégation

Une sous-requête scalaire d’agrégation est une sous-requête qui a une fonction d’agrégation dans sa projection ou filtre qui aboutit à une valeur unique.

Dans un premier exemple, considérez un élément avec les champs suivants.

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

Voici une sous-requête dont la projection contient une expression de fonction d’agrégation unique. Cette requête compte toutes les balises pour chaque élément.

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

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

Voici la même sous-requête avec un filtre.

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

Voici une autre sous-requête avec plusieurs expressions de fonction d’agrégation :

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

Enfin, voici une requête avec une sous-requête d’agrégation dans la projection et le filtre :

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

Une solution plus optimale pour écrire cette requête consiste à effectuer une jointure sur la sous-requête et à référencer l’alias de celle-ci dans les clauses SELECT et WHERE. Cette requête est plus efficace, car vous ne devez exécuter la sous-requête qu’au sein de l’instruction de jointure et non dans la projection et le filtre.

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

Expression EXISTS

Le moteur de requête d’Azure Cosmos DB for NoSQL prend en charge les expressions EXISTS. Cette expression s’agit d’une sous-requête scalaire d’agrégation intégrée à Azure Cosmos DB for NoSQL. EXISTS prend une expression de sous-requête et retourne true si la sous-requête retourne des lignes. Sinon, falseest retourné.

Étant donné que le moteur de requête considère indifféremment les expressions booléennes et toutes les autres expressions scalaires, vous pouvez utiliser EXISTS dans les clauses SELECT et WHERE. Ce comportement est différent de T-SQL, où une expression booléenne est limitée aux filtres uniquement.

Si la sous-requête EXISTS retourne une valeur unique égale à undefined, EXISTS a la valeur false. Par exemple, considérez la requête suivante qui ne retourne rien.

SELECT VALUE
    undefined

Si vous utilisez l’expression EXISTS et la requête précédente en tant que sous-requête, l’expression retourne false.

SELECT
    EXISTS (SELECT VALUE undefined)

[
  {
    "$1": false
  }
]

Si le mot clé VALUE dans la sous-requête précédente est omis, la sous-requête correspond à un tableau avec un seul objet vide.

SELECT
    undefined

[
  {}
]

À ce stade, l’expression EXISTS est évaluée à true puisque l’objet ({}) se termine techniquement.

SELECT 
    EXISTS (SELECT undefined) 

[
  {
    "$1": true
  }
]

Un cas d’usage courant de ARRAY_CONTAINS consiste à filtrer un élément en fonction de l’existence d’un élément dans un tableau. Dans ce cas, nous vérifions si le tableau tags contient un élément nommé « orange ».

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

La même requête peut être utilisée EXISTS comme autre option.

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

De plus, ARRAY_CONTAINS peut uniquement vérifier si une valeur est égale à un élément dans un tableau. Si vous avez besoin de filtres plus complexes sur des propriétés de tableau, utilisez plutôt JOIN.

Considérez cet exemple d’élément dans un ensemble avec plusieurs éléments contenant chacun un tableau accessories.

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

Ensuite, considérez la requête suivante qui filtre en fonction des propriétés type et quantityOnHand dans le tableau de chaque élément.

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

Pour chacun des éléments dans la collection, un produit croisé est effectué avec ses éléments de tableau. Cette opération JOIN permet d’effectuer un filtrage sur les propriétés dans le tableau. Toutefois, la consommation de RU de cette requête est importante. Par exemple, si 1 000 éléments contiennent 100éléments dans chaque tableau, l’opération aboutit à 1,000 x 100 (autrement dit, 100 000) tuples.

L’utilisation de EXISTS peut aider à éviter ce produit croisé couteux. Dans cet exemple suivant, la requête filtre les éléments de tableau au sein de la EXISTS sous-requête. Si un élément de tableau correspond au filtre, vous le projetez et EXISTS a la valeur true.

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

Les requêtes peuvent également alias EXISTS et référencer l’alias dans la projection :

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

Expression ARRAY

Vous pouvez utiliser l’expression ARRAY pour projeter les résultats d’une requête sous forme de tableau. Vous ne pouvez utiliser cette expression que dans la clause SELECT de la requête.

Pour ces exemples, supposons qu’il existe un conteneur avec au moins cet élément.

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

Dans ce premier exemple, l’expression est utilisée dans la SELECT clause .

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

Comme dans le cas des autres sous-requêtes, vous pouvez utiliser des filtres avec l’expression ARRAY.

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

Les expressions de tableau peuvent également apparaître après la clause FROM dans les sous-requêtes.

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

Contenu connexe

• Clause JOIN
• Constantes
• Mots clés