Форматирование результатов запроса в формате JSON с помощью FOR JSON

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

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

Форматирование результатов запроса

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

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

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

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

SELECT name, surname
FROM emp
FOR JSON AUTO;

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

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 JOIN #tabStudent AS s ON s.ClassGuid = c.ClassGuid
ORDER BY c.ClassName,
    s.StudentName
FOR JSON AUTO
    -- To include NULL values in the output, uncomment the following line:
    --, INCLUDE_NULL_VALUES
    ;
GO

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

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"}]}
]

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

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

• ROOT

  Чтобы добавить один элемент верхнего уровня в выходные данные JSON, укажите ROOT этот параметр. Если не указать этот параметр, выходные данные JSON не будут поддерживать корневой элемент. Дополнительные сведения см. в разделе "Добавление корневого узла в выходные данные JSON" с параметром ROOT (SQL Server).

• INCLUDE_NULL_VALUES

  Чтобы включить значения NULL в выходные данные JSON, укажите INCLUDE_NULL_VALUES параметр. Если этот параметр не указан, выходные данные не включают свойства JSON для NULL значений в результатах запроса. Дополнительные сведения см. в разделе "Включение значений NULL" в JSON — параметр INCLUDE_NULL_VALUES.

• WITHOUT_ARRAY_WRAPPER

  Чтобы удалить квадратные скобки, которые окружают выходные данные FOR JSON JSON предложения по умолчанию, укажите WITHOUT_ARRAY_WRAPPER этот параметр. Используйте этот параметр, чтобы создать единый объект JSON в качестве выходных данных из одной строки результата. Если этот параметр не указан, выходные данные JSON форматируются в виде массива, то есть выходные данные заключены в квадратные скобки. Дополнительные сведения см. в разделе "Удалить квадратные скобки" из JSON — параметр WITHOUT_ARRAY_WRAPPER.

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

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

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

   • Небольшой результирующий набор может содержать одну строку.
   • Большой результирующий набор разбивает длинную строку JSON по нескольким строкам.

     • По умолчанию SQL Server Management Studio (SSMS) сцепляет результаты в одну строку, если выходной параметр В виде сетки. В строке состояния SSMS отображается действительное число строк.

     • Другим клиентским приложениям может потребоваться код для повторного объединения длинных результатов в одну допустимую строку JSON путем объединения содержимого нескольких строк. Пример этого кода в приложении C# см. в разделе Использование выходных данных FOR JSON в клиентском приложении C#.

       

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

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

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

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

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

Пример

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

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

Выходные данные 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 преобразует типы данных SQL Server в типы данных JSON (SQL Server)
• Как FOR JSON экранирует специальные символы и управляющие символы (SQL Server)
• JSON as a bridge between NoSQL and relational worlds (JSON как мост между NoSQL и реляционными решениями)
• SELECT — предложение FOR (Transact-SQL)
• Использование выходных данных FOR JSON в SQL Server и клиентских приложениях (SQL Server)