Форматирование вложенных выходных данных JSON в режиме PATH (SQL Server)

Статья
12/12/2023

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

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

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

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

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

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

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

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

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

Запрос

SELECT TOP 5   
       BusinessEntityID As Id,  
       FirstName, LastName,  
       Title As 'Info.Title',  
       MiddleName As 'Info.MiddleName'  
   FROM Person.Person  
   FOR JSON PATH

Результат

[{
    "Id": 1,
    "FirstName": "Ken",
    "LastName": "Sanchez",
    "Info": {
        "MiddleName": "J"
    }
}, {
    "Id": 2,
    "FirstName": "Terri",
    "LastName": "Duffy",
    "Info": {
        "MiddleName": "Lee"
    }
}, {
    "Id": 3,
    "FirstName": "Roberto",
    "LastName": "Tamburello"
}, {
    "Id": 4,
    "FirstName": "Rob",
    "LastName": "Walters"
}, {
    "Id": 5,
    "FirstName": "Gail",
    "LastName": "Erickson",
    "Info": {
        "Title": "Ms.",
        "MiddleName": "A"
    }
}]

Пример: несколько таблиц

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

Запрос

SELECT TOP 2 H.SalesOrderNumber AS 'Order.Number',  
        H.OrderDate AS 'Order.Date',  
        D.UnitPrice AS 'Product.Price',  
        D.OrderQty AS 'Product.Quantity'  
FROM Sales.SalesOrderHeader H  
   INNER JOIN Sales.SalesOrderDetail D  
     ON H.SalesOrderID = D.SalesOrderID  
FOR JSON PATH

Результат

[{
    "Order": {
        "Number": "SO43659",
        "Date": "2011-05-31T00:00:00"
    },
    "Product": {
        "Price": 2024.9940,
        "Quantity": 1
    }
}, {
    "Order": {
        "Number": "SO43659"
    },
    "Product": {
        "Price": 2024.9940
    }
}]

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

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

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

JSON as a bridge between NoSQL and relational worlds (JSON как мост между NoSQL и реляционными решениями)

См. также

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

Поделиться через