Practical .NET

Querying JSON Data in SQL Server 2016

With SQL Server 2016, it now makes sense to store JSON objects in your database (even though there’s no JSON datatype). Here’s how to query JSON properties to find the rows you want.

• By Peter Vogel
• 10/19/2017

It’s not unusual in ASP.NET MVC applications to send and receive data in a JSON format, typically as part of creating an AJAX application. In an earlier column, I showed how to use the new SQL Server JSON support to return JSON objects directly from SQL statements, potentially slimming down the code in your Controller classes. But that left open the question of how to deal with JSON objects that your Action methods receive as parameters.

I addressed one way of dealing with that in a second column, which showed how to use the new JSON-related functionality to convert JSON objects into virtual tables: Each object becomes a row with a column for each property on the object. This lets you use JSON objects in your SQL statements as if they were tables, even inserting those objects as rows into a table.

The problem with that solution is that you lose the original JSON object. It might make more sense for you to just shove that JSON object into an NVarChar(MAX) column, leaving the object in its original format. That could, however, make it difficult to find your way back to that object. How do you retrieve a row based on values in the properties of a JSON object in a text column?

This is where the next part of the SQL Server 2016 support for JSON comes into play.

Validating JSON
For the purposes of this column, I’m going to assume this JSON object has been put into a column called LastUpdate in a table called Customers:

{"Name":{"First": "Peter", 
 	  "Last": "Vogel"}, 
 "IsPremium": true, 
 "SignupDate":"2017-09-30"}

Your first step in working with JSON data in a table should probably be defensive: You should make sure the text in the column you want to treat as a JSON object really is a valid JSON object. The ISJSON function will do that by returning 1 if the data passed to it is a valid JSON string and 0 if it’s not. You can use this to test any string, but normally you’ll pass ISJSON the name of a column. This query, for example, finds all the rows in the Customers table that have a valid JSON object in their LastUpdate column:

Select *
From Orders.Customers
Where (ISJSON(LastUpdate) > 0)

Of course, it’s probably a better plan to apply a constraint to the column to ensure that only valid JSON objects can be placed in the column in the first place. A Check constraint to do that would look like this:

Alter Table Orders.Customers
Add Constraint LastUpdateMustBeJson
Check (ISJSON(LastUpdate )> 0)

Querying JSON
A typical task with a JSON column is to find a row based on the value of a specific JSON property. For that you’ll use the T-SQL JSON_VALUE function, passing a JSON path expression (along with the column name) to reference an individual property.

In a JSON path expression, the dollar sign ($) represents the JSON object, and the standard dot notation is used to reference properties in the object. Using my earlier JSON object, the path $.SignupDate would retrieve the value of the SignupDate property and $.Name.First would retrieve "Peter" from the First property of the Name object nested in my JSON object.

In a T-SQL statement, I could retrieve both values with a query like this:

Select JSON_VALUE(LastUpdate, '$.SignupDate') AS SignupDate, 
       JSON_VALUE(LastUpdate, '$.Name.First') AS FirstName
From Customers

If the path expression doesn’t return a value (if, in this example, there is no SignupDate property), then JSON_VALUE returns NULL if you’re running in the default (lax) mode. If you’d prefer to have an error thrown in those scenarios, you can switch to strict mode by inserting the keyword "strict" in front of the dollar sign in your path expression. This path expression ensures I’ll get an error if the SignupDate property isn’t found:

Select JSON_VALUE(LastUpdate, 'strict $.SignupDate') AS SignupDate
From Customers

Strict mode does not, however, return an error if the column itself is NULL.

You’ll also get an error in strict mode if the target of the path expression isn’t a scalar value. If, for example, I tried to use JSON_VALUE to return the Name property (which contains a JSON object), I would get NULL in lax mode and an error in strict mode. If the value is more than 4,000 characters, you’ll also get NULL in lax mode and an error in strict mode. On the other hand, it’s perfectly OK for JSON objects to have multiple properties with the same name, so it’s possible for a path expression to match two properties. In that scenario, only the value of the first property is returned, regardless of what mode you’re running in.

You can use JSON_VALUE anywhere you can use a scalar value in a T-SQL statement. This query uses it in both the Order By and Where clauses, for example:

Select JSON_VALUE(LastUpdate, '$.SignupDate') AS SignupDate, 
       JSON_VALUE(LastUpdate, '$.Name.First') AS FirstName
From Customers
Where (JSON_VALUE(LastUpdate, '$.IsPremium') = 'true')
Order By JSON_VALUE(LastUpdate, '$.Name.Last')

Because SQL Server doesn’t have a Boolean data type, I must test the IsPremium property against the string "true."

But, wait, there’s more! In my next column I’m going to look at retrieving whole JSON objects rather than just values. I’ll also look at updating the JSON objects you retrieve.