Subconsultas en Azure Cosmos DB for NoSQL

SE APLICA A: NoSQL

Una subconsulta es una consulta anidada dentro de otra consulta dentro de Azure Cosmos DB for NoSQL. Una subconsulta también se denomina consulta interna o SELECT interna. La instrucción que contiene una subconsulta normalmente se llama consulta externa.

Tipos de subconsultas

Existen dos tipos principales de subconsultas:

• Correlacionado: una subconsulta que hace referencia a valores de la consulta externa. La subconsulta se evalúa una vez para cada fila que procesa la consulta externa.
• Sin correlacionar: una subconsulta que es independiente de la consulta externa. Se puede ejecutar por sí misma sin depender de la consulta externa.

Nota:

Azure Cosmos DB admite solo subconsultas correlacionadas.

Las subconsultas se pueden clasificar aún más en función del número de filas y columnas que devuelven. Existen tres tipos:

• Tabla: devuelve varias filas y varias columnas.
• Valores múltiples: devuelve varias filas y una sola columna.
• Escalar: devuelve una sola fila y una sola columna.

Las consultas en Azure Cosmos DB for NoSQL siempre devuelven una única columna (ya sea un valor simple o un elemento complejo). Por lo tanto, solo las subconsultas escalares y multivalor se aplican. Puede usar una subconsulta multivalor solo en la cláusula FROM como expresión relacional. Puede usar una subconsulta escalar como expresión escalar en la cláusula SELECT o WHERE, o como expresión relacional en la cláusula FROM.

Subconsultas multivalor

Las subconsultas multivalor devuelven un conjunto de elementos y siempre se usan dentro de la cláusula FROM. Se usan para:

• Optimización de expresiones JOIN (autocombinación).
• Evaluar expresiones costosas una vez y hacer referencia varias veces.

Optimización de expresiones de autocombinación

Las subconsultas multivalor pueden optimizar las expresiones JOIN mediante la inserción de predicados después de cada expresión select-many, en lugar de hacerlo después de todas las combinaciones cruzadas en la cláusula WHERE.

Considere la siguiente consulta:

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

Para esta consulta, el índice coincide con cualquier elemento que tenga una etiqueta con un name de "winter" (invierno) o "fall" (otoño), al menos un quantity entre zero (cero) y ten (diez), y al menos un almacén donde backstock es false. La expresión JOIN aquí realiza el producto cruzado de todos los elementos de las matrices tags, onHandQuantities y warehouseStock para cada elemento coincidente antes de aplicar cualquier filtro.

Luego, la cláusula WHERE aplica el predicado de filtro en cada tupla <c, t, n, s>. Por ejemplo, si un elemento coincidente tenía diez elementos en cada una de las tres matrices, se expande a 1 x 10 x 10 x 10 (es decir, 1 000) tuplas. Usar subconsultas aquí puede ayudar a filtrar los elementos de matrices combinadas antes de unirse a la siguiente expresión.

Esta consulta es equivalente a la anterior, pero usa subconsultas:

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)

Se supone que solo un elemento de la matriz tags coincide con el filtro, y hay cinco elementos tanto para la matriz de nutrients como la de servings. Después, las expresiones JOIN se amplían a 1 x 1 x 5 x 5 (25) elementos, frente a los 1 000 elementos de la primera consulta.

Evaluar una vez y hacer referencia varias veces

Las subconsultas pueden ayudar a optimizar las consultas con expresiones costosas, como funciones definidas por el usuario (UDF), cadenas complejas o expresiones aritméticas. Puede usar una subconsulta junto con una expresión JOIN para evaluar la expresión una vez, pero hacer referencia a ella varias veces.

Supongamos que tiene definida la siguiente función definida por el usuario (getTotalWithTax).

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

La siguiente consulta ejecuta la UDF getTotalWithTax varias veces:

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

Esta es una consulta equivalente que ejecuta la UDF solo una vez:

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

