Sql to json: Format Query Results as JSON with FOR JSON — SQL Server

SPBDEV Blog — JSON повсюду, так почему бы и не в SQL Server? Новая функция в SQL Server 2016



Tags:
SQL Server, SQL Server 2016, JSON


Wednesday, July 4, 2018 10:47:00 PM


Если вы разработчик, то наверняка вы могли бы использовать JSON (JavaScript Object Notation), но если нет, то не беспокойтесь, вы начнете использовать его рано или поздно. JSON — это своего рода экосистема, которая наиболее популярна в различных областях для обмена данными. Если вы говорите о графическом решении, AJAX, мобильных услугах или любой сторонней интеграции, то, как правило, JSON является первым выбором разработчиков.

Как вы возможно заметили, в настоящее время большинство баз данных NOSQL, таких как Microsoft Azure Document DB, MONGODB и т. д., также используют экосистему JSON, а некоторые из них основаны на JSON.

Потому что популярность этой системы растет с каждым днем. Итак, почему бы не использовать ее в SQL SERVER?

В SQL SERVER 2016 был представлен JSON. Это можно сказать, это шаг или мост между NON-реляционной базой данных и реляционной базой данных Microsoft SQL SERVER.

SQL Server 2016 предоставляет следующие возможности при использовании JSON:

  1. Разбор JSON по запросу отношения

  2. Вставка и обновление JSON с использованием запроса

  3. Хранение JSON в базе данных

 Концептуально, это похоже на тип данных XML, который вы можете использовать в SQL SERVER.
 Хорошим моментом для  JSON в SQL Server 2016 является то, что в нем нет собственного типа данных. Это поможет в миграции с любого NOSQL на SQL SERVER.

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

Теперь давайте перейдем непосредственно к практическому рассмотрению возможностей JSON в SQL SERVER.

1) ДЛЯ JSON AUTO
Он похож на FOR XML AUTO. Он вернет объект JSON выбранного столбца, где имя столбца будет рассматриваться как ключ или, другими словами, мы можем сказать, что он будет форматировать результат запроса в JSON.

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

2) ДЛЯ JSON PATH
Он точно такой же, как JSON auto — единственная разница с SQL SERVER в том, что мы имеем полный контроль над форматом. JSON Auto принимает предопределенную схему столбца, а с помощью пути JSON PATH мы можем создать сложный объект.

Например, мы используем таблицу заказов AdventureWorks Sales и объединяем ее с таблицей продуктов, чтобы получить подузел. Как вы видите на рисунке ниже, мы добавили узел Root. Этот корневой узел может быть добавлен и в JSON auto, если потребуется.

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

3) Функция IsJSON
По названию понятно, что это проверяющая функция.

Чтобы перекрестно проверить, является ли предоставленная строка допустимым JSON или нет, мы можем запустить ISJSON.

4) JSON_VALUE
По названию понятно, что если вы хотите получить значение конкретного ключа JSON, вы можете использовать эту прекрасную функцию JSON_VALUE.

5) Функция OPENJSON
Это очень хорошая функция, которую вы можете использовать для анализа внешней схемы. Предположим, у вас есть строка JSON из мобильной службы, которую вы передаете непосредственно в SQL Sever, а хранимая процедура SQL SERVER выполнит оставшуюся часть операции для ее анализа. OPENJSON может легко обрабатывать синтаксический анализ и другие операции. Единственная настройка здесь заключается в том, что для этого требуется уровень совместимости базы данных 130, который вам нужно настроить (если он не совместим с уровнем 130).

Есть много других интересных вещей, которые мы рассмотрим позже.











Вложенный вывод JSON в формате

с режимом PATH — SQL Server

Редактировать

Твиттер

LinkedIn

Фейсбук

Электронная почта

  • Статья

Применимо к:
SQL Server 2016 (13.x) и более поздние версии База данных SQL Azure Управляемый экземпляр Azure SQL

