Примеры вложенных запросов SQL для Azure Cosmos DB

ОБЛАСТЬ ПРИМЕНЕНИЯ: API SQL

Вложенный запрос — это запрос, находящийся внутри другого запроса. Вложенный запрос также называется внутренним запросом или внутренней выборкой. Инструкция, содержащая вложенный запрос, обычно называется внешним запросом.

В этой статье описываются вложенные запросы SQL и их распространенные варианты использования в Azure Cosmos DB. В статье показаны примеры запросов к набору данных о питании.

Типы вложенных запросов

Существует два основных типа вложенных запросов:

  • Коррелированный. Вложенный запрос, который ссылается на значения из внешнего запроса. Вложенный запрос вычисляется один раз для каждой строки, обрабатываемой внешним запросом.
  • Некоррелированный. Вложенный запрос, который не зависит от внешнего запроса. Его можно выполнять самостоятельно, не полагаясь на внешний запрос.

Примечание

Azure Cosmos DB поддерживает только коррелированные вложенные запросы.

Вложенные запросы можно дополнительно классифицировать на основе числа возвращаемых записей и столбцов. Здесь возможны три варианта:

  • Таблица. Возвращает несколько записей и несколько столбцов.
  • Несколько значений. Возвращает несколько записей и один столбец.
  • Скаляр. Возвращает одну запись и один столбец.

Запросы SQL в Azure Cosmos DB всегда возвращают один столбец (простое значение или сложный документ). Таким образом, в Azure Cosmos DB применимы только содержащие несколько значений и скалярные вложенные запросы. Содержащий несколько значений вложенный запрос можно использовать только в предложении FROM в качестве реляционного выражения. Скалярный вложенный запрос можно использовать в качестве скалярного выражения в предложении SELECT или WHERE или в качестве реляционного выражения в предложении FROM.

Вложенные запросы с несколькими значениями

Вложенные запросы с несколькими значениями возвращают набор документов и всегда используются в предложении FROM. Они используются, когда требуется:

  • Оптимизация выражений JOIN.
  • Однократная оценка дорогостоящих выражений и многократная ссылка на них.

Оптимизация выражений JOIN.

Вложенные запросы с несколькими значениями могут оптимизировать выражения JOIN путем принудительной отправки предикатов после каждого выражения select-many, а не после всех cross-joins в предложении WHERE.

Обратите внимание на следующий запрос:

SELECT Count(1) AS Count
FROM c
JOIN t IN c.tags
JOIN n IN c.nutrients
JOIN s IN c.servings
WHERE t.name = 'infant formula' AND (n.nutritionValue > 0 
AND n.nutritionValue < 10) AND s.amount > 1

Для этого запроса индекс будет соответствовать любому документу с тегом "infant formula". Это элемент питательных веществ со значением от 0 до 10 и элемент порций со значением больше 1. Выражение JOIN здесь выполняет перекрестное перемножение всех элементов массивов тегов, питательных веществ и порций для каждого соответствующего документа перед применением какого-либо фильтра.

Затем предложение WHERE применит предикат фильтра к каждому кортежу <c, t, n, s>. Например, если в соответствующем документе содержится 10 элементов в каждом из трех массивов, он будет развернут до 1 × 10 × 10 × 10 (то есть 1000) кортежей. Использование вложенных запросов здесь может помочь отфильтровать присоединенные элементы массива перед присоединением к следующему выражению.

Этот запрос эквивалентен предыдущему, но использует вложенные запросы:

SELECT Count(1) AS Count
FROM c
JOIN (SELECT VALUE t FROM t IN c.tags WHERE t.name = 'infant formula')
JOIN (SELECT VALUE n FROM n IN c.nutrients WHERE n.nutritionValue > 0 AND n.nutritionValue < 10)
JOIN (SELECT VALUE s FROM s IN c.servings WHERE s.amount > 1)