Sugerencia

Tenga en cuenta el comportamiento del producto cruzado de las expresiones JOIN. Si la expresión UDF se puede evaluar como undefined, debe asegurarse de que la expresión JOIN siempre produzca una sola fila mediante la devolución de un objeto de la subconsulta en lugar del valor directamente.

Imitar la combinación con datos de referencia externos

A menudo, es posible que deba hacer referencia a datos estáticos que no suelen cambiar, por ejemplo, unidades de medida. Es ideal no duplicar datos estáticos para cada elemento de una consulta. Al evitar esta duplicación, ahorra en almacenamiento y mejora el rendimiento de escritura, ya que mantiene un tamaño del elemento individual más pequeño. El uso de una subconsulta permite imitar la semántica de las uniones internas con una colección de datos de referencia estáticos.

Por ejemplo, considere este conjunto de medidas:

	Nombre	Multiplicador	Unidad base
ng	Nanogramo	1.00E-09	Gramo
µg	Microgramo	1.00E-06	Gramo
mg	Miligramo	1.00E-03	Gramo
g	Gramo	1.00E+00	Gramo
kg	Kilogramo	1.00E+03	Gramo
Mg	Megagramo	1.00E+06	Gramo
Gg	Gigagramo	1.00E+09	Gramo

La siguiente consulta imita la combinación con estos datos para que agregue el nombre de la unidad a la salida:

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

Subconsultas escalares

Una expresión de subconsulta escalar es una subconsulta que se evalúa como valor único. El valor de la expresión de subconsulta escalar es el valor de la proyección (cláusula SELECT) de la subconsulta. Puede usar una expresión de subconsulta escalar en muchos lugares donde sea válida una expresión escalar. Por ejemplo, puede usar una subconsulta escalar en cualquier expresión en las cláusulas SELECT y WHERE.

El uso de una subconsulta escalar no siempre ayuda a optimizar la consulta. Por ejemplo, pasar una subconsulta escalar como argumento a un sistema o a funciones definidas por el usuario no brinda ninguna ventaja en la reducción del consumo de recursos de unidad (RU) ni en la latencia.

Las subconsultas escalares se pueden clasificar, además, de esta manera:

• Subconsultas escalares de expresión simple
• Subconsultas escalares agregadas

Subconsultas escalares de expresión simple

Una subconsulta escalar de expresión simple es una subconsulta correlacionada que tienen una cláusula SELECT que no contiene ninguna expresión agregada. Estas subconsultas no brindan ninguna ventaja de optimización porque el compilador las convierte en una expresión simple mayor. No existe ningún contexto correlacionado entre las consultas internas y externas.

Como primer ejemplo, considere esta consulta trivial.

SELECT
    1 AS a,
    2 AS b

Puede reescribir esta consulta con una subconsulta escalar de expresión simple.

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

Ambas consultas generan la misma salida.

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

En este siguiente ejemplo, la consulta concatena el identificador único con un prefijo como subconsulta escalar de expresión simple.

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

El ejemplo usa una subconsulta escalar de expresión simple para devolver solo los campos relevantes de cada elemento. La consulta genera algo para cada elemento, pero solo incluye el campo proyectado si cumple el filtro dentro de la subconsulta.

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

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

Subconsultas escalares agregadas

Una subconsulta escalar agregada es una subconsulta que tiene una función agregada en su proyección o filtro que se evalúa como valor único.

Como primer ejemplo, considere un elemento con los campos siguientes.

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

Esta es una subconsulta con una única expresión de función agregada en su proyección. Esta consulta cuenta todas las etiquetas de cada elemento.

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

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

Esta es la misma subconsulta con un filtro.

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

Esta es otra subconsulta con varias expresiones de función agregada:

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

Por último, esta es una consulta con una subconsulta agregada tanto en la proyección como en el filtro:

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

