CODEWITHSUNDEEP
sql-serverjsont-sqlsql-server-2016
Aamir
Aamir

Reputation: 821

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

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.

Upvotes: 0

Views: 308

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89091

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)

Upvotes: 2

Related Questions