Subconsultas no Azure Cosmos DB para NoSQL

APLICA-SE A: NoSQL

Uma subconsulta é uma consulta aninhada noutra consulta no Azure Cosmos DB para NoSQL. Uma subconsulta também é denominada consulta interna ou interna SELECT. Normalmente, a instrução que contém uma subconsulta é denominada consulta externa.

Tipos de subconsultas

Existem dois tipos principais de subconsultas:

• Correlacionado: uma subconsulta que referencia valores da consulta externa. A subconsulta é avaliada uma vez para cada linha que a consulta externa processa.
• Não correlacionado: uma subconsulta independente da consulta externa. Pode ser executado por si só sem depender da consulta externa.

Nota

O Azure Cosmos DB suporta apenas subconsultas correlacionadas.

As subconsultas podem ser classificadas com base no número de linhas e colunas que devolvem. Existem três tipos:

• Tabela: devolve várias linhas e múltiplas colunas.
• Valor múltiplo: devolve várias linhas e uma única coluna.
• Escalar: devolve uma única linha e uma única coluna.

As consultas no Azure Cosmos DB para NoSQL devolvem sempre uma única coluna (um valor simples ou um item complexo). Por conseguinte, apenas são aplicáveis subconsultas de múltiplos valores e escalares. Pode utilizar uma subconsulta de múltiplos valores apenas na FROM cláusula como uma expressão relacional. Pode utilizar uma subconsulta escalar como uma expressão escalar na SELECT cláusula ou WHERE ou como uma expressão relacional na FROM cláusula.

Subconsultas de valores múltiplos

As subconsultas de valores múltiplos devolvem um conjunto de itens e são sempre utilizadas dentro da FROM cláusula. São utilizados para:

• Otimizar expressões (associação JOIN autónoma).
• Avaliar expressões dispendiosas uma vez e referenciar várias vezes.

Otimizar expressões de associação autónoma

As subconsultas de valores múltiplos podem otimizar JOIN expressões ao emitir predicados após cada expressão select-many em vez de depois de todas as associações cruzadas na WHERE cláusula.

Considere a consulta seguinte:

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, o índice corresponde a qualquer item que tenha uma etiqueta com um name de "inverno" ou "queda", pelo menos um quantity entre zero e dez e, pelo menos, um armazém em que o backstock é false. A JOIN expressão aqui executa o produto cruzado de todos os itens de tags, onHandQuantitiese warehouseStock matrizes para cada item correspondente antes de qualquer filtro ser aplicado.

Em WHERE seguida, a cláusula aplica o predicado de filtro em cada <c, t, n, s> cadeia de identificação. Por exemplo, se um item correspondente tiver dez itens em cada uma das três matrizes, este expande para 1 x 10 x 10 x 10 (ou seja, 1000) cadeias de identificação. A utilização de subconsultas aqui pode ajudar a filtrar itens de matriz associados antes de se associar à expressão seguinte.

Esta consulta é equivalente à anterior, mas utiliza 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)

Suponha que apenas um item na matriz de etiquetas corresponde ao filtro e que existem cinco itens para matrizes de nutrientes e porções. Em JOIN seguida, as expressões expandem para 1 x 1 x 5 x 5(25) itens, em oposição a 1000 itens na primeira consulta.

Avaliar uma vez e referenciar muitas vezes

As subconsultas podem ajudar a otimizar as consultas com expressões dispendiosas, como funções definidas pelo utilizador (UDFs), cadeias complexas ou expressões aritméticas. Pode utilizar uma subconsulta juntamente com uma JOIN expressão para avaliar a expressão uma vez, mas referencia-a muitas vezes.

Vamos supor que tem o seguinte UDF (getTotalWithTax) definido.

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

A seguinte consulta executa o UDF getTotalWithTax várias vezes:

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

Eis uma consulta equivalente que executa o UDF apenas uma vez:

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

Dica

Tenha em atenção o comportamento entre produtos das JOIN expressões. Se a expressão UDF puder ser avaliada como undefined, deve garantir que a JOIN expressão produz sempre uma única linha ao devolver um objeto da subconsulta em vez do valor diretamente.

Imitar a associação com dados de referência externa

Muitas vezes, poderá ter de referenciar dados estáticos que raramente mudam, como unidades de medição. É ideal não duplicar dados estáticos para cada item numa consulta. Evitar esta duplicação poupa no armazenamento e melhora o desempenho de escrita ao manter o tamanho do item individual mais pequeno. Pode utilizar uma subconsulta para imitar a semântica de associação interna com uma coleção de dados de referência estáticos.

Por exemplo, considere este conjunto de medidas:

	Nome	Multiplicador	Unidade base
ng	Nanograma	1.00E-09	Grama
µg	Micrograma	1.00E-06	Grama
mg	Miligrama	1.00E-03	Grama
g	Grama	1.00E+00	Grama
kg	Quilograma	1.00E+03	Grama
Mg	Megagrama	1.00E+06	Grama
Gg	Gigagram	1.00E+09	Grama

A seguinte consulta imita a associação a estes dados para que adicione o nome da unidade à saída:

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

Uma expressão de subconsulta escalar é uma subconsulta que é avaliada como um único valor. O valor da expressão de subconsulta escalar é o valor da projeção (SELECT cláusula) da subconsulta. Pode utilizar uma expressão de subconsulta escalar em vários locais onde uma expressão escalar é válida. Por exemplo, pode utilizar uma subconsulta escalar em qualquer expressão nas SELECT cláusulas e WHERE .

A utilização de uma subconsulta escalar nem sempre ajuda a otimizar a consulta. Por exemplo, transmitir uma subconsulta escalar como um argumento para um sistema ou funções definidas pelo utilizador não oferece qualquer benefício na redução do consumo ou latência de unidades de recursos (RU).

