Форматирование результатов запроса в формате 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.

Примечание.

Azure Data Studio — это рекомендуемый редактор запросов JSON, так как он автоматически форматирует результаты JSON, как показано в этой статье. СРЕДА SQL Server Management Studio отображает неформатированную строку.

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

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

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

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

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

ПУТЬ ДЛЯ JSON

Управление выходными данными с помощью FOR JSON PATH

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

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

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

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

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

ДЛЯ JSON AUTO

Оператор 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"
}]

Пример с помощью JOIN и NULL

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

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

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, который выводится предыдущей инструкцией 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 предложения с помощью следующих дополнительных параметров.

• 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.

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

A	B	C	D
10	11	12	X
20	21	22	Y
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 преобразует типы данных 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)