Эта статья поможет вам расширить базовые возможности SQL-запросов Cosmos DB. Здесь вы узнаете несколько хитростей, которые могут вам понадобиться, чтобы преодолеть ограничения в стиле Cosmos DB SQL.

Строковые функции для поиска по шаблону

Движок Cosmos DB неплохо подходит для поиска по обширным наборам данных JSON. Если вам нужно использовать поиск по шаблону для текста внутри определенного атрибута, функция CONTAINS() является хорошей отправной точкой. Вы используете его скорее как T-SQL LIKE в предложении WHERE.

ПРИМЕЧАНИЕ. Загрузите данные SimpleCars2 в эмулятор Cosmos DB. Для получения справки об этом смотрите первую статью в этой серии.

Запустите примеры из этой статьи для SimpleCars2.

Этот запрос находит следующие документы в исходной коллекции:

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

В качестве альтернативы, если вам нужно настроить поиск по шаблону, который применяется к началу или концу содержимого атрибута, аналогично LIKE «%какой-то текст» в T-SQL, вы можете использовать этот подход:

Этот более сфокусированный поиск по шаблону дает следующий результат:

Этот запрос будет по-прежнему чувствителен к регистру, но вы можете использовать функции UPPER() или LOWER(), чтобы обеспечить применение поиска без учета регистра.

Обработка NULL в Cosmos DB SQL

Одна из обнадеживающих общих возможностей между T-SQL и Cosmos DB SQL заключается в том, что значения NULL, выраженные в JSON как строчные слова без кавычек в JSON, «заражают» весь расчет и возвращают значение NULL, если какой-либо один атрибут имеет значение NULL. Это то, что вы должны научиться обрабатывать в документах JSON.

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

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

Предполагая, что запрос работает (а нет причин, по которым он не должен работать), вывод должен быть следующим:

Здесь вы комбинируете функцию IS_NULL () из Cosmos DB SQL которая обнаруживает отсутствующий атрибут, с троичной логикой для имитации функции ISNULL () T-SQL. Более того, вы заключили троичную логику обнаружения NULL в круглые скобки следующим образом:

(IS_NULL(s.LineItemDiscount) ? 0 : s.LineItemDiscount)

Это гарантирует, что вывод работает так, как вы ожидаете. Действительно, если забыть заключить логику IS_NULL () в круглые скобки, это вызовет проблемы.

Если вы выполните этот запрос к коллекции SimpleCars2, вы увидите, что этот метод работает безупречно для Invoice GBPGB002, где в исходном документе JSON установлен атрибут LineItemDiscount, равный нулю. К сожалению, общие затраты отсутствуют по трем счетам, номерам GBPGB003, GBPGB004 и GBPGB005. Если вы посмотрите на эти документы, то увидите, что во всех них отсутствует атрибут LineItemDiscount.

Вы непоколебимо пытаетесь исправить ситуацию, используя функцию IS_DEFINED(), например таким образом (и обратите внимание, что троичная логика перевернута по сравнению с функцией IS_NULL()):

На этот раз вывод выглядит следующим образом:

В этом случае вы увидите затраты для счетов-фактур GBPGB003, GBPGB004 и GBPGB005, где вообще нет атрибута для скидки на позицию, но не для GBPGB002, документа с фактическим нулевым значением.

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

Наконец, вы получаете результат, который искали:

Здесь вы используете простую логику, чтобы, во-первых, выявить, определен ли атрибут, и, во-вторых, чтобы убедиться, что, если он присутствует, то не содержит нуль. Такой подход приближается к базовой обработке нуля в T-SQL. Как только вы достигнете определенного уровня владения в Cosmos DB, вы можете написать свои собственные пользовательские функции на JavaScript для решения подобных проблем.

Учитывая явные ограничения Cosmos DB SQL, вам придется проявить изобретательность при написании SQL-запросов. Вам нужно будет написать сложный SQL, используя комбинации доступных функций для достижения ваших целей.

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

SUBSTRING() для LEFT()

Cosmos DB SQL в настоящее время не имеет эквивалента функции SQL LEFT (). Вместо этого вам нужно проявить немного изобретательности и применить функцию SUBSTRING() следующим образом:

Вы должны получить следующий результат:

SUBSTRING() для RIGHT()

Так же, как нет функции LEFT(), нет функции RIGHT(). Однако объединение функций SUBSTRING() и REVERSE(), как это, может решить определенные проблемы:

На этот раз результат будет следующим:

Помните, что если вы ищете более одного символа справа от строки, используя эту технику, вам нужно будет перевернуть строку, которую вы ищете. То есть вместо того, чтобы искать «red», вы должны были бы ввести «der».

Если ввод текста поиска в обратном порядке вызывает у вас дискомфорт, вы всегда можете добавить второй REVERSE(), например:

Напомним, что это чувствительно к регистру, если вы не расширите SQL для обеспечения учета регистра, как здесь:

Имитация T-SQL YEAR(), MONTH() и DAY()

В Cosmos DB SQL нет функции YEAR(). Однако одна простая альтернатива - применить функцию SUBSTRING() к строке даты и изолировать соответствующий элемент даты. Чтобы указать документы JSON для определенного года, вы можете написать:

Выходные данные этого запроса отображают дату продажи шести из семи документов в коллекции.

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

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

Этот фильтр возвращает только один объект.

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

Операторы> =, <=, <> или ! =

IN для набора лет и т. д.

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

Округление до ближайшей 1000

В качестве простого примера того, как расширить SQL-функции Cosmos DB, рассмотрим следующий фрагмент кода, который расширяет функцию TRUNC () до ближайшей тысячи:

Здесь вывод будет выглядеть следующим образом:

Заключение

Существует множество других обходных путей, которые вы можете применить, чтобы преодолеть ограничения реализации SQL в Cosmos DB, и этот короткий список может быть расширен для охвата ряда вариантов. Однако это будет похоже на возвращение к SQL Server примерно в 1994 году, и даже это не охватит все проблемы, с которыми вы, вероятно, столкнетесь. Достаточно сказать, что вам, вероятно, потребуется некоторая изобретательность и написать чрезвычайно сложный SQL, чтобы сделать что-то, что было бы просто в T-SQL, или рассмотреть другие решения.

У Cosmos DB есть короткий ответ на этот вопрос, а именно на то, чтобы написать свои собственные пользовательские функции в JavaScript. Это богатый и чрезвычайно мощный язык, и его может быть довольно легко дополнить Cosmos DB SQL множеством функций, отвечающих вашим конкретным требованиям. Тем не менее, цель этой статьи состоит в том, чтобы взглянуть на SQL-код Cosmos DB, и поэтому для изучения того, как добавлять расширения на языке, с которым вы, возможно, не знакомы, придется подождать еще один день.

В следующей статье этой серии будет рассказано, как запрашивать более сложные документы JSON.              

SELECT  s.CustomerName ,s.SalePrice

FROM    simplecars AS s

WHERE   SUBSTRING(REVERSE(s.CustomerName), 0, 1) = "r"

No Comments