Subconsultas no Azure Cosmos DB para NoSQL

APLICA-SE A: NoSQL

Uma subconsulta é uma consulta aninhada em outra consulta no Azure Cosmos DB para NoSQL. Uma subconsulta também é chamada de consulta interna ou interna SELECT. A instrução que contém uma subconsulta é normalmente chamada de consulta externa.

Tipos de subconsultas

Existem dois tipos principais de subconsultas:

• Correlacionado: uma subconsulta que faz referência a 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. Ele pode ser executado por conta própria 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 retornam. Existem três tipos:

• Tabela: Devolve várias linhas e várias colunas.
• Vários valores: Devolve várias linhas e uma única coluna.
• Escalar: retorna uma única linha e uma única coluna.

As consultas no Azure Cosmos DB para NoSQL sempre retornam uma única coluna (um valor simples ou um item complexo). Portanto, apenas subconsultas escalares e de vários valores são aplicáveis. Você pode usar uma subconsulta de vários valores somente na FROM cláusula como uma expressão relacional. Você pode usar 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 vários valores

As subconsultas de vários valores retornam um conjunto de itens e são sempre usadas dentro da FROM cláusula. Eles são usados para:

• Otimização JOIN (auto-junção) expressões.
• Avaliando expressões caras uma vez e referenciando várias vezes.

Otimize expressões de associação automática

As subconsultas de vários valores podem otimizar JOIN expressões empurrando predicados após cada expressão select-many em vez de depois de todas as WHERE junções cruzadas na cláusula.

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

Em WHERE seguida, a cláusula aplica o predicado do filtro em cada <c, t, n, s> tupla. Por exemplo, se um item correspondente tinha dez itens em cada uma das três matrizes, ele se expande para 1 x 10 x 10 x 10 (ou seja, 1.000) tuplas. O uso de subconsultas aqui pode ajudar na filtragem de itens de matriz associados antes de ingressar com a próxima expressão.

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

Suponha que apenas um item na matriz de tags corresponda ao filtro e há cinco itens para matrizes de quantidade e estoque. Em JOIN seguida, as expressões se expandem para 1 x 1 x 5 x 5 (25) itens, em vez de 1.000 itens na primeira consulta.

Avalie uma vez e refira muitas vezes

As subconsultas podem ajudar a otimizar consultas com expressões caras, como UDFs (funções definidas pelo usuário), cadeias de caracteres complexas ou expressões aritméticas. Você pode usar uma subconsulta junto com uma JOIN expressão para avaliar a expressão uma vez, mas fazer referência a ela muitas vezes.

Vamos supor que você tenha a seguinte UDF (getTotalWithTax) definida.

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

A consulta a seguir 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

Aqui está 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

Gorjeta

Tenha em mente o comportamento entre produtos das JOIN expressões. Se a expressão UDF puder ser avaliada como undefined, você deve garantir que a expressão sempre produza JOIN uma única linha retornando um objeto da subconsulta em vez do valor diretamente.

Mimetizar a junção com dados de referência externos

Muitas vezes, talvez seja necessário fazer referência a dados estáticos que raramente são alterados, como unidades de medida. É ideal não duplicar dados estáticos para cada item em uma consulta. Evitar essa duplicação economiza armazenamento e melhora o desempenho de gravação, mantendo o tamanho do item individual menor. Você pode usar uma subconsulta para imitar semântica de junçã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	Gigagrama	1.00E+09	Grama

A consulta a seguir imita a junção com esses dados para que você 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. Você pode usar uma expressão de subconsulta escalar em muitos lugares onde uma expressão escalar é válida. Por exemplo, você pode usar uma subconsulta escalar em qualquer expressão nas SELECT cláusulas e WHERE .

O uso de uma subconsulta escalar nem sempre ajuda a otimizar sua consulta. Por exemplo, passar uma subconsulta escalar como um argumento para um sistema ou funções definidas pelo usuário não oferece nenhum benefício na redução do consumo ou da latência da unidade de recurso (RU).

