SQL Server JSON结果分成多行 [英] Sql Server JSON result separated in multiple rows

问题描述

我想知道这是否是SQL Server的正常行为(在Azure上). 我的SQL查询使用 FOR JSON PATH 和嵌套查询返回JSON. 我的问题是，似乎，根据生成的JSON的长度，结果会分为多行.

I would like to know if this is normal behavior of SQL Server (on Azure). My SQL query returns JSON using FOR JSON PATH and nested queries. My problem is that it seems that depending on the length of the resulting JSON, the result is separated into multiple rows.

这是一个问题，因为行数可能会根据数据而变化.我还必须手动将所有行合并在一起，以获得有效的JSON.

This is a problem since the number of rows might change depending on the data. I would also have to concat all the rows together manually to have a valid JSON.

一种解决方法是将生成的JSON放入变量中，然后手动选择它.这样就形成了一个单一的行和列(所以我知道这不是最大长度的问题).

A workaround is to put the resulting JSON into a variable and select it after hand. This results in a single row and column (so I know it is not a max length issue).

这是正常现象还是我的查询有问题?

Is this normal behavior or is there a problem with my query?

推荐答案

Sql Server将FOR JSON查询的结果拆分为〜2KB的块，因此您应该像

Sql Server splits result of FOR JSON query into ~2KB chunk, so you should either concatenate fragments like on the MSDN page or you can stream results into some output stream.

在这里您可以找到ASP.NET Core REST API实现，其中带有FOR JSON的SQL查询的结果将流式传输到响应主体中:

Here you can find ASP.NET Core REST API implementation where results of the SQL query with FOR JSON is streamed into response body: https://www.codeproject.com/Articles/1106622/Building-REST-services-with-ASP-NET-Core-Web-API-a

这篇关于SQL Server JSON结果分成多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！