Вложенные запросы — язык запросов в Cosmos DB (в Azure и Fabric)

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

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

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

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

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

• Таблица: возвращает несколько строк и несколько столбцов.
• Многозначное значение: возвращает несколько строк и один столбец.
• Скаляр: возвращает одну строку и один столбец.

Запросы на языке запросов всегда возвращают один столбец (простое значение или сложный элемент). Поэтому применимы только многозначные и скалярные вложенные запросы. Вложенный запрос с несколькими значениями можно использовать только в предложении в FROM качестве реляционного выражения. Скалярный вложенный запрос можно использовать в качестве скалярного выражения в SELECT предложении или WHERE в качестве реляционного FROM выражения.

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

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

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

Оптимизация выражений самосоединения

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

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

SELECT VALUE
  COUNT(1)
FROM
  products p
JOIN 
  t in p.tags
JOIN
  s in p.sizes
JOIN
  c in p.colors
WHERE
  t.key IN ("fabric", "material") AND
  s["order"] >= 3 AND
  c LIKE "%gray%"

Для этого запроса индекс соответствует любому элементу, который имеет тег с keyfabricmaterialодним или или по крайней мере одним размером со order значением больше *3, и по крайней мере один цвет с gray подстрокой. Выражение здесь JOIN выполняет перекрестное произведение всех элементов и sizescolors массивов для каждого соответствующего tagsэлемента перед применением любого фильтра.

Затем предложение WHERE применяет предикат фильтра для каждого кортежа $<c, t, n, s>$ кортежа. Например, если соответствующий элемент содержит десять элементов в каждом из трех массивов, он расширяется до 1000 кортежей с помощью этой формулы:

$$ 1 x 10 x 10 x 10 $$

С помощью вложенных запросов здесь можно отфильтровать присоединенные элементы массива перед присоединением к следующему выражению.

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

SELECT VALUE
  COUNT(1)
FROM
  products p
JOIN 
  (SELECT VALUE t FROM t IN p.tags WHERE t.key IN ("fabric", "material"))
JOIN 
  (SELECT VALUE s FROM s IN p.sizes WHERE s["order"] >= 3)
JOIN 
  (SELECT VALUE c FROM c in p.colors WHERE c LIKE "%gray%")

Предположим, что только один элемент в массиве тегов соответствует фильтру, и есть пять элементов как для количества, так и для массивов запасов. Затем JOIN выражение расширяется до 25 кортежей, использующих эту формулу, в отличие от 1000 элементов в первом запросе:

$$ 1 x 1 x 5 x 5$$

Оценка один раз и ссылка много раз

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

Этот пример запроса вычисляет цену с дополнением 25% несколько раз в запросе.

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

Ниже приведен эквивалентный запрос, который выполняет вычисление только один раз:

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

[
  {
    "subtotal": 15,
    "total": 18.75
  },
  {
    "subtotal": 10,
    "total": 12.5
  },
  ...
]

Подсказка

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

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

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

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

Size	Length	Единицы
xs	63.5	cm
s	64.5	cm
m	66.0	cm
l	67.5	cm
xl	69.0	cm
xxl	70.5	cm

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

SELECT
  p.name,
  p.subCategory,
  s.description AS size,
  m.length,
  m.unit
FROM
  products p
JOIN
  s IN p.sizes
JOIN m IN (
  SELECT VALUE [
    {size: 'xs', length: 63.5, unit: 'cm'},
    {size: 's', length: 64.5, unit: 'cm'},
    {size: 'm', length: 66, unit: 'cm'},
    {size: 'l', length: 67.5, unit: 'cm'},
    {size: 'xl', length: 69, unit: 'cm'},
    {size: 'xxl', length: 70.5, unit: 'cm'}
  ]
)
WHERE
  s.key = m.size

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

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

Использование скалярного вложенного запроса не всегда помогает оптимизировать запрос. Например, передача скалярного вложенного запроса в качестве аргумента в системные или определяемые пользователем функции не обеспечивает преимущества снижения потребления единиц ресурсов (ЕЗ) или задержки.

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

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

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

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

В первом примере рассмотрим этот тривиальный запрос.

SELECT
  1 AS a,
  2 AS b

Этот запрос можно переписать с помощью скалярного запроса простого выражения.

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

Оба запроса создают одинаковые выходные данные.

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

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

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

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

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

