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

Question

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.

David Browne - Microsoft · Accepted Answer · 2017-06-30 04:04:31Z

2

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)

answered Jun 30, 2017 at 4:04

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aamir Over a year ago

Thank you! Never thought of CROSS APPLY. I wonder if the execution plan is efficient than the inline scalar user-defined function I have been using. If nothing else, your solution helps me avoid creating UDFs in the database if there is only one time use for them.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related