Get tabular as well as json column in a T-SQL quer

问题:

In SQL Server 2016, FOR JSON PATH allows for the whole result set returned as a JSON string. Is there a way to get a regular record set with some columns as JSON?

For e.g. for a Products (Master) and Orders (Details) table when joined together, I would like the query to return the result set from the Products table as regular tabular columns, but those multiple rows for each product from the Orders table returned as a JSON column.

Until now I have been doing this using a user-defined scalar function to which the product ID was passed and it returned JSON formatted Orders data, but I was hoping if there was a cleaner way of doing it.

回答1:

Yes. Something like:

select SalesOrderId, OrderDate, TotalDue, d.Details
from SalesLT.SalesOrderHeader h
cross apply 
(
    select * 
    from SalesLT.SalesOrderDetail d
    where d.SalesOrderId = h.SalesOrderId
    for json path
)  d(details)