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.

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.

About the Author

Peter Vogel is a system architect and principal in PH&V Information Services. PH&V provides full-stack consulting from UX design through object modeling to database design. Peter tweets about his VSM columns with the hashtag #vogelarticles. His blog posts on user experience design can be found at http://blog.learningtree.com/tag/ui/.

comments powered by Disqus

Featured

  • Uno Platform Ports Windows Calculator to Linux

    Uno Platform has ported the famed Windows Calculator, open sourced last year, to Linux as part of a continuing "proof point" effort to demonstrate the reach of what it describes as the sole UI offering available to target Windows, WebAssembly, iOS, macOS, Android and Linux with single-codebase applications coded in C# and XAML.

  • ASP.NET Core OData 8 Preview Supports .NET 5, but with Breaking Changes

    ASP.NET Core OData, which debuted in July 2018, is out in a v8.0 preview that for the first time supports the upcoming .NET 5 milestone release.

  • VS Code Java Team Details 5 Best Dev Practices

    Microsoft's Visual Studio Code team for Java development added a new Coding Pack for Java installer and detailed best practices for setting up a development environment.

  • Binary Classification Using PyTorch: Defining a Network

    Dr. James McCaffrey of Microsoft Research tackles how to define a network in the second of a series of four articles that present a complete end-to-end production-quality example of binary classification using a PyTorch neural network, including a full Python code sample and data files.

  • Blazor Debugging Boosted in .NET 5 RC 2

    In highlighting updates to ASP.NET Core in the just-launched second and final Release Candidate of .NET 5, Microsoft pointed out better debugging for Blazor, the red-hot project that allows for C# coding of web projects.

Upcoming Events