在T-SQL查询中获取表格以及json列 [英] Get tabular as well as json column in a T-SQL query

问题描述

在SQL Server 2016中，FOR JSON PATH允许将整个结果集作为JSON字符串返回.有没有一种方法来获取带有某些列的常规记录集作为JSON?

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?

例如对于一个产品(主)和订单(明细)表，当连接在一起时，我希望查询以常规表格列的形式返回Products表的结果集，但是Orders表中每个产品的那些行作为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.

直到现在，我一直使用用户自定义的标量函数来执行此操作，产品ID传递给该函数，并且它返回了JSON格式的Orders数据，但是我希望是否有一种更简洁的方法.

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.

推荐答案

是.像这样:

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)

这篇关于在T-SQL查询中获取表格以及json列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！