Practical .NET

Returning JSON from SQL Server Queries

With JSON now the default format for moving data between clients and servers, SQL Server adds JSON support to make it easier to get your data out of the database and down to the client.

• By Peter Vogel
• 08/24/2017

SQL Server 2016 adds new support for the JSON format when retrieving rows from a database table. Because most Web Services now return data in the JSON format, this support can simplify those Action methods in your controllers that are the targets of AJAX calls from clients. Effectively, those methods can become just thin wrappers around SQL queries.

If you want to play with SQL Server’s JSON support (or the other big new feature in SQL Server 2016, temporal tables), you can download the Developer edition of SQL Server 2016.

JSON support consists of keywords and functions you can incorporate into your queries. Unlike the XML support in SQL Server, JSON support does not include a JSON data type -- you just store JSON objects in a character column (probably defined as VarChar(max)). However, even without a JSON datatype, SQL Server 2016 still lets you query data stored in JSON format. I’ll look at that support in a later column (among other features, it provides a way to recompose a JSON object into a row in a table). In this column, I’ll concentrate on SQL Server’s ability to retrieve rows in a JSON format.

Some Caveats
Before getting excited about this feature, there are at least three caveats of which you should be aware:

• There is, as yet, no support in Entity Framework or LINQ to leverage the TSQL JSON functionality -- accessing JSON support means using plain old ADO.NET. It’s possible that Entity Framework Core, which supports more configuration options than Entity Framework, may support this feature . . . someday.
• Without LINQ, there’s no benefit in declaring a method’s return type as IQueryable to allow the client to use OData to integrate a query with your Action method’s query.
• If you’re working with ASP.NET Web API and use SQL Server JSON support, then you’ll have to give up on content negotiation -- the results of your method will always return JSON.

Returning JSON Results
Listing 1 shows a simple Action method using ADO.NET to submit a SQL statement to SQL Server 2016, retrieving a JSON result and returning it to the client that called the method. The FOR JSON AUTO keywords at the end of the SQL statement causes SQL Server to return JSON rather than a rowset.

Public Function GetJson() As String
  Dim cn As SqlConnection
  Dim cmd As SqlCommand
  Dim rdr As SqlDataReader
  Dim res As String = String.Empty

  cn = New SqlConnection("…connection string…")
  cmd = cn.CreateCommand()
  cmd.CommandText = "Select * " &
     "  From Customers " &
     "FOR JSON AUTO;"
  cn.Open()
  rdr = cmd.ExecuteReader()
  rdr.Read()
  res = rdr(0)
  cn.Close()

  Return res
End Function

The result would look something like this for a Customers table containing two rows (I’ve inserted carriage returns, line returns and leading spaces to improve readability):

[{""Id"":1,""FirstName"":""Peter"",""LastName"":""Vogel"",""BirthDate"":""1953-05-31""},
 {""Id"":2,""FirstName"":""Jan"",""LastName"":""Vogel"",""BirthDate"":""1955-04-08""}]"

One of the first things to notice is that a single read using FOR JSON AUTO returns all the selected rows as an array of JSON objects. If your query returns a single row, it will still be returned as an array.

If you’re returning a single row, then you can eliminate the array wrapper by adding the Without_Array_Wrapper keyword after FOR JSON AUTO (the comma you see in this example is required):

Select * 
  From Customers 
Where Id = 1 
FOR JSON AUTO, Without_Array_Wrapper;

The result of that SQL statement would look something like this:

"{""Id"":1,""FirstName"":""Peter"",""LastName"":""Vogel"",""BirthDate"":""1953-05-31""}"

You won’t get a runtime error if you return multiple rows using Without_Array_Wrapper, but the result probably won’t be acceptable to any client, either.

You can assign a name to the JSON object enclosing your result by using Root after FOR JSON AUTO. This example returns multiple customers inside an object called Customers:

Select * 
  From Customers 
Where Id = 1 
FOR JSON AUTO, Root('Customers')

The result would look something like this (as before, I’ve inserted line breaks and tabs to make this more readable):

"{""Customers"":[
{""Id"":1,""FirstName"":""Peter"",""LastName"":""Vogel"",""BirthDate"":""1953-05-31""},
{""Id"":2,""FirstName"":"""",""LastName"":""Vogel"",""BirthDate"":""1955-04-08""}
               ]
}""

Formatting Columns
You’ll need to pay attention to how FOR JSON AUTO handles empty columns and columns with NULL. In this example, the FirstName column contains a zero-length string so the column is included in the result:

"[{""Id"":2,""FirstName"":"""",""LastName"":""Vogel"",""BirthDate"":""1955-04-08""}]"

In the following example, however, the FirstName column was set to NULL. As you can see, the column is omitted from the JSON result:

"[{""Id"":3,""LastName"":""van de Velde"",""BirthDate"":""1981-12-23""}]"

If you’d prefer to include columns even if they have NULL values (and, in most cases, I suspect you will), you can add the Include_Null_Values option after FOR JSON AUTO, like this:

Select * 
  From Customers 
FOR JSON AUTO, Include_Null_Values

With this option, a column with NULL in it will be presented, but will contain the text "null" as its value:

"[{""Id"":3,""FirstName"":null,""LastName"":""van de Velde"",""BirthDate"":""1981-12-23""}]"

Naming and Nesting Results
You can control the names of any of the columns used in your JSON result by applying aliases in your SQL statement. This query, for example, assigns the alias FName to the FirstName column:

Select FirstName As FName 
  From Customers
Where Id = 1 
FOR JSON AUTO

The JSON result reflects the alias:

"[{""FName"":""Peter""}]"

You’re not limited to retrieving from a single table. With FOR JSON AUTO you can also retrieve rows from related tables and those related tables will appear as nested objects. This SQL statement, for example, joins the Customer and SalesOrder table:

Select * 
  From Customers As C JOIN 
       SalesOrders As S 
    On C.Id = S.CustId 
Where C.Id = 1 
FOR JSON AUTO

The result nests two SalesOrder JSON objects inside the Customer JSON object, with the SalesOrder objects identified using the S alias from my SQL statement (formatted for readability):

"[{""Id"":1,""FirstName"":""Peter"",""LastName"":""Vogel"",""BirthDate"":""1953-05-31"",
    ""S"":[
    {""Id"":1,""OrderDate"":""2017-10-12"",""CustId"":""1""},
    {""Id"":2,""OrderDate"":""2017-09-09"",""CustId"":""1""}
   ]
   }
]"

As you can see, it might be a good idea to give meaningful aliases to your tables if you’re generating JSON results.

You can also force nested objects into your result by using aliases with dots in their names. Columns with similar aliases will be grouped into JSON objects. To take advantage of this option, you’ll need to switch from FOR JSON AUTO to FOR JSON PATH and enclose your aliases in either single quotes or brackets.

The following query creates a nested object called Name by assigning the alias Name.First to the FirstName column and Name.Last to the LastName column. In this query, I’ve used both brackets and single quotes to delimit my aliases just to demonstrate that both can be used:

Select id, FirstName as [Name.First], 'Name.Last'
  From Customers 
Where Id = 1 
FOR JSON PATH

And here’s the result (once again, formatted for readability):

"[{""id"":1,
   ""Name"":{
""First"":""Peter"",
""Last"":""Vogel""
}
}]"

Before using this feature, decide if you can write a readable SQL query to generate a result to return to your clients (and if you want to give up LINQ and Entity Framework to use this facility). But, if you decide you can, then you can reduce the amount of code required to return a result in the format your client wants to use.