FOR JSON path returns less number of Rows on AZURE SQL

user_4743543 0
Viewed 3333

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

azure-sql-database sql-server-2016 sql
5 Answers

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
user_1181100

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

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;
user_65228290
Related