Предположим, что только один элемент в массиве тегов соответствует фильтру и в нем есть пять элементов для массивов питательных веществ и порций. Выражения JOIN здесь разворачиваются до 1 × 1 × 5 × 5 = 25 элементов, а не до 1000 элементов, как в первом запросе.

Однократное вычисление и многократная ссылка

Вложенные запросы могут помочь оптимизировать запросы с дорогостоящими выражениями, такими как определяемые пользователем функции (UDF), сложные строки или арифметические выражения. Можно использовать вложенный запрос вместе с выражением JOIN для однократного вычисления выражения, но многократной ссылки на него.

Следующий запрос дважды запускает определяемую пользователем функцию GetMaxNutritionValue:

SELECT c.id, udf.GetMaxNutritionValue(c.nutrients) AS MaxNutritionValue
FROM c
WHERE udf.GetMaxNutritionValue(c.nutrients) > 100

Ниже приведен эквивалентный запрос, запускающий функцию UDF только один раз:

SELECT TOP 1000 c.id, MaxNutritionValue
FROM c
JOIN (SELECT VALUE udf.GetMaxNutritionValue(c.nutrients)) MaxNutritionValue
WHERE MaxNutritionValue > 100

Примечание

Учитывайте поведение перекрестного произведения выражений JOIN. Если выражение UDF может принимать значение undefined, следует убедиться, что выражение JOIN всегда создает одну запись, возвращая объект из вложенного запроса, а не значение напрямую.

Вот похожий пример, возвращающий объект, а не значение:

SELECT TOP 1000 c.id, m.MaxNutritionValue
FROM c
JOIN (SELECT udf.GetMaxNutritionValue(c.nutrients) AS MaxNutritionValue) m
WHERE m.MaxNutritionValue > 100

Этот подход не ограничивается функциями UDF. Он применяется к любому потенциально дорогостоящему выражению. Например, можно использовать тот же подход к математической функции avg:

SELECT TOP 1000 c.id, AvgNutritionValue
FROM c
JOIN (SELECT VALUE avg(n.nutritionValue) FROM n IN c.nutrients) AvgNutritionValue
WHERE AvgNutritionValue > 80

Имитация соединения с внешними эталонными данными

Часто бывает необходимо ссылаться на статические данные, которые редко изменяются, например единицы измерения или коды стран. Лучше не дублировать такие данные для каждого документа. Устранение этого дублирования позволит сэкономить место для хранения и повысить производительность операций записи, уменьшая размер документа. Вложенный запрос можно использовать для имитации семантики внутреннего объединения с помощью коллекции эталонных данных.

Например, рассмотрим следующий набор эталонных данных:

Единица измерения имя; Коэффициент Базовая единица измерения
нг Нанограмм 1,00 E-09 Грамм
мкг Микрограмм 1,00 E-06 Грамм
mg Миллиграмм 1,00 E-03 Грамм
н Грамм 1,00 E + 00 Грамм
кг Килограмм 1,00 E + 03 Грамм
Мг Мегаграмм 1,00 E + 06 Грамм
Гг Гигаграмм 1,00 E + 09 Грамм
нДж Наноджоуль 1,00 E-09 Джоуль
мкДж Микроджоуль 1,00 E-06 Джоуль
мДж Миллиджоуль 1,00 E-03 Джоуль
J Джоуль 1,00 E + 00 Джоуль
КДж Килоджоуль 1,00 E + 03 Джоуль
МДж Мегаджоуль 1,00 E + 06 Джоуль
ГДж Гигаджоуль 1,00 E + 09 Джоуль
кал Калория 1,00 E + 00 калория
ккал Килокалория 1,00 E + 03 калория
МЕ Международные единицы

Следующий запрос имитирует соединение с этими данными, чтобы добавить в выходные данные название единицы измерения:

