Этот пост является ответом на приглашение Вторник T-SQL в этом месяце. T-SQL вторник был создан Адамом Махаником и позволяет пользователям SQL обмениваться идеями по интересным темам. Тема этого месяца — Решение старых проблем с помощью новых блестящих игрушек.

Почему я люблю JSON

Я знаю, что многие пользователи в сообществе SQL съеживаются, когда слышат о JSON, NoSQL и других вариантах хранения нереляционных баз данных. Я понимаю. Много раз формат хранения строк компрометирует многие преимущества, которые может предложить SQL: индексирование, целостность данных, простые запросы, надзор за изменениями схемы разработчиками и т. д. Я видел (и писал) некоторые запросы, которые работают как патока только зимой. из-за того, что они находятся в строковом формате, не подлежащем анализу.

Тем не менее, несмотря на все эти недостатки, я все еще люблю JSON. Я думаю, что существует золотая середина, когда администраторы баз данных и разработчики могут договориться о том, когда уместно использовать JSON. Захочу ли я когда-нибудь использовать JSON в хранилище данных, которое активно запрашивается для аналитики? Нет. Хотел бы я также использовать JSON там, где производительность чрезвычайно важна? Нет. Хотел бы я использовать его в приложении, где чрезвычайно важна целостность данных или схемы? Нет.

Однако мне нравится использовать JSON при захвате динамически структурированного пользовательского ввода, особенно если база данных используется только для сохранения данных, а не для их анализа. Мне нравится иметь JSON в базе данных, потому что он так легко сериализует/десериализует между JSON и моими моделями C#. Если мне нужна низкая задержка для моего веб-приложения, я также могу записать JSON из приложения в базу данных, а затем поставить его в очередь для преобразования в нормализованные данные с помощью ETL позже. Именно по этим причинам я невероятно взволнован новой функциональностью JSON в SQL Server 2016.

Хорошо, я думаю, есть несколько допустимых применений JSON. Каковы мои варианты использования JSON в SQL Server?

До выпуска SQL Server 2016 в SQL Server не было поддержки JSON. Никто. пшик. Нуль. Лучшее, что у нас было, это потрясающая определяемая пользователем функция парсинга JSON от Фила Фактора или написание собственной CLR.

Функция Фила действительно потрясающая, и она ловко использует некоторые встроенные функции SQL для десериализации практически любого типа строки JSON, которую вы можете ей передать. Однако у него есть недостатки — у функции есть несколько ошибок в крайних случаях (попробуйте добавить пробел перед любым «:» в вашем JSON), это не позволяет легко запрашивать JSON, и функцию необходимо добавить в любую базу данных. вы хотите использовать его на.

Я не пытаюсь преуменьшить значение функции Фила — она мне нравится и я использую ее во многих своих программах, — но дело в том, что это очень умный обходной путь для того, что Microsoft в то время не поддерживала.

Так как же выглядел синтаксический анализ JSON в мире до 2016 года? Давайте посмотрим на некоторые примеры данных, которые я сгенерировал с помощью http://www.json-generator.com/:

По сути, у нас есть строка JSON, которая представляет пять пользователей и некоторые их атрибуты (примечание: я бы сказал, что хранение такого большого количества данных о человеке лучше подходит для некоторых структурированных таблиц, но, увы, это был тип данных, который был легко создать для примера).

Если бы я хотел проанализировать имя каждого пользователя, чтобы использовать его для редко запускаемого отчета или для целей ETL, мой запрос должен был бы выглядеть так:

Есть пара забавных вещей, которые мы должны сделать в SQL до 2016 года, чтобы разобрать этот JSON:

  1. Мы должны добавить функцию parseJson() в нашу базу данных.
  2. Поскольку существует несколько ключей с именем «NAME», нам нужно добавить функцию LAG(), чтобы помочь идентифицировать ключ свойства, чтобы мы могли убедиться, что у нас есть правильный ключ.
  3. Требуются вложенные запросы, так как оконные функции не могут выполняться в предложении WHERE.

В целом этот код работает, и он выполняет свою работу. Однако нам нужно убедиться, что на наш сервер добавлена ​​функция parseJson(, а фильтрация наших данных JSON для извлечения интересующих нас свойств требует небольшой работы. Кроме того, функция parseJson() выполняет некоторую тяжелую обработку, которая влияет на нашу общую производительность:

Блестящие новые игрушки в SQL Server 2016: функции JSON

SQL Server 2016 включает множество новых функций разбора JSON, которые мы можем использовать. Написать запрос для возврата того же набора результатов, что и выше, теперь так же просто:

Функция OPENJSON() позволяет нам написать запрос XPath для фильтрации объекта JSON Users в пять отдельных строк данных, по одной для каждого пользователя:

Затем мы снова используем функцию JSON_VALUE() и XPath, чтобы отфильтровать только свойство «имя». В целом, этот код написать намного проще, он работает более последовательно (проблема с пробелом перед «:» обрабатывается правильно) и работает намного быстрее:

Мне нравится синтаксис новых функций синтаксического анализа SQL JSON: его легко запомнить, легко использовать в реальных сценариях и он очень быстрый. Это одна из новых функций SQL Server 2016, которая определенно облегчает мою жизнь и делает любые другие способы разбора данных JSON устаревшими.

Если вам интересно узнать больше о JSON в SQL-сервере, последние несколько недель я вел блог о том, как использовать все новые функции JSON в SQL.

Также есть одна ошибка, которую я обнаружил в разделе Как SQL обрабатывает удаления JSON, которые я отправил в Microsoft Connect, если вы хотите проголосовать и увидеть, как JSON продолжает развиваться на SQL Server.