Для сохранения полного контроля над выходными данными FOR JSON укажите параметр PATH .

Режим PATH позволяет создавать объекты-оболочки и вкладывать сложные свойства. Результаты форматируются как массив объектов JSON.

В качестве альтернативы можно использовать параметр AUTO для автоматического форматирования вывода на основе структуры оператора SELECT .

  • Дополнительные сведения о параметре AUTO см. в разделе Автоматическое форматирование вывода JSON в режиме AUTO.
  • Обзор обоих вариантов см. в разделе Форматирование результатов запроса в формате JSON с FOR JSON.

Вот несколько примеров предложения FOR JSON с параметром PATH . Форматируйте вложенные результаты, используя имена столбцов, разделенных точками, или используя вложенные запросы, как показано в следующих примерах. По умолчанию нулевые значения не включаются в выходные данные FOR JSON . Azure Data Studio — рекомендуемый редактор запросов для запросов JSON, поскольку он автоматически форматирует результаты JSON (как показано в этой статье) вместо отображения простой строки.

Пример — имена столбцов, разделенные точками

Следующий запрос форматирует первые пять строк из таблицы AdventureWorks Person как JSON.

Предложение FOR JSON PATH использует псевдоним или имя столбца для определения имени ключа в выходных данных JSON. Если псевдоним содержит точки, параметр PATH создает вложенные объекты.

Запрос 

 ВЫБЕРИТЕ ПЕРВЫЕ 5
       BusinessEntityID как идентификатор,
       Имя Фамилия,
       Заголовок As 'Info.Title',
       Отчество как «Info.MiddleName»
   ОТ Человека.Человека
   ДЛЯ ПУТИ JSON

Результат 

 [{
«Идентификатор»: 1,
"Имя": "Кен",
"Фамилия": "Санчес",
"Информация": {
"Отчество": "J"
}
}, {
"Идентификатор": 2,
"Имя": "Терри",
"Фамилия": "Даффи",
"Информация": {
"Отчество": "Ли"
}
}, {
"Идентификатор": 3,
"Имя": "Роберто",
"Фамилия": "Тамбурелло"
}, {
"Идентификатор": 4,
"Имя": "Роб",
"Фамилия": "Уолтерс"
}, {
«Идентификатор»: 5,
"Имя": "Гейл",
"Фамилия": "Эриксон",
"Информация": {
«Должность»: «Мисс»,
"Отчество": "А"
}
}]

Пример — Несколько таблиц

Если вы ссылаетесь на несколько таблиц в запросе, FOR JSON PATH вкладывает каждый столбец, используя его псевдоним. Следующий запрос создает один объект JSON для каждой пары (OrderHeader, OrderDetails), объединенной в запросе.

Запрос 

 ВЫБРАТЬ TOP 2 H.SalesOrderNumber AS 'Order.Number',
        H.OrderDate AS 'Заказ.Дата',
        D.UnitPrice AS 'Продукт.Цена',
        D.OrderQty AS 'Продукт.Количество'
ОТ Sales.SalesOrderHeader H
   ВНУТРЕННЕЕ СОЕДИНЕНИЕ Sales.SalesOrderDetail D
     ON H.SalesOrderID = D.SalesOrderID
ДЛЯ ПУТИ JSON

Результат 

 [{
"Заказ": {
"Номер": "SO43659",
"Дата": "2011-05-31T00:00:00"
},
"Продукт": {
«Цена»: 2024.9940,
"Количество": 1
}
}, {
"Заказ": {
"Номер": "SO43659"
},
"Продукт": {
"Цена": 2024.9940
}
}]

Дополнительные сведения о JSON в SQL Server и базе данных SQL Azure

Видео Microsoft

Наглядное представление о встроенной поддержке JSON в SQL Server и базе данных SQL Azure см. в следующих видеороликах:

  • JSON как мост между NoSQL и реляционным миром

См.

также

Предложение FOR (Transact-SQL)

