FOR JSON 路径在 AZURE SQL 上返回较少数量的行

我正在使用 AZURE SQL (SQL Server 2016) 并创建一个查询以提供 JSON 对象中的输出。我正在添加FOR JSON PATH在查询结束时。

当我执行该过程而不添加FOR JSON PATH对于查询，我得到 244 行（表中没有记录）；但是当我通过添加执行该过程时FOR JSON PATH我收到消息 33 行，并且还收到被截断的 JSON 对象。

我使用不同类型的查询对此进行了测试，包括仅选择 10 列的简单查询，但我总是得到较少的行数FOR JSON PATHJSON 对象在末尾被截断。

这是我的查询

SELECT 
    [Id]
    ,[countryCode]
    ,[CountryName]
    ,[FIPS]
    ,[ISO1]
    ,[ISO2]
    ,[ISONo]
    ,[capital]
    ,[region]
    ,[currency]
    ,[currencyCode]
    ,[population]
    ,[timeZone]
    ,[timeZoneCode]
    ,[ISDCode]
    ,[currencySymbol]
FROM 
    [dbo].[countryDB]

上面的查询返回 2 行。

我使用以下查询来获取 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 行，输出为

[{"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 格式获取输出

当 FOR JSON 查询返回到客户端时，JSON 文本将作为单列结果集返回。 JSON 被分解为固定长度的字符串并通过多行发送。

在 SSMS 中很难正确地看到这一点，因为 SSMS 在“结果到网格”中为您连接结果，并截断“结果到文本”中的每一行。

为什么？不知道。我的猜测是，只有 .NET 客户端知道如何有效地从 SQL Server 读取大型流，并且 99% 的时间用户仍然只是缓冲整个对象。将 JSON 分解为多行为客户端提供了一个简单的 API 来增量读取数据。在 .NET 中，事实上的标准 JSON 库不在 BCL 中，这意味着 SqlClient 无法真正拥有一流的 JSON API。

无论如何，在 C# 中，您可以使用类似这样的方法来读取结果：

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());
            }



        }
    }
}