Subconsultas no Azure Cosmos DB for NoSQL

APLICA-SE A: NoSQL

Uma subconsulta é uma consulta aninhada em outra consulta no Azure Cosmos DB for 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

Há dois principais tipos 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 que é independente da consulta externa. Ele pode ser executado sozinho sem depender da consulta externa.

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

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

As consultas no Azure Cosmos DB for NoSQL sempre retornam uma única coluna (um valor simples ou um item complexo). Portanto, somente subconsultas de valores múltiplos e escalares são aplicáveis. Você pode usar uma subconsulta de vários valores somente na cláusula FROM como uma expressão relacional. Você pode usar uma subconsulta escalar como uma expressão escalar na cláusula SELECT ou WHERE, ou como uma expressão relacional na cláusula FROM.

Subconsultas de vários valores

Subconsultas de vários valores retornam um conjunto de itens e são sempre usadas dentro da cláusula FROM. Elas são utilizadas para:

• Otimizando expressões JOIN (autojunção).
• Avaliar expressões caras uma vez e fazer referência várias vezes.

Otimizar expressões de autojunção

Subconsultas de vários valores podem otimizar expressões JOIN enviando predicados por push após cada expressão select-many em vez de todas as junções cross-joins na cláusula WHERE.

Considere a consulta a seguir:

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 essa consulta, o índice corresponde a qualquer item que tenha uma marca com um name de "winter" ou "fall", pelo menos um quantity entre zero e dez e pelo menos um warehouse em que o backstock é false. A expressão JOIN aqui executará o produto cruzado de todos os itens de matrizes de tags, onHandQuantities e warehouseStock para cada item correspondente antes de qualquer filtro ser aplicado.

A cláusula WHERE aplicará o predicado de filtro em cada tupla de <c, t, n, s>. Por exemplo, se o item correspondente tem dez itens em cada uma das três matrizes, ele expande para tuplas 1 x 10 x 10 x 10 (ou seja, 1.000. O uso de subconsultas aqui pode ajudar a filtrar itens de matriz unidos antes de se juntar à próxima expressão.

Essa 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 que haja cinco itens para as matrizes de quantidade e estoque. As expressões JOIN se expandem para itens 1 x 1 x 5 x 5 (25), em oposição a 1.000 itens na primeira consulta.

Avaliar uma vez e fazer referência várias 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 juntamente com uma expressão JOIN para avaliar a expressão uma vez, mas fazer referência a ela muitas vezes.

Vamos supor que você tenha o UDF (getTotalWithTax) a seguir definido.

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 somente uma vez:

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

Imitar junção com dados de referência externa

Geralmente, talvez seja necessário fazer referência a dados estáticos que raramente mudam, como unidades de medida. É ideal não duplicar dados estáticos para cada item em uma consulta. Evitar essa duplicação economiza armazenamento e melhorará 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ático.

Por exemplo, considere esse conjunto de medidas:

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 (cláusula SELECT) da subconsulta. Você pode usar uma expressão de subconsulta escalar em muitos locais em que uma expressão escalar é válida. Por exemplo, você pode usar uma subconsulta escalar em qualquer expressão nas cláusulas SELECT 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 fornece nenhum benefício em reduzir o consumo ou na latência de RU (unidade de recurso).

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 cláusula SELECT que não contém nenhuma expressão de agregação. Essas subconsultas não fornecem benefícios de otimização porque o compilador as converte em uma expressão simples maior. Não há nenhum contexto correlacionado entre as consultas internas e externas.

Como primeiro exemplo, considere essa 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
  }
]

O próximo exemplo de consulta 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

Esse 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": "aaaaaaaa-0000-1111-2222-bbbbbbbbbbbb",
    "name": "Winter glove"
  },
  {
    "id": "bbbbbbbb-1111-2222-3333-cccccccccccc"
  },
  {
    "id": "cccccccc-2222-3333-4444-dddddddddddd"
  }
]

Agregar subconsultas escalares

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

Como primeiro exemplo, considere um item com os campos a seguir.

{
  "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 de agregação em sua projeção. Essa consulta conta todas as marcas 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
  }
]

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

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 é ingressar 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 de junçã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 mecanismo de consulta do Azure Cosmos DB for NoSQL dá suporte a expressões EXISTS. Essa expressão é uma subconsulta escalar agregada incorporada ao Azure Cosmos DB for NoSQL. EXISTS usa uma expressão de subconsulta e retorna true se a subconsulta retornar qualquer linha. Caso contrário, ele retornará false.

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

A subconsulta EXISTS retorna um único valor que undefined, EXISTS avalia como false. Por exemplo, considere a consulta a seguir que não retorna nada.

SELECT VALUE
    undefined

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

SELECT
    EXISTS (SELECT VALUE undefined)

[
  {
    "$1": false
  }
]

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

SELECT
    undefined

[
  {}
]

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

SELECT 
    EXISTS (SELECT undefined) 

[
  {
    "$1": true
  }
]

Um caso de uso comum de ARRAY_CONTAINS é filtrar um documento pela existência de um item em uma matriz. Nesse caso, estamos verificando se a matriz de tags contém um item chamado "outerwear."

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

A mesma consulta pode usar 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 em Propriedades de matriz, use JOIN.

Considere esse item de exemplo em um conjunto com vários itens cada um contendo uma 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
    }
  ]
}

Agora considere a seguinte consulta que filtra com base nas propriedades type 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, um produto cruzado é executado com seus elementos de matriz. Essa operação JOIN torna possível filtrar as propriedades dentro da matriz. No entanto, o consumo de RU dessa consulta é significativo. Por exemplo, se os documentos 1.000 tivessem 100 itens em cada matriz, eles serão expandidos para tuplas 1,000 x 100 (ou seja, 100.000).

O uso de EXISTS pode ajudar a evitar este caro produto cruzado. Neste próximo exemplo, a consulta filtra os elementos de matriz dentro da subconsulta EXISTS. Se um elemento de matriz corresponder ao filtro, você o projetará 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 criar o 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 ARRAY

Você pode usar a expressão ARRAY para projetar os resultados de uma consulta como uma matriz. Você pode usar essa expressão somente dentro da cláusula SELECT 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"
    }
  ]
}

Nesse primeiro exemplo, a expressão é usada dentro da 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"
    ]
  }
]

Assim como ocorre com outras subconsultas, são possíveis filtros com a expressão 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"
    ]
  }
]

As expressões de matriz também podem vir após a cláusula FROM 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

• JOINcláusula
• Constantes
• Palavras-chave