Practical .NET

Working with JSON in SQL Queries

SQL Server 2016 lets you treat JSON objects like rows in a table, allowing you to use data from AJAX queries in joins, updates and any other SQL statement you can think of.

• By Peter Vogel
• 09/19/2017

SQL Server 2016 provides new support for working with JSON objects. In a previous column, I discussed how to generate JSON from SQL queries. This column looks at the reverse process: accepting JSON objects and converting them into relational tables that can be used in any SQL statement.

Two caveats: First, even if you’re working with SQL Server 2016, your database’s compatibility level must be set to 130 or higher to use these features. You can view your database’s compatibility level in SQL Server Management Studio by right-clicking on the database in Object Explorer, selecting Properties and viewing the Options tab. You can adjust your database’s compatibility level with this command:

Alter Database <YourDatabaseName> Set Compatibility_Level = 130

Second, this functionality isn’t supported through Entity Framework. So, to use the features in this column you’ll have to issue your SQL statements to the database yourself. I’ll discuss that code at the end of this column.

Leveraging JSON in ASP.NET
If you have client-side JavaScript code making AJAX calls to either ASP.NET MVC or Web API methods, then those methods will have parameters that accept JSON data, typically by mapping the JSON object into a .NET class. However, those classes can’t then be used in SQL statements.

With SQL Server 2016, it may make more sense to just accept the JSON data as a string, like this:

Public Function UpdateCustomer(jsonData As String) As ActionResult

Typical JSON data in that string, representing a single object, might look like this:

'{ "FirstName":null, "LastName":"Vogel", "IsPremium" : true, "SignupDate":"2017-09-30", "CreditLimit": 15, "PurchaseCount":25 }'

That JSON string can be converted into a table using TSQL by passing the string to the SQL Server 2016 OPENJSON function. Typical code looks like this:

Select *
From OPENJSON (jsondata);  

By default, the resulting table has columns (called key, value and type) with one row in the table for each property in the object. Table 1 shows the output for my JSON object.

The key column holds the name of the property, the value column holds the property’s data and the type column holds an integer from 0 to 5 (meaning, respectively, null, string, number, Boolean, array and object).

If your JSON string contains multiple objects in an array, like this:

'[
   { "FirstName":"Peter", "LastName":"Vogel", "IsPremium" : true, "SignupDate":"2017-09-30", 
     "CreditLimit": 15, "PurchaseCount":25 },
   { "FirstName":"Jan", "LastName":"Vogel", "IsPremium" : true, "SignupDate":"2016-10-15", 
     "CreditLimit": 10, "PurchaseCount":50 }
]'

then your output would look like Table 2, with each row containing a JSON object.

Structuring Your Table
You can convert that default, three-column format into a more useful table structure by following your call to OPENJSON with the WITH clause. In the WITH clause you define a table that you want your JSON mapped to by providing a list of column names with data types. The properties in your JSON object will be mapped to the columns in the table by matching names. Given this JSON object (or objects):

'{ "FirstName":null, "LastName":"Vogel", "IsPremium" : true, "SignupDate":"2017-09-30", 
  "CreditLimit": 15, "PurchaseCount":25 }'

the following SQL statement converts the object into a table (output is shown in Table 3):

Select * 
From OPENJSON (jsondata)  
  WITH (FirstName nvarchar(20),
        LastName nvarchar(20),
        IsPremium bit,
        SignupDate date,
        CreditLimit int);

This works as long as the column names in the WITH clause match the object’s property names. Because I didn’t specify a column for the object’s PurchaseCount property, it’s omitted from the table. Passed an array of JSON objects, OPENJSON…WITH just adds more rows to the table.

Defining Your Table
You can also map columns to JSON properties using JSON path expressions. In this JSON string, for example, two of the properties (FName and LName) have names that I’d prefer to replace:

' "FName":"Jason", "LName":"van de Velde", "IsPremium" : true, "SignupDate":"2017-09-30", 
  "CreditLimit": 25 }'

I can map columns called FirstName and LastName in my table to my JSON object’s FName and LName properties using simple JSON path expressions, like this:

Insert Into Customers
SELECT *
FROM OPENJSON (jsonData)  
  WITH (FirstName nvarchar(20) '$.FName',
        LastName nvarchar(20)  '$.LName',
        IsPremium bit,
        SignupDate date,
        CreditLimit int);

JSON path expressions can also be helpful in "flattening" JSON objects that contain other objects in their properties. This JSON object has another object in its Name property, for example:

'{ "Name": {"First":"Jason", "Last":"van de Velde"}, 
  "IsPremium" : true, "SignupDate":"2017-09-30", "CreditLimit": 25 }'

I can use a JSON path expression to map my table’s FirstName and LastName columns to the properties on this "nested" object, like this:

SELECT *
FROM OPENJSON (jsonData)  
  WITH (FirstName nvarchar(20) '$.Name.FName',
        LastName nvarchar(20)  '$.Name.LName',
        IsPremium bit,
        SignupDate date,
        CreditLimit int);

Working with JSON Tables
Once you’ve converted JSON into a table you can do table-like things with it. Often, for example, you might want to use the JSON object to add a row to a table. This code does just that:

Insert Into Customers
Select *
From OPENJSON (jsonData)  
  WITH (FirstName nvarchar(20),
        LastName nvarchar(20),
        IsPremium bit,
        SignupDate date,
        CreditLimit int);

Typical ADO.NET code to submit that SQL statement would look like Listing 1.

Public Function UpdateCustomer(jsonData As String) As ActionResult  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 = "Insert Into Customers " +
     "Select * " +
                    "From OPENJSON (@jsonData)" + 
                    "  WITH (FirstName nvarchar(20)," +
     "        LastName nvarchar(20)," +
     "        IsPremium bit," +
     "        SignupDate date," +
     "        CreditLimit int);" 
  cmd.Parameters.AddWithValue(@jsonData,jsonData)
  cn.Open()
  rdr = cmd.ExecuteNonQuery()
  cn.Close()

Alternatively, you might want to join the JSON object to another table so you can access related data. The following example joins the JSON object to rows in the Purchases table to access customer purchasing information. To do that, I only need to define the columns I need for my query:

Select *
From OPENJSON (@jsonData)  
  WITH (LastName nvarchar(20)) as Json
Inner Join Purchases 
  On Purchases.LastName = Json.LastName;

Similar code would allow you to use your JSON object to delete existing rows:

Delete cust
From OPENJSON (@jsonData)  
  WITH (LastName nvarchar(20)) As Json
Inner Join dbo.Customers As cust 
  On cust.LastName = Json.LastName;

And here’s the SQL to use the JSON object to update a table:

Update cust
  Set LastName = Json.FirstName
From OPENJSON (@jsonData)  
  WITH (FirstName nvarchar(20),
        LastName nvarchar(20)) As Json
Inner Join dbo.Customers As cust 
  On cust.LastName = Json.LastName;

Of course, looking back at the Insert statement, the simplest solution is just to shove JSON data into a text column in a row … provided, of course, you can query that data afterward. I’ll be coming back to look at that topic in a later column.