Una manera más eficaz de escribir esta consulta es combinar en la subconsulta y hacer referencia al alias de la subconsulta tanto en la cláusula SELECT como en la WHERE. Esta consulta es más eficaz porque tiene que ejecutar la subconsulta solo dentro de la instrucción de combinación, y no en la proyección y en el filtro.

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

Expresión EXISTS

El motor de consultas de Azure Cosmos DB for NoSQL admite expresiones EXISTS. Esta expresión es una subconsulta escalar agregada compilada en Azure Cosmos DB for NoSQL. EXISTS toma una expresión de subconsulta y devuelve true si la subconsulta devuelve alguna fila. De lo contrario, devuelve false.

Dado que el motor de consulta no distingue entre expresiones booleanas y cualquier otra expresión escalar, puede usar EXISTS tanto en cláusulas SELECT como WHERE. Este comportamiento es diferente de T-SQL, donde una expresión booleana está restringida solo a filtros.

Si la subconsulta EXISTS devuelve un único valor que es undefined, EXISTS se evalúa como falso. Por ejemplo, considere la siguiente consulta que no devuelve nada.

SELECT VALUE
    undefined

Si usa la expresión EXISTS y la consulta anterior como subconsulta, la expresión devuelve false.

SELECT
    EXISTS (SELECT VALUE undefined)

[
  {
    "$1": false
  }
]

Si se omite la palabra clave VALUE en la subconsulta anterior, la subconsulta se evalúa a una matriz con un único objeto vacío.

SELECT
    undefined

[
  {}
]

En ese momento, la expresión EXISTS se evalúa a true ya que el objeto ({}) técnicamente se cierra.

SELECT 
    EXISTS (SELECT undefined) 

[
  {
    "$1": true
  }
]

Un caso de uso común de ARRAY_CONTAINS es filtrar un elemento por la existencia de un elemento en una matriz. En este caso, estamos consultando si la matriz tags contiene un elemento llamado "outerwear" (ropa exterior).

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

La misma consulta puede usar EXISTS como opción alternativa.

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

Además, ARRAY_CONTAINS solo puede comprobar si un valor es igual a cualquier elemento dentro de una matriz. Si necesita filtros más complejos en las propiedades de la matriz, use JOIN en su lugar.

Considere este elemento de ejemplo en un conjunto con varios elementos, cada uno de los cuales contiene una matriz 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
    }
  ]
}

Ahora, considere la siguiente consulta que filtra basándose en las propiedades type y quantityOnHand de la matriz dentro de cada elemento.

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

Para cada uno de los elementos de la colección, se realiza un producto cruzado con los elementos de la matriz. Esta operación JOIN permite filtrar según las propiedades dentro de la matriz. Sin embargo, el consumo de RU de esta consulta es significativo. Por ejemplo, si 1 000 elementos tuvieran 100 elementos en cada matriz, se expande a 1,000 x 100 (es decir, 100 000) tuplas.

Usar EXISTS puede ayudar a evitar este costoso producto cruzado. En el siguiente ejemplo, la consulta filtra los elementos de la matriz dentro de la subconsulta EXISTS. Si un elemento de la matriz coincide con el filtro, lo proyecta y EXISTS se evalúa como 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"
]

Las consultas también pueden establecer alias EXISTS y hacer referencia al alias en la proyección:

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

Expresión ARRAY

Puede usar la expresión ARRAY para proyectar los resultados de una consulta como matriz. Puede usar esta expresión solo dentro de la cláusula SELECT de la consulta.

En estos ejemplos, supongamos que hay un contenedor con al menos este elemento.

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

En este primer ejemplo, la expresión se usa dentro de la cláusula SELECT.

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

Al igual que con las demás subconsultas, son posibles los filtros con la expresión 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"
    ]
  }
]

Las expresiones de la matriz también pueden ir después de la cláusula FROM en las subconsultas.

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

Contenido relacionado

• JOIN clause
• Constantes
• Palabras clave