FOR JSON路径在AZURE SQL上返回的行数较少 [英] FOR JSON path returns less number of Rows on AZURE SQL
问题描述
我正在使用AZURE SQL(SQL Server 2016)并创建一个查询以在JSON对象中给我输出.我在查询末尾添加FOR JSON PATH.
I am using AZURE SQL (SQL Server 2016) and creating a query to give me output in JSON object. I am adding FOR JSON PATH at the end of query.
在不向查询中添加FOR JSON PATH的情况下执行该过程时，将获得244行(表中没有记录)；但是当我通过添加FOR JSON PATH执行该过程时，我得到了33行消息，并且还得到了被截断的JSON对象.
When I execute the procedure without adding FOR JSON PATH to the query, I get 244 rows (no of records in my table); but when I execute the procedure by adding FOR JSON PATH I get message 33 rows and also I get JSON object which is truncated.
我用不同类型的查询进行了测试，包括仅选择10列的简单查询，但是总是得到较少的行，并且FOR JSON PATH和结尾的JSON对象被截断.
I tested this with different types of queries including simple query selecting only 10 columns, but I always get less number of rows with FOR JSON PATH and JSON object truncated at the end.
这是我的查询
SELECT
[Id]
,[countryCode]
,[CountryName]
,[FIPS]
,[ISO1]
,[ISO2]
,[ISONo]
,[capital]
,[region]
,[currency]
,[currencyCode]
,[population]
,[timeZone]
,[timeZoneCode]
,[ISDCode]
,[currencySymbol]
FROM
[dbo].[countryDB]
以上查询返回2行.
然后我使用以下查询来获取JSON中的输出
And I use following query to get output in JSON
SELECT
[Id]
,[countryCode]
,[CountryName]
,[FIPS]
,[ISO1]
,[ISO2]
,[ISONo]
,[capital]
,[region]
,[currency]
,[currencyCode]
,[population]
,[timeZone]
,[timeZoneCode]
,[ISDCode]
,[currencySymbol]
FROM
[dbo].[countryDB]
FOR JSON PATH
以上查询返回33行，输出为
Above query returns 33 rows and output is
[{"Id":1,"countryCode":"AD","CountryName":"Andorra","FIPS":"AN","ISO1":"AD","ISO2":"AND","ISONo":20,"capital":"Andorra la Vella","region":"Europe","currency":"Euro","currencyCode":"EUR","population":67627,"timeZone":2.00,"timeZoneCode":"DST","ISDCode":"+376"},{"Id":2,"countryCode":"AE","CountryName":"United Arab Emirates","FIPS":"AE","ISO1":"AE","ISO2":"ARE","ISONo":784,"capital":"Abu Dhabi","region":"Middle East","currency":"UAE Dirham","currencyCode":"AED","population":2407460,"timeZone":4.00,"timeZoneCode":"STD","ISDCode":"+971"},{"Id":3,"countryCode":"AF","CountryName":"Afghanistan","FIPS":"AF","ISO1":"AF","ISO2":"AFG","ISONo":4,"capital":"Kabul","region":"Asia","currency":"Afghani","currencyCode":"AFA","population":26813057,"timeZone":4.50,"timeZoneCode":"STD","ISDCode":"+93"},{"Id":4,"countryCode":"AG","CountryName":"Antigua and Barbuda","FIPS":"AC","ISO1":"AG","ISO2":"ATG","ISONo":28,"capital":"Saint Johns","region":"Central America and the Caribbean","currency":"East Caribbean Dollar","currencyCode":"205","population":66970,"timeZone":-4.00,"timeZoneCode":"STD","ISDCode":"+1"},{"Id":5,"countryCode":"AI","CountryName":"Anguilla","FIPS":"AV","ISO1":"AI","ISO2":"AIA","ISONo":660,"capital":"The Valley","region":"Central America and the Caribbean","currency":"East Caribbean Dollar","currencyCode":"205","population":12132,"timeZone":-4.00,"timeZoneCode":"STD","ISDCode":"+1"},{"Id":6,"countryCode":"AL","CountryName":"Albania","FIPS":"AL","ISO1":"AL","ISO2":"ALB","ISONo":8,"capital":"Tirana","region":"Europe","currency":"Lek","currencyCode":"ALL","population":3510484,"timeZone":2.00,"timeZoneCode":"DST","ISDCode":"+355"},{"Id":7,"countryCode":"AM","CountryName":"Armenia","FIPS":"AM","ISO1":"AM","ISO2":"ARM","ISONo":51,"capital":"Yerevan","region":"Commonwealth of Independent States","currency":"Armenian Dram","currencyCode":"AMD","population":3336100,"timeZone":5.00,"timeZoneCode":"DST","ISDCode":"+374"},{"Id":8,"countryCode":"AN","CountryName":"Netherlands Antilles","FIPS":"NT","ISO1":"AN","ISO2":
我正在尝试直接在JSON中获取输出
I am trying to get output directly in JSON
推荐答案
将FOR JSON查询返回给客户端时，JSON文本作为单列结果集返回. JSON分为固定长度的字符串，并通过多行发送.
When FOR JSON queries are returned to the client, the JSON text is returned as a single-column result set. The JSON is broken into fixed-length strings and sent over multiple rows.
在SSMS中很难正确地看到这一点，因为SSMS在"Results to Grid"中为您合并结果，并在"Results to Text"中将每一行截断.
It's really hard to see this properly in SSMS, as SSMS concatenates the results for you in "Results to Grid", and truncates each row in "Results to Text".
为什么?不知道.我的猜测是，只有.NET客户端才知道如何有效地从SQL Server读取大型流，并且99％的时间用户仍然会缓冲整个对象.将JSON分解为多行可为客户端提供一个简单的API，以逐步读取数据.在.NET中，事实上的标准JSON库不在BCL中这一事实意味着SqlClient不能真正拥有一流的JSON API.
Why? Dunno. My guess is that only .NET clients know how to efficiently read large streams from SQL Server, and 99% of the time users will still just buffer the whole object. Breaking the JSON over multiple rows gives clients a simple API to read the data incrementally. And in .NET the fact that the de facto standard JSON library is not in the BCL means that SqlClient can't really have a first-class JSON API.
无论如何，从C#中，您可以使用类似以下内容来读取结果:
Anyway, from C#, you can use something like this to read the results:
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
namespace ConsoleApp3
{
class SqlJSONReader: TextReader
{
SqlDataReader rdr;
string currentLine = "";
int currentPos = 0;
public SqlJSONReader(SqlDataReader rdr)
{
this.rdr = rdr;
}
public override int Peek()
{
return GetChar(false);
}
public override int Read()
{
return GetChar(true);
}
public int GetChar(bool Advance)
{
while (currentLine.Length == currentPos)
{
if (!rdr.Read())
{
return -1;
}
currentLine = rdr.GetString(0);
currentPos = 0;
}
int rv = (int)currentLine[currentPos];
if (Advance) currentPos += 1;
return rv;
}
public override void Close()
{
rdr.Close();
}
}
class Program
{
static void Main(string[] args)
{
using (var con = new SqlConnection("server=.;database=master;Integrated Security=true"))
{
con.Open();
var sql = @"
select o.object_id as [obj.Id], replicate('n', 2000) as [obj.foo], c.name as [obj.col.name]
from sys.objects o
join sys.columns c
on c.object_id = o.object_id
for json path;
"
;
var cmd = new SqlCommand(sql, con);
var sr = new StringBuilder();
using (var rdr = cmd.ExecuteReader())
{
using (var tr = new SqlJSONReader(rdr))
{
using (var jr = new Newtonsoft.Json.JsonTextReader(tr))
{
while (jr.Read())
{
Console.WriteLine($" {jr.TokenType} : {jr.Value}");
}
}
}
}
Console.WriteLine(sr.ToString());
}
}
}
}
这篇关于FOR JSON路径在AZURE SQL上返回的行数较少的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