As subconsultas escalares podem ainda 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. Essas subconsultas não fornecem benefícios de otimização porque o compilador as converte em uma expressão simples maior. Não há contexto correlacionado entre as consultas internas e externas.

Como primeiro exemplo, considere esta consulta trivial.

SELECT
    1 AS a,
    2 AS b

Você pode reescrever essa consulta usando uma subconsulta escalar de expressão simples.

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

Ambas as consultas produzem a mesma saída.

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

Esta próxima consulta de exemplo 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 usa uma subconsulta escalar de expressão simples para retornar apenas os campos relevantes para cada item. A consulta gera algo para cada item, mas só inclui o campo projetado se ele atender ao filtro dentro da 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 agregada em 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
    }
  ]
}

Aqui está uma subconsulta com uma única expressão de função agregada em sua projeção. Essa consulta conta todas as tags de cada item.

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

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

Aqui está 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
  }
]

Aqui está outra subconsulta com várias expressões de função 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
    }
  }
]

Finalmente, aqui está 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 maneira mais ideal de escrever essa consulta é unir na subconsulta e fazer referência ao alias de subconsulta nas cláusulas SELECT e WHERE. Essa consulta é mais eficiente porque você precisa executar a subconsulta somente dentro da instrução join, 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

EXISTE expressão

O mecanismo de consulta do Azure Cosmos DB para NoSQL dá suporte a EXISTS expressões. Esta expressão é uma subconsulta escalar agregada incorporada no Azure Cosmos DB para NoSQL. EXISTS Usa uma expressão de subconsulta e retorna true se a subconsulta retornar quaisquer linhas. Caso contrário, ele retorna false.

Como o mecanismo de consulta não diferencia entre expressões booleanas e quaisquer outras expressões escalares, você pode usar EXISTS em ambas as SELECT cláusulas e WHERE . Esse comportamento é diferente do T-SQL, onde uma expressão booleana é restrita apenas a filtros.

Se a EXISTS subconsulta retornar um único valor que é undefined, EXISTS será avaliado como false. Por exemplo, considere a seguinte consulta que não retorna nada.

SELECT VALUE
    undefined

Se você usar a EXISTS expressão e a consulta anterior como uma subconsulta, a expressão retornará false.

SELECT
    EXISTS (SELECT VALUE undefined)

[
  {
    "$1": false
  }
]

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

SELECT
    undefined

[
  {}
]

Nesse ponto, a expressão avalia EXISTS desde true que o objeto ({}) tecnicamente sai.

SELECT 
    EXISTS (SELECT undefined) 

[
  {
    "$1": true
  }
]

Um caso de uso comum é ARRAY_CONTAINS filtrar um item pela existência de um item em uma matriz. Nesse caso, estamos verificando 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 usada 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 você precisar de filtros mais complexos nas propriedades da matriz, use JOIN em vez disso.

Considere este item de exemplo em um conjunto com vários itens, cada um contendo 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 type com base nas 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 da coleção, um produto cruzado é realizado com seus elementos de matriz. Esta JOIN operação torna possível filtrar as propriedades dentro da matriz. No entanto, o consumo de RU desta consulta é significativo. Por exemplo, se 1.000 itens tinham 100 itens em cada matriz, ele se expande para 1,000 x 100 (ou seja, 100.000) tuplas.

A utilização EXISTS pode ajudar a evitar este produto cruzado dispendioso. Neste próximo exemplo, a consulta filtra os elementos da matriz dentro da EXISTS subconsulta. Se um elemento de matriz corresponder ao filtro, você o projeta e EXISTS avalia 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"
]

As consultas também podem usar o alias EXISTS e fazer referência ao 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 ARRAY

Você pode usar a ARRAY expressão para projetar os resultados de uma consulta como uma matriz. Você pode usar essa expressão somente dentro da SELECT cláusula da consulta.

Para esses exemplos, vamos supor que haja um contêiner com pelo menos esse item.

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

Neste primeiro exemplo, a expressão é usada 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 vir 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údos relacionados

• JOIN cláusula
• Constantes
• Palavras-chave