Форматирование результатов запроса как JSON с помощью предложения FOR JSON (SQL Server)

Применимо к: даSQL Server 2016 (13.x); и более поздние версии

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

При использовании предложения FOR JSON вы можете в явном виде указать структуру выходных данных JSON или дать определить ее структуре инструкции SELECT.

  • Чтобы сохранить полный контроль над форматом выходных данных JSON, используйте FOR JSON PATH. Вы можете создавать объекты-оболочки и вкладывать сложные свойства друг в друга.

  • Используйте FOR JSON AUTO, чтобы отформатировать выходные данные JSON автоматически на основе структуры инструкции SELECT.

Ниже приведен пример инструкции SELECT с предложением FOR JSON и ее выходные данные.

FOR JSON

Вариант 1. Вы управляете выходными данными с помощью FOR JSON PATH

В режиме PATH можно использовать синтаксис с точкой — например, 'Item.UnitPrice' — для форматирования вложенных выходных данных.

Ниже приведен пример запроса, где режим PATH с предложением FOR JSON . В следующем примере также используется параметр ROOT для указания именованного корневого элемента.

Схема потока выходных данных FOR JSON

Дополнительные сведения о FOR JSON PATH

Более подробные сведения и примеры см. в разделе Форматирование вложенных выходных данных JSON с помощью режима PATH (SQL Server).

Сведения о синтаксисе и использовании см. в разделе Предложение FOR (Transact-SQL).

Вариант 2. Инструкция SELECT управляет выходными данными с помощью FOR JSON AUTO

В режиме AUTO структура инструкции SELECT определяет формат выходных данных JSON.

По умолчанию значения NULL не включаются в выходные данные. Это поведение можно изменить с помощью INCLUDE_NULL_VALUES .

Ниже приведен пример запроса, где режим AUTO используется с предложением FOR JSON .

SELECT name, surname  
FROM emp  
FOR JSON AUTO;

А вот возвращаемый JSON.

[{
    "name": "John"
}, {
    "name": "Jane",
    "surname": "Doe"
}]

2.b — пример с JOIN и NULL

В следующем примере SELECT...FOR JSON AUTO включает результаты JSON при соотношении связи "один ко многим" между данными из таблиц JOIN.

Отсутствие значения NULL в возвращаемом JSON также показано. Тем не менее можно переопределить это поведение по умолчанию с помощью ключевого слова INCLUDE_NULL_VALUES в предложении FOR.

go

DROP TABLE IF EXISTS #tabStudent;
DROP TABLE IF EXISTS #tabClass;

go

CREATE TABLE #tabClass
(
   ClassGuid   uniqueIdentifier  not null  default newid(),
   ClassName   nvarchar(32)      not null
);

CREATE TABLE #tabStudent
(
   StudentGuid   uniqueIdentifier  not null  default newid(),
   StudentName   nvarchar(32)      not null,
   ClassGuid     uniqueIdentifier      null   -- Foreign key.
);

go

INSERT INTO #tabClass
      (ClassGuid, ClassName)
   VALUES
      ('DE807673-ECFC-4850-930D-A86F921DE438', 'Algebra Math'),
      ('C55C6819-E744-4797-AC56-FF8A729A7F5C', 'Calculus Math'),
      ('98509D36-A2C8-4A65-A310-E744F5621C83', 'Art Painting')
;

INSERT INTO #tabStudent
      (StudentName, ClassGuid)
   VALUES
      ('Alice Apple', 'DE807673-ECFC-4850-930D-A86F921DE438'),
      ('Alice Apple', 'C55C6819-E744-4797-AC56-FF8A729A7F5C'),
      ('Betty Boot' , 'C55C6819-E744-4797-AC56-FF8A729A7F5C'),
      ('Betty Boot' , '98509D36-A2C8-4A65-A310-E744F5621C83'),
      ('Carla Cap'  , null)
;

go

SELECT
      c.ClassName,
      s.StudentName
   from
                       #tabClass   as c
      RIGHT OUTER JOIN #tabStudent as s ON s.ClassGuid = c.ClassGuid
   --where
   --   c.ClassName LIKE '%Math%'
   order by
      c.ClassName,
      s.StudentName
   FOR
      JSON AUTO
      --, INCLUDE_NULL_VALUES
;

go

DROP TABLE IF EXISTS #tabStudent;
DROP TABLE IF EXISTS #tabClass;

go

Вот JSON, который выводится предыдущей инструкцией SELECT.

JSON_F52E2B61-18A1-11d1-B105-00805F49916B

[
   {"s":[{"StudentName":"Carla Cap"}]},
   {"ClassName":"Algebra Math","s":[{"StudentName":"Alice Apple"}]},
   {"ClassName":"Art Painting","s":[{"StudentName":"Betty Boot"}]},
   {"ClassName":"Calculus Math","s":[{"StudentName":"Alice Apple"},{"StudentName":"Betty Boot"}]}
]

Дополнительные сведения о FOR JSON AUTO

Более подробные сведения и примеры см. в разделе Автоматическое форматирование выходных данных JSON с помощью режима AUTO (SQL Server).

Сведения о синтаксисе и использовании см. в разделе Предложение FOR (Transact-SQL).

Управление другими параметрами выходных данных JSON

Управление выходными данными из FOR JSON предложения, используя следующие дополнительные параметры.

Выходные данные предложения FOR JSON

Выходные данные предложения FOR JSON имеют следующие характеристики:

  1. Результирующий набор содержит один столбец.

    • Небольшой результирующий набор может содержать одну строку.
    • Большой результирующий набор разбивает длинную строку JSON по нескольким строкам.
      • По умолчанию SQL Server Management Studio (SSMS) сцепляет результаты в одну строку, если выходной параметр В виде сетки. В строке состояния SSMS отображается действительное число строк.
      • В других клиентских приложениях может потребоваться внести код, который будет перераспределять большой объем результатов в одну общую допустимую строку JSON, объединяя содержимое множества записей. Пример этого кода в приложении C# см. в разделе Использование выходных данных FOR JSON в клиентском приложении C#.

    Пример выходных данных FOR JSON

  2. Результаты форматируются в виде массива объектов JSON.

    • Число элементов в массиве JSON равно числу строк в результатах инструкции SELECT (до применения предложения FOR JSON).

    • Каждая строка в результатах инструкции SELECT (до применения предложения FOR JSON) становится отдельным объектом JSON в массиве.

    • Каждый столбец в результатах инструкции SELECT (до применения предложения FOR JSON) становится свойством объекта JSON.

  3. Как имена столбцов, так и их значения экранируются согласно синтаксису JSON. Дополнительные сведения см. в разделе Как FOR JSON экранирует специальные и управляющие символы (SQL Server).

Пример

Приведенный ниже пример показывает, каким образом предложение FOR JSON форматирует выходные данные JSON.

Результаты запроса

Объект B C D
10 11 12 X
20 21 22 Да
30 31 32 Z
       

Выходные данные JSON

[{
    "A": 10,
    "B": 11,
    "C": 12,
    "D": "X"
}, {
    "A": 20,
    "B": 21,
    "C": 22,
    "D": "Y"
}, {
    "A": 30,
    "B": 31,
    "C": 32,
    "D": "Z"
}] 

Дополнительные сведения о том, какое содержимое отображается в выходных данных предложения FOR JSON, см. в следующих статьях.

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

Видео Майкрософт

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

См. также:

Предложение FOR (Transact-SQL)
Использование выходных данных FOR JSON в SQL Server и клиентских приложениях (SQL Server)