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

Применимо к: SQL Server 2016 (13.x) и более поздним версиямAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics (только бессерверный пул SQL)SQL аналитическая точка в Microsoft FabricWarehouse в Microsoft Fabricбаза данных SQL в Предварительном просмотре Microsoft Fabric

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

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

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

При использовании 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.

• INCLUDE_NULL_VALUES

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

• WITHOUT_ARRAY_WRAPPER

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

Example

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

Query results

JSON output

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

Related content

• Как FOR JSON преобразует типы данных SQL Server в типы данных JSON
• Как FOR JSON экранирует специальные символы и управляющие символы
• Видео: JSON в качестве моста между NoSQL и реляционными мирами
• SELECT — предложение FOR (Transact-SQL)
• Использование выходных данных FOR JSON в ядре СУБД SQL и клиентских приложениях