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

Question

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

当我在不向查询中添加 FOR JSON PATH 的情况下执行该过程时，我得到 244 行(我的表中没有记录)；但是当我通过添加 FOR JSON PATH 执行该过程时，我收到了第 33 行消息，并且我还收到了被截断的 JSON 对象。

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

这是我的问题

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 中获取输出

Answer 1

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

在 SSMS 中很难正确地看到这一点，因为 SSMS 会在“Results to Grid”中为您连接结果，并在“Results to Text”中截断每一行。

为什么？不知道。我的猜测是，只有 .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());
            }



        }
    }
}