[
  {
    "id": "00000000-0000-0000-0000-000000004041",
    "name": "Remdriel Shoes"
  },
  {
    "id": "00000000-0000-0000-0000-000000004322"
  },
  {
    "id": "00000000-0000-0000-0000-000000004055"
  }
]

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

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

В первом примере рассмотрим элемент со следующими полями.

[
  {
    "name": "Blators Snowboard Boots",
    "colors": [
      "turquoise",
      "cobalt",
      "jam",
      "galliano",
      "violet"
    ],
    "sizes": [ ... ],
    "tags": [ ... ]
  }
]

Ниже приведен вложенный запрос с одним статистическим выражением функции в проекции. Этот запрос подсчитывает все теги для каждого элемента.

SELECT
  p.name,
  (SELECT VALUE COUNT(1) FROM c IN p.colors) AS colorsCount
FROM
  products p
WHERE
  p.id = "00000000-0000-0000-0000-000000004389"

[
  {
    "name": "Blators Snowboard Boots",
    "colorsCount": 5
  }
]

Ниже приведен тот же вложенный запрос с фильтром.

SELECT
  p.name,
  (SELECT VALUE COUNT(1) FROM c IN p.colors) AS colorsCount,
  (SELECT VALUE COUNT(1) FROM c IN p.colors WHERE c LIKE "%t") AS colorsEndsWithTCount
FROM
  products p

[
  {
    "name": "Blators Snowboard Boots",
    "colorsCount": 5,
    "colorsEndsWithTCount": 2
  }
]

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

SELECT
  p.name,
  (SELECT VALUE COUNT(1) FROM c IN p.colors) AS colorsCount,
  (SELECT VALUE COUNT(1) FROM s in p.sizes) AS sizesCount,
  (SELECT VALUE COUNT(1) FROM t IN p.tags) AS tagsCount
FROM
  products p

[
  {
    "name": "Blators Snowboard Boots",
    "colorsCount": 5,
    "sizesCount": 7,
    "tagsCount": 2
  }
]

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

SELECT
  p.name,
  (SELECT VALUE COUNT(1) FROM s in p.sizes WHERE s.description LIKE "%Small") AS smallSizesCount,
  (SELECT VALUE COUNT(1) FROM s in p.sizes WHERE s.description LIKE "%Large") AS largeSizesCount
FROM
  products p
WHERE
  (SELECT VALUE COUNT(1) FROM c IN p.colors) >= 5

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

SELECT
  p.name,
  colorCount,
  smallSizesCount,
  largeSizesCount
FROM
  products p
JOIN
  (SELECT VALUE COUNT(1) FROM c IN p.colors) AS colorCount
JOIN
  (SELECT VALUE COUNT(1) FROM s in p.sizes WHERE s.description LIKE "%Small") AS smallSizesCount
JOIN
  (SELECT VALUE COUNT(1) FROM s in p.sizes WHERE s.description LIKE "%Large") AS largeSizesCount
WHERE
  colorCount >= 5 AND
  largeSizesCount > 0 AND
  smallSizesCount > 0

Выражение EXISTS

Язык запросов поддерживает EXISTS выражения. Это выражение является статистическим скалярным вложенным запросом, встроенным в язык запросов. EXISTS принимает выражение вложенного запроса и возвращает true , если вложенный запрос возвращает все строки. В противном случае он возвращает false.

Так как обработчик запросов не отличает логические выражения и другие скалярные выражения, их можно использовать EXISTS как в предложениях, так и WHERE в SELECT предложениях. Это поведение в отличие от T-SQL, где логическое выражение ограничено только фильтрами.

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

SELECT VALUE
  undefined

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

SELECT VALUE
  EXISTS (SELECT VALUE undefined)

[
  false
]

Если ключевое слово VALUE в предыдущем вложенных запросах опущено, вложенный запрос вычисляет массив с одним пустым объектом.

SELECT
  undefined

[
  {}
]

На этом этапе EXISTS выражение оценивается true так, как объект ({}) технически завершает работу.

SELECT VALUE
  EXISTS (SELECT undefined)

[
  true
]

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

SELECT
  p.name,
  p.colors
FROM
  products p
WHERE
  ARRAY_CONTAINS(p.colors, "cobalt")

Тот же запрос может использоваться EXISTS в качестве альтернативного варианта.

SELECT
  p.name,
  p.colors
FROM
  products p