Обратная связь

Просмотреть все отзывы о странице

Преобразование результатов SQL Server в JSON

12 июля 2016 г., Сифизо Ндлову

В моей статье Хранилище данных в формате JSON в SQL Server 2016 мы рассмотрели доступные параметры T-SQL для преобразования данных JSON в строки и столбцы для заполнения хранилища данных на основе SQL Server. Растущая популярность JSON в современных веб-приложениях может привести к тому, что группам обработки данных потребуется предоставлять часть своих данных клиентским приложениям (например, средства отчетности, веб-службы и т. д.) в формате JSON. В этой статье мы рассмотрим, как такое требование может быть реализовано группами данных, использующими SQL Server 2016 FOR JSON, пункт 9.0003

SQL Server в JSON Поддерживаемые типы данных

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

  • Геометрия

  • География

  • Общеязыковая среда выполнения (CLR)

Разбивка поддерживаемых типов данных показана в Table1 .

Тип данных SQL Server Тип данных JSON
char, nchar, varchar, nvarchar, дата, дата и время, дата и время 2, время, смещение даты и времени, уникальный идентификатор, деньги строка
int, bigint, float, decimal, numeric номер
Бит Булево значение
varbinary, binary, image, timestamp, rowversion Строка в кодировке BASE64

Таблица 1

ДЛЯ JSON T-SQL Пункт

Хотя поддержка SQL Server для XML позволяла графически представлять данные с помощью редактора (показанного на рис. 1 ), попытка просмотра данных JSON с помощью редактора может разочаровать, поскольку данные JSON отображаются в виде одной неформатированной строки.

Рисунок 1

Рисунок 2

Поэтому рекомендуется, пока вы изучаете JSON в SQL Server, найти себе редактор JSON. Для целей этого обсуждения я буду использовать JSONFormatter с сайта любопытногоконцепта.com. Как видно из Рисунок 3 , вывод JSON из Рисунок 2 теперь правильно отформатирован.

Рисунок3

