FOR JSON path returns less number of Rows on AZURE SQL

Question

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.

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.

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.

Here is my query

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

Above query returns 2 rows.

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

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":

I am trying to get output directly in JSON

score 10 · Answer 1 · answered May 13 '17 at 10:30

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.

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".

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.

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



        }
    }
}

Thanks for your solution, I was looking for if there is an easy way to handle it at SQL Server level — NodeJSNewbi, May 13 '17 at 18:16
Oh, that's easy. Just assign the results of a FOR JSON query to a nvarchar(max) variable. eg declare @json nvarchar(max) = (select * from sys.objects for json auto) — David Browne - Microsoft, May 13 '17 at 21:42
Thanks David , it worked perfectly the way i wanted... 101% points to you — NodeJSNewbi, May 14 '17 at 13:59
Please tell me how do I get the result using @DavidBrowne-Microsoft solution. I just get a text message as `Commands completed successfully.`? But where is the JSON output? — Unbreakable, Feb 18 '19 at 05:22
No way to tell without seeing your code. So that probably needs to be a new question. — David Browne - Microsoft, Feb 18 '19 at 14:49

score 1 · Answer 2 · edited Oct 20 '19 at 03:01

1

With thanks to @David Browne. I found I had to use 'print' instead of 'select'

declare @json varchar(max) = (SELECT * FROM dbo.AppSettings FOR JSON AUTO)

print @json

edited Oct 20 '19 at 03:01

Marcello Miorelli

3,368
4
44
67

answered Mar 19 '19 at 14:37

justSteve

5,444
19
72
137

score 0 · Answer 3 · answered Apr 09 '18 at 22:40

Separation of Concerns dictates returning a string and parsing the JSON separately. The below snippet can be used with no dependency on JSON.net which can be be used separately or a different Json Deserializer can be used (e.g. the one built into RestSharp) and does not require the SqlJSONReader class.

try {
     using (var conn = new SqlConnection(connectionString))
     using (var cmd = new SqlCommand(sql, conn)) {
        await conn.OpenAsync();
        logger.LogInformation("SQL:" + sql);
        var rdr = await cmd.ExecuteReaderAsync().ConfigureAwait(false);
        var result = "";
        var moreRows = rdr.HasRows;
        while (moreRows) {
            moreRows = await rdr.ReadAsync();
            if (moreRows) result += rdr.GetString(0);
        }
        return result;
     }
  }
  catch (Exception ex) {
     //logger.LogError($"Error accessing Db:{ex}");
     return null;
  }

The concerns are fully separated in the sample I posted. The SqlJSONReader is a TextReader (a streaming view of a String). If you return a String you've copied all the data into memory, which defeats the purpose of streaming the data to the client. — David Browne - Microsoft, Apr 23 '18 at 16:56

score 0 · Answer 4 · answered Jul 19 '18 at 21:40

sql Query result of for json path is a long string that is divided to multi column or block something like this statement: "i want to get result of for json path"

"i want"+
" to ge"+
"t resu"+
"lt of "+
"for js"+
"on path"

each block is equal to max size of column in sql so just get them all to a list

    public IHttpActionResult GetAdvertises()
    {

        var rEQUEST = db.Database.SqlQuery<string>("SELECT 
        ID,CITY_NAME,JSON_QUERY(ALBUM) AS ALBUM FOR JSON PATH").ToList();
        foreach(string req in rEQUEST)
        {
        HttpContext.Current.Response.Write(req);
        }

        return Ok();
    }

score 0 · Answer 5 · answered May 23 '19 at 06:06

If your query returned more then 2033 charters then there will be rows. Each row contains 2033 charters data another row contains remaining data. So you need to merge to get actual json. As seen below code sample.

dynamic jsonReturned = unitOfWork
        .Database
        .FetchProc<string>("storedProcedureGetSaleData", new { ProductId = productId });

    if (Enumerable.Count(jsonReturned) == 0)
    {
        return null;
    }

    dynamic combinedJson = "";
    foreach (var resultJsonRow in jsonReturned)
    {
        combinedJson += resultJsonRow;
    }

    return combinedJsonResult;

FOR JSON path returns less number of Rows on AZURE SQL

5 Answers5

Linked

Related