As subconsultas escalares podem ser classificadas como:

• Subconsultas escalares de expressão simples
• Agregar subconsultas escalares

Subconsultas escalares de expressão simples

Uma subconsulta escalar de expressão simples é uma subconsulta correlacionada que tem uma SELECT cláusula que não contém expressões agregadas. Estas subconsultas não proporcionam benefícios de otimização porque o compilador as converte numa expressão simples maior. Não existe nenhum contexto correlacionado entre as consultas internas e externas.

Como primeiro exemplo, considere esta consulta trivial.

SELECT
    1 AS a,
    2 AS b

Pode reescrever esta consulta com uma subconsulta escalar de expressão simples.

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

Ambas as consultas produzem o mesmo resultado.

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

Esta consulta de exemplo seguinte concatena o identificador exclusivo com um prefixo como uma subconsulta escalar de expressão simples.

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

Este exemplo utiliza uma subconsulta escalar de expressão simples para devolver apenas os campos relevantes para cada item. A consulta produz algo para cada item, mas só inclui o campo projetado se cumprir o filtro na 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"
  }
]

Agregar subconsultas escalares

Uma subconsulta escalar agregada é uma subconsulta que tem uma função de agregação na sua projeção ou filtro que é avaliada como um único valor.

Como primeiro exemplo, considere um item com os seguintes campos.

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

Segue-se uma subconsulta com uma única expressão de função de agregação na projeção. Esta consulta conta todas as etiquetas para cada item.

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

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

Eis a mesma subconsulta com um 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
  }
]

Eis outra subconsulta com múltiplas expressões de função de agregação:

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 fim, eis uma consulta com uma subconsulta agregada na projeção e no 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
  }
]

Uma forma mais ideal de escrever esta consulta é associar-se à subconsulta e referenciar o alias de subconsulta nas cláusulas SELECT e WHERE. Esta consulta é mais eficiente porque só precisa de executar a subconsulta na instrução de associação e não na projeção e no 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

Expressão EXISTS

O motor de consulta do Azure Cosmos DB para NoSQL suporta EXISTS expressões. Esta expressão é uma subconsulta escalar agregada incorporada no Azure Cosmos DB para NoSQL. EXISTS utiliza uma expressão de subconsulta e devolve true se a subconsulta devolver linhas. Caso contrário, devolve false.

Uma vez que o motor de consulta não diferencia entre expressões booleanas e quaisquer outras expressões escalares, pode utilizar EXISTS em ambas as SELECT cláusulas e WHERE . Este comportamento é diferente do T-SQL, em que uma expressão booleana está restrita a apenas filtros.

Se a EXISTS subconsulta devolver um único valor, undefinedé avaliado EXISTS como falso. Por exemplo, considere a seguinte consulta que não devolve nada.

SELECT VALUE
    undefined

Se utilizar a EXISTS expressão e a consulta anterior como uma subconsulta, a expressão devolve false.

SELECT
    EXISTS (SELECT VALUE undefined)

[
  {
    "$1": false
  }
]

Se a palavra-chave VALOR na subconsulta anterior for omitida, a subconsulta é avaliada como uma matriz com um único objeto vazio.

SELECT
    undefined

[
  {}
]

Nessa altura, a EXISTS expressão é avaliada como uma true vez que o objeto ({}) sai tecnicamente.

SELECT 
    EXISTS (SELECT undefined) 

[
  {
    "$1": true
  }
]

Um caso de utilização comum de ARRAY_CONTAINS é filtrar um item pela existência de um item numa matriz. Neste caso, estamos a verificar se a tags matriz contém um item chamado "outerwear".

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

A mesma consulta pode ser utilizada EXISTS como uma opção alternativa.

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

Além disso, ARRAY_CONTAINS só pode verificar se um valor é igual a qualquer elemento dentro de uma matriz. Se precisar de filtros mais complexos nas propriedades da matriz, utilize JOIN em alternativa.

Considere este item de exemplo num conjunto com vários itens que contêm uma accessories matriz.

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

Agora, considere a seguinte consulta que filtra com base nas type propriedades e quantityOnHand na matriz dentro de cada 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"
  }
]

Para cada um dos itens na coleção, é efetuado um produto cruzado com os respetivos elementos de matriz. Esta JOIN operação permite filtrar as propriedades na matriz. No entanto, o consumo de RU desta consulta é significativo. Por exemplo, se 1000 itens tinham 100 itens em cada matriz, expande-se para 1,000 x 100 (ou seja, 100 000) cadeias de identificação.

A utilização EXISTS pode ajudar a evitar este produto cruzado dispendioso. Neste exemplo seguinte, a consulta filtra os elementos de matriz na subconsulta EXISTS . Se um elemento de matriz corresponder ao filtro, projetará-o e EXISTS avaliará como verdadeiro.

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

As consultas também podem alias EXISTS e referenciar o alias na projeção:

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

Expressão MATRIZ

Pode utilizar a ARRAY expressão para projetar os resultados de uma consulta como uma matriz. Só pode utilizar esta expressão na SELECT cláusula da consulta.

Para estes exemplos, vamos supor que existe um contentor com, pelo menos, este item.

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

Neste primeiro exemplo, a expressão é utilizada dentro da SELECT cláusula.

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

Tal como acontece com outras subconsultas, os filtros com a ARRAY expressão são possíveis.

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

As expressões de matriz também podem ser apresentadas após a FROM cláusula em 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"
  }
]

Conteúdo relacionado

• JOIN cláusula
• Constantes
• Palavras-chave