SQL Server 2016“FOR JSON”格式嵌套JSON

问题描述：

我想使用最新版本的SQL Server支持的FOR JSON子句聚合一些嵌套的JSON数据。SQL Server 2016“FOR JSON”格式嵌套JSON

比方说，我写了一个函数，使用该子句导出一段JSON数据。由于不存在JSON类型，因此此函数返回NVARCHAR(MAX)值。

现在假设我想使用这段JSON作为另一个函数的字段的值，该字段通过FOR JSON子句返回JSON数据。

不幸的是我没有办法做到这一点，除了将其存储为字符串。这有效地打破了JSON数据的嵌套。

例如，利用这个功能导出JSON对象的数组，如（直接从微软的例子）：

CREATE FUNCTION GetSalesOrderDetails(@salesOrderId int) 
RETURNS NVARCHAR(MAX) 
AS 
BEGIN 
    RETURN (SELECT UnitPrice, OrderQty 
      FROM Sales.SalesOrderDetail 
      WHERE SalesOrderID = @salesOrderId 
      FOR JSON AUTO) 
END

现在，如果你试图用内的另一个FOR JSON条款SELECT此功能：

SELECT H.*, dbo.GetSalesOrderDetails(H.SalesOrderId) AS Details 
FROM Sales.SalesOrderHeader H 
FOR JSON AUTO

会产生这样的：

[ 
    { 
    "Id": 12, 
    "Details": "[{\"UnitPrice\":12, \"OrderQty\":1}]" 
    } 
]

正如您所看到的，该查询的结果无法一次性解析，但需要第二次解析Details字段。

有没有办法告诉FOR JSON子句将字段解释为JSON数组或JSON对象？

答

内置函数JSON_QUERY从JSON字符串中提取对象或数组。

此查询应该有效。

SELECT 
    H.*, 
    JSON_QUERY(dbo.GetSalesOrderDetails(H.SalesOrderId)) AS Details 
FROM Sales.SalesOrderHeader H 
FOR JSON AUTO

不幸的是，'JSON_QUERY'函数的返回值仍然'NVARCHAR（MAX）'，所以外'FOR JSON AUTO'子句仍然将其解释为在SQL字符串 – Francesco

JSON功能服务器对字符串（varchar或nvarchar）进行操作并返回，无论该字符串是否为格式正确的JSON字符串，都将决定FOR JSON AUTO如何解析它。看看这段*防止在JSON输出* FOR FOR JSON输出中的双转义JSON https://msdn.microsoft.com/en-us/library/mt631706.aspx – Mono

我很抱歉，我重新检查了我所做的测试昨天，事实上，你的解决方案的作品！我不知道我是如何错过的！顺便说一下，JSON_QUERY存在的每个级别的引擎是否都解析和字符串JSON？这可能会对业绩造成相当大的影响？ – Francesco

SQL Server 2016“FOR JSON”格式嵌套JSON

相关推荐