SELECT TOP 10 n.id, n.description, n.nutritionValue, n.units, r.name
FROM food
JOIN n IN food.nutrients
JOIN r 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'},
        {unit: 'nJ', name: 'nanojoule', multiplier: 0.000000001, baseUnit: 'joule'},
        {unit: 'µJ', name: 'microjoule', multiplier: 0.000001, baseUnit: 'joule'},
        {unit: 'mJ', name: 'millijoule', multiplier: 0.001, baseUnit: 'joule'},
        {unit: 'J', name: 'joule', multiplier: 1, baseUnit: 'joule'},
        {unit: 'kJ', name: 'kilojoule', multiplier: 1000, baseUnit: 'joule'},
        {unit: 'MJ', name: 'megajoule', multiplier: 1000000, baseUnit: 'joule'},
        {unit: 'GJ', name: 'gigajoule', multiplier: 1000000000, baseUnit: 'joule'},
        {unit: 'cal', name: 'calorie', multiplier: 1, baseUnit: 'calorie'},
        {unit: 'kcal', name: 'Calorie', multiplier: 1000, baseUnit: 'calorie'},
        {unit: 'IU', name: 'International units'}
    ]
)
WHERE n.units = r.unit

скалярные вложенные запросы;

Скалярное выражение вложенного запроса — это вложенный запрос, результатом которого является единственное значение. Значением скалярного выражения вложенного запроса является значение проекции (предложение SELECT) вложенного запроса. Можно использовать скалярное выражение вложенного запроса во многих случаях, когда допустимо использование скалярного выражения. Например, можно использовать скалярный вложенный запрос в любом выражении в предложениях SELECT и WHERE.

При этом использование скалярного вложенного запроса не всегда упрощает процесс оптимизации. Например, передача скалярного вложенного запроса в качестве аргумента в системную или определяемую пользователем функцию не дает никаких преимуществ, с точки зрения единицы использования ресурса (RU) или задержки.

Скалярные вложенные запросы можно дополнительно классифицировать следующим образом:

  • Скалярные вложенные запросы в простых выражениях
  • Агрегирование скалярных вложенных запросов

Скалярные вложенные запросы в простых выражениях

Скалярный вложенный запрос с простым выражением — это коррелированный вложенный запрос, имеющий предложение SELECT, которое не содержит статистических выражений. Эти вложенные запросы не дают никаких преимуществ оптимизации, поскольку компилятор преобразует их в одно более крупное простое выражение. Не просматривается корреляция контекста между внутренними и внешними запросами.

Рассмотрим несколько примеров.

Пример 1

SELECT 1 AS a, 2 AS b

Можно переписать этот запрос с помощью скалярного вложенного запроса простого выражения, чтобы:

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

Эти выходные данные создавались в результате обоих запросов:

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

Пример 2

SELECT TOP 5 Concat('id_', f.id) AS id
FROM food f

Можно переписать этот запрос с помощью скалярного вложенного запроса простого выражения, чтобы:

SELECT TOP 5 (SELECT VALUE Concat('id_', f.id)) AS id
FROM food f

Вывод запроса:

[
  { "id": "id_03226" },
  { "id": "id_03227" },
  { "id": "id_03228" },
  { "id": "id_03229" },
  { "id": "id_03230" }
]

Пример 3

SELECT TOP 5 f.id, Contains(f.description, 'fruit') = true ? f.description : undefined
FROM food f

Можно переписать этот запрос с помощью скалярного вложенного запроса простого выражения, чтобы:

SELECT TOP 10 f.id, (SELECT f.description WHERE Contains(f.description, 'fruit')).description
FROM food f

Вывод запроса:

[
  { "id": "03230" },
  { "id": "03238", "description":"Babyfood, dessert, tropical fruit, junior" },
  { "id": "03229" },
  { "id": "03226", "description":"Babyfood, dessert, fruit pudding, orange, strained" },
  { "id": "03227" }
]

Агрегирование скалярных вложенных запросов

Статистический скалярный вложенный запрос — это вложенный запрос, имеющий агрегатную функцию в проекции или фильтре, результатом которой является единственное значение.