WHERE
  EXISTS (SELECT VALUE c FROM c IN p.colors WHERE c = "cobalt")

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

Рассмотрим этот пример элемента в наборе с несколькими элементами, содержащими accessories массив.

[
  {
    "name": "Cosmoxy Pack",
    "tags": [
      {
        "key": "fabric",
        "value": "leather",
        "description": "Leather"
      },
      {
        "key": "volume",
        "value": "68-gal",
        "description": "6.8 Gal"
      }
    ]
  }
]

Теперь рассмотрим следующий запрос, который фильтрует на type основе и quantityOnHand свойств массива в каждом элементе.

SELECT
  p.name,
  t.description AS tag
FROM
  products p
JOIN
  t in p.tags
WHERE
  t.key = "fabric" AND
  t["value"] = "leather"

[
  {
    "name": "Cosmoxy Pack",
    "tag": "Leather"
  }
]

Для каждого элемента в коллекции кросс-продукт выполняется со своими элементами массива. Эта JOIN операция позволяет фильтровать свойства в массиве. Однако потребление ЕЗ этого запроса имеет значительное значение. Например, если в каждом массиве 1000 элементов было 100 элементов, он расширяется до 100 000 кортежей с помощью этой формулы:

$$ 1000 x 100 $$

Использование EXISTS помогает избежать этого дорогого кросс-продукта. В следующем примере запрос фильтрует элементы массива в подзапросе EXISTS . Если элемент массива соответствует фильтру, проектируйте его и EXISTS оцениваете как true.

SELECT VALUE
  p.name
FROM
  products p
WHERE
  EXISTS (
    SELECT VALUE
      t
    FROM
      t IN p.tags
    WHERE
      t.key = "fabric" AND
      t["value"] = "leather"
  )

[
  "Cosmoxy Pack"
]

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

SELECT
  p.name,
  EXISTS (
    SELECT VALUE
      t
    FROM
      t IN p.tags
    WHERE
      t.key = "fabric" AND
      t["value"] = "leather"
  ) AS containsFabricLeatherTag
FROM
  products p

[
  {
    "name": "Cosmoxy Pack",
    "containsFabricLeatherTag": true
  }
]

Выражение ARRAY

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

В этих примерах предположим, что есть контейнер с по крайней мере этим элементом.

[
  {
    "name": "Menti Sandals",
    "sizes": [
      {
        "key": "5"
      },
      {
        "key": "6"
      },
      {
        "key": "7"
      },
      {
        "key": "8"
      },
      {
        "key": "9"
      }
    ]
  }
]

В этом первом примере выражение используется в предложении SELECT .

SELECT
  p.name,
  ARRAY (
    SELECT VALUE
      s.key
    FROM
      s IN p.sizes
  ) AS sizes
FROM
  products p
WHERE
  p.name = "Menti Sandals"

[
  {
    "name": "Menti Sandals",
    "sizes": [
      "5",
      "6",
      "7",
      "8",
      "9"
    ]
  }
]

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

SELECT
  p.name,
  ARRAY (
    SELECT VALUE
      s.key
    FROM
      s IN p.sizes
    WHERE
      STRINGTONUMBER(s.key) <= 6
  ) AS smallSizes,
  ARRAY (
    SELECT VALUE
      s.key
    FROM
      s IN p.sizes
    WHERE
      STRINGTONUMBER(s.key) >= 9
  ) AS largeSizes
FROM
  products p
WHERE
  p.name = "Menti Sandals"

[
  {
    "name": "Menti Sandals",
    "smallSizes": [
      "5",
      "6"
    ],
    "largeSizes": [
      "9"
    ]
  }
]

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

SELECT
  p.name,
  z.s.key AS sizes
FROM
  products p
JOIN
  z IN (
    SELECT VALUE
      ARRAY (
        SELECT
          s
        FROM
          s IN p.sizes
        WHERE
          STRINGTONUMBER(s.key) <= 8
      )
  )

[
  {
    "name": "Menti Sandals",
    "sizes": "5"
  },
  {
    "name": "Menti Sandals",
    "sizes": "6"
  },
  {
    "name": "Menti Sandals",
    "sizes": "7"
  },
  {
    "name": "Menti Sandals",
    "sizes": "8"
  }
]

Связанный контент

• Просмотр языка запросов
• Начало работы с JSON на языке запросов
• Выполнение самосоединений
• Изучение системных функций