Существует два способа преобразования реляционных результатов в JSON, а именно параметры AUTO и PATH.

  1. Преобразование результатов в автоматическом режиме

    Это самый простой способ преобразовать реляционные данные в формат JSON, поскольку все, что вам нужно сделать, это добавить предложение FOR JSON AUTO в конце оператора SELECT. В этом режиме структура вывода JSON определяется комбинацией порядка столбцов в инструкции SELECT, а также таблиц, на которые ссылается инструкция SELECT. На рис. 4 показана инструкция T-SQL, которая преобразует результаты нашей вымышленной витрины данных о продажах фруктов в формат JSON.

    1

    2

    3

    4

    5

    6

    7

     

    SELECT

    продажи.[Номер позиции],продажи.[Дата транзакции],продажи.[Фрукты],продажи.[Количество]

    ,продажи.[Клиент],продажи.[MOP],продажи.[ Номер счета]

    ОТ [selectSIFISOBlogs].[DIM].[FruitSales] продаж

    ДЛЯ JSON AUTO

     

    Рисунок 4

    Результаты приведенных выше сценариев показаны ниже, в которых был возвращен один массив (представленный квадратной скобкой) со строками, содержащимися в виде объектов (представленных фигурными скобками).

    Рисунок 5

    Пример сценария, представленный в Рисунок 4 , не полностью демонстрирует роль упорядочения столбцов и таблиц в предложении FOR JSON AUTO, поскольку использовалась только одна таблица. На рис. 6 показан пересмотренный сценарий с присоединением к другому фиктивному измерению поиска клиентов, в котором хранится дополнительная информация о покупателях, купивших фрукты.

    1

    2

    3

    4

    5

    6

    7

    8

    9 9 0003

    10

     

    SELECT

    продажи.[Номер позиции],продажи.[Дата транзакции],продажи.[Фрукты],продажи.[Количество]

    ,продажи.[Клиент],продажи.[MOP],продажи.[Номер счета],клиент.[Имя]

    ,клиент.[Год рождения],клиент.[Пол]

    ОТ [selectSIFISOBlogs].[DIM ].[FruitSales] sales

    LEFT JOIN [selectSIFISOBlogs].[DIM].[Customer] cust

    ON sales.[Customer] = cust.[Customer]

    FOR JSON AUTO

     

    9 0152

    Рисунок 6

    Выполнение приведенного выше скрипта приводит к результату, показанному на Рис. 7 . Теперь вы заметите, что в выходных данных появляется еще один дочерний массив (с его собственными объектами), помеченный как cust . Дочерний массив представляет информацию, полученную из измерения клиента.

    Рисунок 7

    Однако если мы изменим порядок столбцов оператора SELECT таким образом, чтобы он начинался со столбца из измерения клиента, как показано на рис.0113 Рисунок 7 тем, что дочерний массив теперь основан на измерении FruitSales.

    1

    2

    3

    4

    5

    6

    7

    8

    9 9 0003

     

    SELECT

    клиент.[Имя],продажи.[Номер позиции],продажи.[Дата транзакции],продажи.[Фрукты]

    ,продажи.[Количество],продажи.[Клиент],продажи.[ MOP],продажи.[номер счета] ,клиент.[дата рождения],клиент.[пол]

    FROM [selectSIFISOBlogs].[DIM].[FruitSales] sales

    LEFT JOIN [selectSIFISOBlogs].[DIM].[Customer] cust

    ON sales.[Customer] = cust.[Customer]

    FOR JSON АВТО

     

    Рисунок 8

    Рисунок 9

    Таким образом, хотя использование режима AUTO удобно, он часто возвращает противоречивые выходные данные, порядок которых может быть изменен в зависимости от порядка столбцов в инструкции SELECT. Чтобы убедиться, что ваши результаты JSON непротиворечивы, вам придется использовать режим PATH.

  2. Преобразование результатов с использованием режима PATH

    Режим PATH можно использовать двумя способами:

    1. Без точечного синтаксиса

    2. С точечным синтаксисом

    Когда вы используете его без точечного синтаксиса, он работает аналогично режиму AUTO, поскольку он генерирует выходные данные JSON на основе порядка столбцов в вашем операторе SELECT.

    1

    2

    3

    4

    5

    6

    7

    8

    9 9 0003

    10

     

    SELECT

    продажи.[Номер позиции],продажи.[Дата транзакции],продажи.[Фрукты],продажи.[Количество]

    ,продажи.[Клиент],продажи.[MOP],продажи.[ Account Number],cust.[Name]

    ,cust.[DOB],cust.[Gender]

    FROM [selectSIFISOBlogs].[DIM]. [FruitSales] sales

    LEFT JOIN [selectSIFISOBlogs].[DIM].[Клиент] cust

    ON sales.[Клиент] = cust.[Клиент]

    FOR JSON PATH

     

    Рисунок 10

    Однако, как видно из Рисунок 11 , режим PATH не группирует автоматически информацию из соединенных таблиц в дочерние массивы. Фактически все столбцы из двух таблиц отображаются на одном и том же корневом уровне.

    Рисунок 11

    Чтобы организовать выходные данные JSON в дочерние массивы, вам придется использовать точечный синтаксис, как показано на Рисунок 12 . Метка перед точкой представляет имя объекта — в этом случае у нас есть два объекта с именами Sales и Cust .

    1

    2

    3

    4

    5

    6

    7

    8

    9 9 0003

    10

    11

    12

    13

     

    SELECT

    продажи. [Номер товара] AS [Номер товара]

    ,продажи.[Дата транзакции] AS [Дата продажи.Транзакции]

    ,продажи.[Фрукты] КАК [Продажи.Фрукты] ,продажи.[Количество] AS [Продажи.Количество]

    ,продажи.[Клиент] AS [Продажи.Клиент],продажи.[MOP] AS [Продажи.MOP]

    ,продажи.[Номер счета] AS [Продажи .Account Number],cust.[Name] AS [Cust.Name]

    ,cust.[DOB] AS [Cust.DOB],cust.[Gender] AS [Cust.Gender]

    FROM [selectSIFISOBlogs].[DIM].[FruitSales] sales

    LEFT JOIN [selectSIFISOBlogs].[DIM].[Customer] cust

    ON sales.[Customer] = cust.[Customer]

    FOR JSON ПУТЬ

     

    Рисунок 12

    Результаты выполнения Рисунок 12 показаны ниже.

    Рисунок 13

    Вы по-прежнему можете организовать выходные данные JSON PATH в дочерние массивы, преобразовав свой сценарий во вложенное соединение, как показано на рисунке 9.0113 Рисунок 14 .

    1

    2

    3

    4

    5

    6

    7

    8

    9 9 0003

    10

    11

    12

    13

     

    ВЫБЕРИТЕ

    (

    ВЫБЕРИТЕ клиент [Имя], клиент [DOB], клиент [Пол]

    ИЗ [selectSIFISOBlogs]. [DIM].[Клиент] клиент

    900 16 ГДЕ заказчик.[Заказчик ] = продажи.[Клиент]

    FOR JSON PATH

    ) cust

    ,продажи.[Номер позиции],продажи.[Дата транзакции],продажи.[Фрукты],продажи.[Количество]

    ,продажи.[Клиент],продажи.[MOP ],продажи.[Номер счета]

    ОТ [selectSIFISOBlogs].[DIM].[FruitSales] продажи

    FOR JSON PATH

     

    Рисунок 14

    Рисунок 15

    Параметры ДЛЯ JSON Пункт

    Оба режима AUTO и PATH позволяют указать дополнительные параметры, такие как КОРЕНЬ , INCLUDE_NULL_VALUES  и WITHOUT_ARRAY_WRAPPER .

    1. КОРЕНЬ

      Параметр ROOT используется для присвоения метки массиву верхнего уровня. На рис. 16 показано применение опции ROOT .

      1

      2

      3

      4

      5

      6

      7

      8

      9

      10

      11

      12

       

      SELECT

      продажи. [Номер позиции] AS [Номер позиции продажи],продажи.[Дата транзакции] КАК [Дата продажи.Транзакции]

      ,продажи.[Фрукты] КАК [Номер позиции продажи],продажи .[Количество] AS [Продажи.Количество]

      ,продажи.[Клиент] КАК [Продажи.Клиент],продажи.[MOP] КАК [Продажи.MOP]

      ,продажи.[Номер счета] КАК [Продажи.Счет] Number],cust.[Name] AS [Cust.Name]

      ,cust.[DOB] AS [Cust.DOB],cust.[Gender] AS [Cust.Gender]

      FROM [selectSIFISOBlogs].[DIM].[FruitSales] sales

      LEFT JOIN [selectSIFISOBlogs].[DIM].[Customer] cust

      ON sales.[Customer] = cust.[Customer]

      FOR JSON ПУТЬ , КОРЕНЬ (‘TOP_LEVEL’)

       

      Рисунок 16

      Как видно из Рисунок 17 , наш основной массив теперь называется TOP_LEVEL .

      Рисунок 17

    2. INCLUDE_NULL_VALUES

      Поведение по умолчанию при использовании предложения FOR JSON заключается в том, что значения NULL не будут включены в ваш вывод JSON. Это можно переопределить, указав параметр INCLUDE_NULL_VALUES . Чтобы проиллюстрировать этот момент, я добавил транзакцию-пустышку в свой оператор SELECT, как показано на Рисунок 18 . Вы заметите, что последние четыре значения в моей фиктивной транзакции равны NULL.

      1

      2

      3

      4

      5

      6

      7

      8

      9 9 0003

       

      SELECT

      продажи.[Номер позиции],продажи.[Дата операции],продажи[Фрукты],продажи.[Количество]

      ,продажи.[Клиент],продажи.[MOP],продажи.[ Номер счета]

      FROM [selectSIFISOBlogs].[DIM].[FruitSales] sales

      UNION

      SELECT 12,209

      ,’Dummy Fruit’,NULL,NULL,NULL,NULL

      ДЛЯ JSON АВТО

      Рисунок 18

      Вывод этого оператора показан на рис. 19 , в котором последние 4 значения, которые были NULL, не включены в вывод JSON.

      Рисунок 19

      Чтобы переопределить это поведение, вам просто нужно включить опцию INCLUDE_NULL_VALUES , как показано на Рисунок 20 .

      1

      2

      3

      4

      5

      6

      7

      8

      9 9 0003

       

      SELECT

      продажи.[Номер позиции],продажи.[Дата операции],продажи[Фрукты],продажи.[Количество]

      ,продажи.[Клиент],продажи.[MOP],продажи.[ Номер счета]

      FROM [selectSIFISOBlogs].[DIM].[FruitSales] sales

      UNION

      SELECT 12,209

      ,’Dummy Fruit’,NULL,NULL,NULL,NULL

      ДЛЯ JSON AUTO, INCLUDE_NULL_VALUES

       

      Рисунок 20

      Как теперь видно из Рисунок 21 , после включения опции INCLUDE_NULL_VALUES наш вывод JSON включает значения NULL.

      Рисунок 21

    3. БЕЗ МАССИВА_ОБОЛОЧКИ

      По умолчанию каждое предложение FOR JSON возвращает данные JSON, заключенные в квадратные скобки, также известные как массив. В некоторых случаях вы хотите, чтобы квадратные скобки были исключены из вывода, потому что вы можете объединить два или более данных JSON. На рис. 22 показано применение опции WITHOUT_ARRAY_WRAPPER .

      1

      2

      3

      4

      5

      6

      7

       

      SELECT

      продажи.[Номер позиции],продажи.[Дата транзакции],продажи.[Фрукты],продажи.[Количество]

      ,продажи.[Клиент],продажи.[MOP],продажи.[ Номер счета]

      ОТ [selectSIFISOBlogs].[DIM].[FruitSales] продаж

      ДЛЯ JSON AUTO, WITHOUT_ARRAY_WRAPPER

       

      Рисунок 22

      Результаты вышеприведенных скриптов показаны ниже.

      Рисунок 23

    Наконец, вы могли заметить, что в Рисунок 22 я использовал предложение TOP, чтобы ограничить свой выбор одной строкой. Это связано с тем, что при использовании параметра WITHOUT_ARRAY_WRAPPER для набора данных, содержащего более одной строки, вы столкнетесь с ошибкой проверки JSON (показанной ниже), вызванной отсутствием квадратных скобок. Таким образом, будьте осторожны в том, как вы используете WITHOUT_ARRAY_WRAPPER , поскольку это может привести к непреднамеренным ошибкам проверки.

    Рисунок 24

    загрузок

    Образец selectSIFISOBlogs база данных

    Артикул

    • INCLUDE_NULL_VALUES Параметр
    • Данные JSON в SQL Server 2016
    • WITHOUT_ARRAY_WRAPPER

    • Автор
    • Recent Posts

    Sifiso Ndlovu

    Сифисо является архитектором данных и техническим руководителем в SELECT SIFISO — технологической консалтинговой фирме, специализирующейся на облачных миграциях, сборе данных, DevOps, отчетности и аналитике. Sifiso более 15 лет работает в частном и государственном бизнес-секторах, помогая предприятиям внедрять решения Microsoft, AWS и технологии с открытым исходным кодом. Он является членом Йоханнесбургской группы пользователей SQL, а также имеет степень магистра в области управления информационными технологиями MCom в Университете Йоханнесбурга.