Пример 1.

Вот вложенный запрос с одним выражением агрегатной функции в его проекции:

SELECT TOP 5 
    f.id, 
    (SELECT VALUE Count(1) FROM n IN f.nutrients WHERE n.units = 'mg'
) AS count_mg
FROM food f

Вывод запроса:

[
  { "id": "03230", "count_mg": 13 },
  { "id": "03238", "count_mg": 14 },
  { "id": "03229", "count_mg": 13 },
  { "id": "03226", "count_mg": 15 },
  { "id": "03227", "count_mg": 19 }
]

Пример 2

Вот вложенный запрос с несколькими выражениями агрегатных функций:

SELECT TOP 5 f.id, (
    SELECT Count(1) AS count, Sum(n.nutritionValue) AS sum 
    FROM n IN f.nutrients 
    WHERE n.units = 'mg'
) AS unit_mg
FROM food f

Вывод запроса:

[
  { "id": "03230","unit_mg": { "count": 13,"sum": 147.072 } },
  { "id": "03238","unit_mg": { "count": 14,"sum": 107.385 } },
  { "id": "03229","unit_mg": { "count": 13,"sum": 141.579 } },
  { "id": "03226","unit_mg": { "count": 15,"sum": 183.91399999999996 } },
  { "id": "03227","unit_mg": { "count": 19,"sum": 94.788999999999987 } }
]

Пример 3

Вот запрос со статистическим вложенным запросом как в проекции, так и в фильтре:

SELECT TOP 5 
    f.id, 
	(SELECT VALUE Count(1) FROM n IN f.nutrients WHERE n.units = 'mg') AS count_mg
FROM food f
WHERE (SELECT VALUE Count(1) FROM n IN f.nutrients WHERE n.units = 'mg') > 20

Вывод запроса:

[
  { "id": "03235", "count_mg": 27 },
  { "id": "03246", "count_mg": 21 },
  { "id": "03267", "count_mg": 21 },
  { "id": "03269", "count_mg": 21 },
  { "id": "03274", "count_mg": 21 }
]

Более оптимальный способ написания этого запроса — соединение во вложенном запросе и ссылка на псевдоним вложенного запроса в предложениях SELECT и WHERE. Этот запрос более эффективен, поскольку необходимо выполнить вложенный запрос только внутри инструкции объединения, а не в проекции и фильтре.

SELECT TOP 5 f.id, count_mg
FROM food f
JOIN (SELECT VALUE Count(1) FROM n IN f.nutrients WHERE n.units = 'mg') AS count_mg
WHERE count_mg > 20

Выражение EXISTS

Azure Cosmos DB поддерживает выражения EXISTS. Это агрегированный скалярный вложенный запрос, встроенный в Azure Cosmos DB API SQL. EXISTS является логическим выражением, которое принимает выражение вложенного запроса и возвращает значение true, если вложенный запрос возвращает какие-либо записи. В противном случае возвращается значение false.

Так как API Azure Cosmos DB SQL не проводит различия между логическими выражениями и любыми другими скалярными выражениями, можно использовать EXISTS в предложениях SELECT и WHERE. В отличие от T-SQL, где логическое выражение (например, EXISTS, BETWEEN и IN) ограничено фильтром.

Если вложенный запрос EXISTS возвращает одиночное значение, которое не определено, тогда EXISTS принимает значение false. Например, рассмотрим следующий запрос, результатом которого является значение false:

SELECT EXISTS (SELECT VALUE undefined)

Если ключевое слово VALUE в предыдущем вложенном запросе опущено, результатом вычисления запроса будет значение true:

SELECT EXISTS (SELECT undefined) 

Вложенный запрос будет заключать список значений в выбранном списке в объекте. Если выбранный список не содержит значений, вложенный запрос возвратит единственное значение "{}". Это значение определено, поэтому значение EXISTS будет равно true.

Пример: перезапись ARRAY_CONTAINS и JOIN как EXISTS

Распространенным вариантом использования ARRAY_CONTAINS является фильтрация документа по наличию элемента в массиве. В этом случае мы проверяем, содержит ли массив тегов элемент с названием "оранжевый".

SELECT TOP 5 f.id, f.tags
FROM food f
WHERE ARRAY_CONTAINS(f.tags, {name: 'orange'})

Можно переписать тот же запрос, чтобы использовать EXISTS:

SELECT TOP 5 f.id, f.tags
FROM food f
WHERE EXISTS(SELECT VALUE t FROM t IN f.tags WHERE t.name = 'orange')

Кроме того, ARRAY_CONTAINS может лишь проверять, равно ли значение какому-либо элементу в массиве. Если требуются более сложные фильтры для свойств массива, используйте JOIN.

Рассмотрим следующий запрос, который выполняет фильтрацию на основе единиц и свойств nutritionValue в массиве:

SELECT VALUE c.description
FROM c
JOIN n IN c.nutrients
WHERE n.units= "mg" AND n.nutritionValue > 0

Для каждого из документов в коллекции перекрестное перемножение выполняется с элементами массива. Эта операция JOIN позволяет фильтровать массив по свойствам. В то же время, с точки зрения количества единиц запроса такой запрос будет достаточно затратным. Например, если в 1000 документах в каждом массиве содержалось бы 100 элементов, он развернется до 1000 × 100 (т. е. 100 000) кортежей.

Использование EXISTS может помочь избежать этого затратного перекрестного перемножения:

SELECT VALUE c.description
FROM c
WHERE EXISTS(
    SELECT VALUE n
    FROM n IN c.nutrients
    WHERE n.units = "mg" AND n.nutritionValue > 0
)

В этом случае производится фильтрация элементов массива внутри вложенного запроса EXISTS. Если элемент массива соответствует фильтру, тогда его проецируют, а EXISTS будет иметь значение true.

Можно также присвоить псевдоним EXISTS и ссылаться на него в проекции:

SELECT TOP 1 c.description, EXISTS(
    SELECT VALUE n
    FROM n IN c.nutrients
    WHERE n.units = "mg" AND n.nutritionValue > 0) as a
FROM c

Вывод запроса:

[
    {
        "description": "Babyfood, dessert, fruit pudding, orange, strained",
        "a": true
    }
]

Выражение ARRAY

Можно использовать выражение ARRAY для проецирования результатов запроса в виде массива. Это выражение можно использовать только в предложении SELECT запроса.

SELECT TOP 1   f.id, ARRAY(SELECT VALUE t.name FROM t in f.tags) AS tagNames
FROM  food f

Вывод запроса:

[
    {
        "id": "03238",
        "tagNames": [
            "babyfood",
            "dessert",
            "tropical fruit",
            "junior"
        ]
    }
]

Как и в случае с другими вложенными запросами, возможны фильтры с выражением ARRAY.

SELECT TOP 1 c.id, ARRAY(SELECT VALUE t FROM t in c.tags WHERE t.name != 'infant formula') AS tagNames
FROM c

Вывод запроса:

[
    {
        "id": "03226",
        "tagNames": [
            {
                "name": "babyfood"
            },
            {
                "name": "dessert"
            },
            {
                "name": "fruit pudding"
            },
            {
                "name": "orange"
            },
            {
                "name": "strained"
            }
        ]
    }
]

Выражения ARRAY могут также следовать после предложения FROM во вложенных запросах.

SELECT TOP 1 c.id, ARRAY(SELECT VALUE t.name FROM t in c.tags) as tagNames
FROM c
JOIN n IN (SELECT VALUE ARRAY(SELECT t FROM t in c.tags WHERE t.name != 'infant formula'))

Вывод запроса:

[
    {
        "id": "03238",
        "tagNames": [
            "babyfood",
            "dessert",
            "tropical fruit",
            "junior"
        ]
    }
]

Дальнейшие действия